INF: How to Convert an Access Database to SQL Server

ID: Q237980

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft Access versions 2.0, 2000, 7.0, 97

SUMMARY

The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard:

Preserves database structure including data, indexes, defaults, and so on.

Automatically converts Access validation rules and defaults to the appropriate SQL Server equivalents.

Maintains table relationships and referential integrity after upsizing.

MORE INFORMATION

To run the Upsizing Wizard from Access 2000, click the Tools menu, point to Database Utilities, and click Upsizing Wizard.

To run the Upsizing Wizard from Access 97, you must first download the Microsoft Access 97 Upsizing Tools from the following Web site:

http://www.microsoft.com/accessdev/prodinfo/aut97dat.htm

If you have an earlier version of Microsoft Access, you can do either of the following:

First upgrade your version of Access to either Access 97 or Access 2000 and then run the Upsizing Wizard.

Use SQL Server Data Transformation Services (DTS) to import the data from your Access database to a SQL Server database. For more information on the DTS and the DTS Import Wizard, see "Data Transformation Services" and "Using the Import and Export Wizards" in the SQL Server 7.0 Books Online. For an example, see "Importing Data from an Access Database Example" in the Books Online.

Access 2000

If you are using Access 2000, you can use the following:

From the File menu, point to New, and then choose New Project from Existing Database.

NOTE

There are several white papers that can assist you in porting your Access application to SQL Server:

Designing Efficient Applications for Microsoft SQL Server
(Discusses why ISAM application design is inappropriate for SQL Server)
http://msdn.microsoft.com/library/psdk/dasdk/desi4jzm.htm

Migrating Your Microsoft Access Database to Microsoft SQL Server 7.0
(More than just the Upsizing Wizard; discusses design changes, limitations of the Wizard, and so forth)
http://www.microsoft.com/sql/interopmigrate/accessmigration.htm

Microsoft Access 2000 Data Engine Options
(Which data engine should you choose? Jet vs. MSDE)
http://www.microsoft.com/Office/2000/enterprise/prodinfo/msdtaeng.htm

Additional query words: conv conversion trans transfer xfer move copy upsize

Keywords : SSrvInst
Version : WINDOWS:2.0,2000,7.0,97; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbhowto kbinfo