Migrating Your Access Database to Microsoft SQL Server 7.0

February 1999

Microsoft Corporation

Summary: This article describes the process and discusses the tools you can use to migrate your Access database to SQL Server.

Introduction

As customer needs grow and demand for an enterprise-scale high-performance database increases, customers sometimes move from the file-server environment of the Microsoft Access Jet engine to the client/server environment of Microsoft SQL Server. The Access 2000 Upsizing Wizard, available with Microsoft Office 2000, moves Access tables and queries into SQL Server 7.0. If you are working with an earlier version of Access, you can migrate your applications to SQL Server by upgrading to Access 2000, and then using the Upsizing Wizard.

If you prefer not to use Access 2000 and the Upsizing Wizard to migrate, use this article as a guide for moving an Access application to SQL Server. Moving an Access application requires moving the data into SQL Server 7.0 and then migrating the Access queries into the database or into SQL files for execution at a later time. The final step involves migrating the applications.

SQL Server Tools Used in Migrations

Several tools in SQL Server can assist you with the migration of your Access data and applications.

SQL Server Enterprise Manager

SQL Server Enterprise Manager allows enterprise-wide configuration and management of SQL Server and SQL Server objects. SQL Server Enterprise Manager provides a powerful scheduling engine, administrative alert capabilities, and a built-in replication management interface. You can also use SQL Server Enterprise Manager to:

By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running the Microsoft Windows NT operating system, and as part of the client software on computers running Windows NT and the Microsoft Windows 95 operating system. You will likely launch Data Transformation Services (DTS) from the SQL Server Enterprise Manager graphical user interface.

Data Transformation Services (DTS)

Data Transformation Services (DTS) allows you to import and export data between multiple heterogeneous sources that use an OLE DB–based architecture such as Microsoft Excel spreadsheets, and to transfer databases and database objects (for example, indexes and stored procedures) between multiple computers running SQL Server 7.0. You can also use DTS to transform data so that it can be used more easily to build data warehouses and data marts from an online transaction processing (OLTP) system.

The DTS Wizard allows you to interactively create DTS packages that use OLE DB and ODBC to import, export, validate, and transform heterogeneous data. The wizard also allows you to copy schema and data between relational databases.

SQL Server Query Analyzer

SQL Server Query Analyzer is a graphical query tool that visually allows you to analyze the plan of a query, execute multiple queries simultaneously, view data, and obtain index recommendations. SQL Server Query Analyzer provides the showplan option, which is used to report data retrieval methods chosen by the SQL Server query optimizer.

SQL Server Profiler

SQL Server Profiler captures a continuous record of server activity in real time. SQL Server Profiler allows you to monitor events produced through SQL Server, filter events based on user-specified criteria, and direct the trace output to the screen, a file, or a table. Using SQL Server Profiler, you can replay previously captured traces. This tool helps application developers identify transactions that might be deteriorating the performance of an application. This can be useful when migrating an application from a file-based architecture to a client/server architecture, because the last step involves optimizing the application for its new client/server environment.

Moving Tables and Data

To use the DTS Wizard to transfer your Access data into SQL Server, you can use these steps:

  1. In SQL Server Enterprise Manager, on the Tools menu, point to Data Transformation Services, and then click Import Data.

  2. In the Choose a Data Source dialog box, select Microsoft Access as the Source, and then type the file name of your .mdb database (.mdb file extension) or browse for the file.

  3. In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, select the database server, and then click the required authentication mode.

  4. In the Specify Table Copy or Query dialog box, click Copy tables.

  5. In the Select Source Tables dialog box, click Select All.

Migrating Microsoft Access Queries

You must move your existing Access queries into SQL Server in one of these formats:

For more information about Transact-SQL, stored procedures, or views, see SQL Server Books Online.

Access query type SQL Server migration options and comments
  A SELECT statement can be stored in a Transact-SQL file, a stored procedure, or a view.
