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

Last Exception: Object reference not set to an instance of an object (18h)

Insertion and selection at same time (1d)

Group by and max in join. Group by should be done on fields from two different tables (2d)

Indexes to add to my queries (2d)

dbContext.SaveChanges(); error (3d)

Backup up System databases only and excluding the User databases (4d)

Bulk Insert with variables and return specific columns (4d)

Hive-exec.jar manual update? (4d)

- Advertisement -