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.

Related Articles

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server (4 May 2007)

Using Fuzzy Lookup Transformations in SQL Server Integration Services (22 January 2007)

Import text files script (19 October 2003)

Exporting records into ASCII format (13 August 2000)

Running Jobs (UNC vs Drive Letters ) (2 July 2000)

Other Recent Forum Posts

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (4d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (5d)

- Advertisement -