Article ID: 237980 - Last Review: September 17, 2011 - Revision: 11.0

How to convert an Access database to SQL Server

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q237980

On This Page

Expand all | Collapse all

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, and default settings.
  • Automatically converts Access validation rules and default settings to the appropriate SQL Server equivalents.
  • Maintains table relationships and referential integrity after you upsize.
To run the Upsizing Wizard in Access 2000, on the Tools menu, point to Database Utilities, and then click Upsizing Wizard.

To use the Upsizing Wizard in Access 97, you must first download the Microsoft Access Upsizing Tools 97 from the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=f5c7a907-7c5f-46cb-9157-a1ec4e96083d&displaylang=en (http://www.microsoft.com/downloads/details.aspx?familyid=f5c7a907-7c5f-46cb-9157-a1ec4e96083d&displaylang=en)

To run the Upsizing Wizard from Access 97, on the Tools menu, point to Add-ins, and then click Upsize to SQL Server.

If you have an earlier version of Access, you can use either one of the following methods:
  • Upgrade to 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 about DTS and the DTS Import Wizard, see the "Data Transformation Services" and "Using the Import and Export Wizards" topics in SQL Server 7.0 Books Online. For an example, see the "Importing Data from an Access Database Example" topic in SQL Server Books Online.
If you are using Access 97 through Access 2003, together with SQL Server 2005, you can also use the SQL Server Migration Assistant for Access. For more information about the SQL Server Migration Assistant for Access, visit the following Microsoft Web site:
http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx (http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx)
Back to the top

Access 2000

In Access 2000, on the File menu, point to New, and then click New Project from Existing Database. This option creates an automatic data processing (ADP) application. This application automatically uses the SQL Server Desktop Engine (also known as MSDE 2000) or SQL Server as the server and uses the ADP file as the application.

Note When you run the Upsizing Wizard from Access 97 or Access 2000 to SQL Server 2000, you may receive an "Overflow" error. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
279454  (http://support.microsoft.com/kb/279454/ ) "Overflow" error message when you try to upsize to SQL Server 2000
272384  (http://support.microsoft.com/kb/272384/ ) "Overflow" error message when you try to upsize to SQL Server 2000
For more information about issues between Access 2000 and SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
269824  (http://support.microsoft.com/kb/269824/ ) Description of incompatibility issues between Access 2000 projects (*.adp) and SQL Server 2000
The following white papers can help you import your Access application to SQL Server:
  • Designing Efficient Applications for Microsoft SQL Server: This white paper explains why indexed sequential access method (ISAM) application design is not appropriate for SQL Server.
    http://msdn2.microsoft.com/en-us/library/aa226307(SQL.70).aspx (http://msdn2.microsoft.com/en-us/library/aa226307(SQL.70).aspx)
  • Migrating Your Microsoft Access Database to SQL Server 7.0: This white paper covers the Upsizing Wizard, including information about design changes and limitations of the wizard.
    http://technet.microsoft.com/en-us/library/cc917601.aspx (http://technet.microsoft.com/en-us/library/cc917601.aspx)
Back to the top

Access 97

For more information about the Microsoft Access Upsizing Tools 97, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=f5c7a907-7c5f-46cb-9157-a1ec4e96083d&displaylang=en (http://www.microsoft.com/downloads/details.aspx?familyid=f5c7a907-7c5f-46cb-9157-a1ec4e96083d&displaylang=en)
To download the Microsoft Access Upsizing Tools 97, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f5c7a907-7c5f-46cb-9157-a1ec4e96083d (http://www.microsoft.com/downloads/details.aspx?FamilyID=f5c7a907-7c5f-46cb-9157-a1ec4e96083d)
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
176614  (http://support.microsoft.com/kb/176614/ ) Microsoft Access 97 Upsizing Tools available
279454  (http://support.microsoft.com/kb/279454/ ) "Overflow" error message when you try to upsize to SQL Server 2000
Back to the top

REFERENCES

For more information about migrating an Access Database to SQL Server, visit the following Microsoft Web site:
http://msdn.microsoft.com/en-us/library/aa139985.aspx (http://msdn.microsoft.com/en-us/library/aa139985.aspx)
Back to the top
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 97 Standard Edition
  • Microsoft Access 2.0 Standard Edition
Back to the top
Keywords: 
kbsqlsetup kbhowtomaster kbhowto kbinfo KB237980
Back to the top