Upgrading from Access to SQL Server

A quick note on upgrading from an Access DB to a SQL DB.

I see a lot of people in forums, and support sites for popular ASP Applications I use struggling with a conversion between Access and SQL Server.

There are many problems I have tried to help with. Stemming from using the DTS Wizard in Enterprise Manager to import the Access DB to redifing keys and default values.

Here's the situation. If you want to upgrade an Access Database to SQL Server 7 or 2000 the WORST way to do it is via Enterprise Manager or Query Analyzer.

The BEST way to upgrade from Access to SQL Server is using the Access "Upsizing Wizard. The reason is that when you import database information from Enterprise Manager you will successfully import all the data however you will loose table relationships, primary keys and default values.

To upgrade from Access simply open the database you want to upgrade and from the "Tools" menu select "Database Utilities" and the Upsizing Wizard. It will then ask for the SQL Server information and login. Provide it with the information it requests. Leave all selections to the default and click next next next finish.

It will take a while if you have a large database, but it will successfully upsize everything for you and provide a report of how the upsize went when it is complete.

 

Share this article!

Follow us!

Find more helpful articles: