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 Query performance Call Center data (19h) Looking for on Premises tool to read data from SQL logs and populate data warehouse (1d) Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (1d) Working with multiple WHERE statements (1d) Create a new field value that shows a difference in value from 2 fields (2d) Hierarchy wise Sales Targets (3d) Get the MaxDate in results (5d) Switch Statement within a SSRS SQL Expression (6d)