Upsizing Microsoft Access Applications to Microsoft SQL Server

Rick Byham
Microsoft Corporation

August 1997

Introduction

This is an exciting time for PC database users and developers. Microsoft® SQL Server™ continues to be the leading high performance database for PCs. Running on Microsoft's rock solid Windows NT® operating system, it has brought unprecedented ease of use to a stable, high security environment.

There has never been a better time to start migrating your Microsoft Access database to Microsoft SQL Server. This paper helps you to complete the migration smoothly and optimize your application strategy for the fastest performance.

Version Differences

Both SQL Server and Microsoft Access exist in various versions. An explanation of the versions and which Upsizing Wizard to use for upsizing is discussed.

SQL Server

Microsoft SQL Server has three principal versions:

• SQL Server Version 4.21a and its predecessors were developed jointly with Sybase.

  This version lacks the Declarative Referential Integrity (DRI) familiar to Microsoft Access users. Only the Microsoft Access 2.0 Upsizing Wizard can reliably move data from Microsoft Access 2.0 to SQL Server 4.21a. This Upsizing Wizard should not be used to move a Microsoft Access 2.0 database to any version of SQL Server except 4.21a.

• SQL Server Version 6.0 (completely rewritten).

  It includes many new features including DRI and the handy SQL Enterprise Manager for administration of the server. You must have Service Pack 2 for SQL Server 6.0 to use the Microsoft Access for Windows® 95 Upsizing Wizard.

• SQL Server Version 6.5 (similar to 6.0 and is considered a maintenance release).

  There were very few changes to the base features of SQL Server 6.0, although many new features was added. These include improved database engine performance, row-level locking, replication to Microsoft Access databases, Internet/Intranet features, and the Distributed Transaction Coordinator. Your application can be converted to either 6.0 or 6.5 in exactly the same way. The Microsoft Access 95 or 97 Upsizing Wizard automates this process, and those versions are reviewed in this paper.

Microsoft Access

Microsoft Access version 1.0 and its enhancement, version 2.0, have been setting the standard for desktop databases.

Microsoft Access for Windows 95 uses 32-bit architecture fortified with many customer requested features. For the best performance in upsizing a Microsoft Access 2.0 database, it is recommended that you open the database with Microsoft Access for Windows 95, convert it to Microsoft Access for Windows 95, and then use the Upsizing Wizard for migration to SQL Server 6.5.

Though there are differences between Microsoft Access 95 and Microsoft Access 97, the Upsizing Wizards are nearly identical.