Creating stored procedures is the best way to separate the database application development from the physical implementation of the database design. Stored procedures are created in one place, and are called from the application.
Calls to stored procedures will not “break” if the underlying database changes and the stored procedure is carefully modified to reflect these changes.
CROSSTAB Crosstabs are often used for summary reports.
An Access CROSSTAB can be implemented as a Transact-SQL SELECT statement in a SQL script, a stored procedure, or a view. The data join is reexecuted each time a query is issued, ensuring that the latest data is always used.
Depending on the application, it might be appropriate to store data from the crosstab as a temporary table (see MAKE TABLE in the next row). The temporary table requires fewer resources, but offers only a snapshot of the data at the time the temporary table is created.
MAKE TABLE An Access MAKE TABLE can be implemented as a Transact-SQL CREATE TABLE statement in a Transact-SQL script or stored procedure. The syntax follows: 
SELECT [ ALL | DISTINCT ]
[ {TOP integer | TOP integer PERCENT} [ WITH TIES] ] 
<select_list>
[ INTO new_table ]
[ FROM {<table_source>} [,…n] ]
[ WHERE <search_condition> ]
[ GROUP BY [ALL] group_by_expression [,…n] 
[ WITH { CUBE | ROLLUP } ]
 CREATE TABLE mytable (low int, high int)
UPDATE An UPDATE statement can be stored in a Transact-SQL script; however, the recommended way to implement an UPDATE statement is to create a stored procedure.
APPEND An APPEND statement can be stored in a Transact-SQL script; however, the recommended way to implement an APPEND statement is to create a stored procedure.
DELETE A DELETE statement can be stored in a Transact-SQL script; however, the recommended way to implement a DELETE statement is to create a stored procedure.

Migrating Microsoft Access Queries into Stored Procedures and Views

Each Access query must be placed into this set of statements:

CREATE PROCEDURE <NAME_HERE> AS
< SELECT, UPDATE, DELETE, INSERT, CREATE TABLE statement from Microsoft Access >
GO

CREATE VIEW  <NAME_HERE> AS
<Place (SELECT only, with no parameters) Microsoft Access Query>
GO

For each Access query:

  1. Open Access, and then in SQL Server, open SQL Server Query Analyzer.

  2. In Access, in the Database window, click the Queries tab, and then click Design.

  3. On the View menu, click SQL.

  4. Paste the entire query into SQL Server Query Analyzer.

  5. Either test the syntax and save the Transact-SQL statement for later use, or run the statement in the database. You can optionally save the Transact-SQL to a script.

Migrating Microsoft Access Queries into Transact-SQL Scripts

Most Access queries should be translated into stored procedures and views. Nevertheless, some statements run infrequently by an application developer can be stored as a Transact-SQL script, a text file that ends in the file extension .sql. These files can be run from within SQL Server Query Analyzer.

If you plan to transfer some of your Access queries into .sql files, consider separating the Transact-SQL statements into several scripts, depending on how they are used. For example, you can group together into a script those Transact-SQL statements that must be run with the same frequency. Another script might contain all Transact-SQL statements that are run only under certain conditions. Additionally, Transact-SQL statements that must be run in a specific order should be grouped together in a discrete script.

To move a statement from Access to a Transact-SQL file:

  1. Copy the statement into SQL Server Query Analyzer.

  2. Use the blue check mark icon to parse the statement.

  3. Execute the statement if appropriate.

Developers with MAKE TABLE Access queries have several options in SQL Server. Developers can create either of these:

For more information about temporary tables, see SQL Server Books Online.

Data Transformation Services (DTS) in SQL Server 7.0 allows you to standardize, automate, and schedule the creation of temporary tables by creating packages.

For example, when you migrate the Access 2.0 Northwind sample database, the crosstab that is created for reporting quarterly data becomes either a view or a data transformation that creates a temporary table on a regular basis. For more information about DTS, see SQL Server Books Online.

Additional Design Considerations

The following are some of the issues you must consider when migrating your Access application to SQL Server.

Using Parameters

SQL Server stored procedures that have parameters need a different syntax from Access queries, for example:

Access 2.0:

Query Name: Employee Sales By Country, in NWIND.mdb:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT Orders.[Order ID], [Last Name] & ", " & [First Name] AS Salesperson, Employees.Country, Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
WHERE (((Orders.[Shipped Date]) Between [Beginning Date] And [Ending Date]))
ORDER BY [Last Name] & ", " & [First Name], Employees.Country, Orders.[Shipped Date];

SQL Server 7.0:

CREATE PROCEDURE EMP_SALES_BY_COUNTRY 
@BeginningDate datetime,
@EndingDate datetime
AS
SELECT Orders.[Order ID], [Last Name] + ", " + [First Name] AS Salesperson, Employees.Country, 
Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
WHERE (((Orders.[Shipped Date]) Between @BeginningDate And @EndingDate))
ORDER BY [Last Name] + ", " + [First Name], Employees.Country, Orders.[Shipped Date]
GO

For more information, see SQL Server Books Online.

Nested Queries

Some Access queries are created on top of other queries in a nested fashion. Nested queries in Access become nested views in SQL Server. The ORDER BY clauses cannot be part of a view definition; instead they are appended to the SELECT statement that queries the VIEW. If you have nested Access queries, create several views, and then create stored procedures that both perform a SELECT operation on the view and append an ORDER BY clause to the SELECT statement.

For example, the following Access query:

SELECT * 
FROM STUDENTS
WHERE COUNTRY = “USA”
ORDER BY LAST_NAME

Becomes a SQL Server view and a stored procedure:
CREATE VIEW US_STUDENTS AS
SELECT * FROM STUDENTS
WHERE COUNTRY = “USA”

CREATE PROCEDURE US_STUDENTS_ORDER AS
SELECT * FROM US_STUDENTS ORDER BY LAST NAME

Verifying SQL Server–compliant Syntax

You can use the Parse command on the Query menu in SQL Server Query Analyzer to verify whether a view or stored procedure functions in SQL Server. In the example below, the Access query uses DISTINCTROW. SQL Server uses the Transact-SQL command DISTINCT to perform the same operation. The Parse command allows developers to isolate and modify syntax problems in their Access queries.

Connecting Your Applications

Many Access applications were written by using Microsoft Visual Basic for Applications or the Visual Basic for Applications Access user interface.

The first step in migrating your file-server application to a client/server model is to ensure that the application works against the new database. The next step is to optimize the application for the client/server environment by:

SQL Server and Access Query Syntax

The following table shows the corresponding differences between SQL Server and Access query syntax.

Access query syntax SQL Server query syntax
ORDER BY in queries ORDER BY in views not supported
DISTINCTROW DISTINCT
String concatenation with “&” String concatenation with “+”
Supported clauses/operators:

SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS

 Supported clauses/operators:

SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
COMPUTE
FOR BROWSE
OPTION

Not Supported: COMPUTE, FOR BROWSE, OPTION Not Supported: WITH OWNERACCESS
Aggregate functions:

AVG

COUNT(column)

COUNT(*)

MIN

MAX

FIRST

LAST

STDEV, STDEVP

SUM

VAR, VARP

 Aggregate functions:

AVG([ALL | DISTINCT] expression)

COUNT([ALL | DISTINCT] expression)

COUNT(*)

GROUPING (column_name)

MAX(expression)

MIN(expression)

STDEV, STDEVP

SUM([ALL | DISTINCT] expression)

VAR, VARP

Not supported: FIRST, LAST
TRANSFORM

(SELECT statement)

PIVOT

WITH ROLLUP, WITH CUBE on SELECT statements
MAKE TABLE, ALTER TABLE

Supported clauses:

CONSTRAINT

ADD COLUMN

DROP COLUMN

DROP INDEX

Also, stand-alone statement: DROP INDEX

 CREATE TABLE, ALTER TABLE

Supported clauses:

CONSTRAINT

ADD COLUMN

DROP COLUMN

Stand-alone statement: DROP INDEX