Uploading MDB to SQL Server Programmatically

By Bill Graziano on 9 August 2000 | Tags: Import/Export

Yashwant writes "Can I upload my local mdb files on the SQL server(and other way round) on the Web Programmatically without using SQL server Desktop for DTC/Import or export utility. I am using VB as my local front end and ASP as my front end for web. Please Help --ASAP"

There are a couple of different ways to go about this. Which method makes sense will depend on how many databases you have to convert and how many tables are in each database.

The easiest method is the Access Upsizing Wizard. This converts the databases very nicely. I'm guessing since you said you wanted this programmatically that you aren't looking for this type of approach. I'm pretty sure you can't script the wizard either.

The second approach is the brute force method. Write a web page that connects to both databases and move records from one database to the other. This will be slow. Very, very slow.

In Access, you can create tables that are really just views of SQL Server tables. These are called attached tables. If you can attach to the SQL Server tables you can use Access queries to move data back and forth. This is probably the best way.

Another approach is to export from Access to a text file and import that into SQL Server. SQL Server has a utility called BCP that imports and exports text files very efficiently. It can be called from inside SQL Server (BULK INSERT) or from an operating system shell (bcp.exe).

I don't know that any of those solutions are especially exciting. I would lean toward attached tables if I could.