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

Checkmark for guaranteed SR = WR (12h)

How to connect to git in SQL Server 2016/2017 without using any third party tool (12h)

Sql restart (17h)

Excel column wise data save in rows (1d)

Date timzone conversion (2d)

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2d)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (2d)

Two records into a single record? (2d)

- Advertisement -