Migrating Your Access Database to Microsoft SQL Server 7.0 

  • Article

 

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. (4 printed pages)

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:

  • Manage logins and user permissions
  • Create scripts
  • Manage backup of SQL Server objects
  • Back up databases and transaction logs
  • Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types
  • Create full-text indexes, database diagrams, and database maintenance plans
  • Import and export data
  • Transform data
  • Perform various Web administration tasks

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:

  • Transact-SQL scripts

    Transact-SQL statements are usually called from database programs, but you can use SQL Server Query Analyzer, included in SQL Server 7.0, to run them against the database directly. SQL Server Query Analyzer helps developers to test Transact-SQL statements against development databases, or to run Transact-SQL statements that perform queries, data manipulation (INSERT, UPDATE, DELETE), or data definition (CREATE TABLE).

  • Stored **** procedures

    Developers can move most Transact-SQL statements that originate from Access queries (SELECT, INSERT, UPDATE, and DELETE) into stored procedures. Stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access, and are actually stored within the database. Stored procedures can run with or without parameters and are called from database programs or manually from SQL Server Query Analyzer.

  • Views

    Views are used as virtual tables that expose specific rows and columns from one or more tables. They allow users to create queries without directly implementing the complex joins that underlie the query. Views do not support the use of parameters. Views that join more than one table cannot be modified using INSERT, UPDATE, or DELETE statements. Views are called from Transact-SQL statements, and can also be used in *.scripts that are run in SQL Server Query Analyzer. SQL Server views and the SQL-92 standard do not support ORDER BY clauses in views.

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:

  • A view

    A view creates the effect of having a dynamic, virtual temporary table that provides the latest information. This is I/O intensive, because it requires the rejoining of the data tables each time a query is issued.

  • A temporary table.

    A temporary table creates a snapshot of data for a connected user’s session. You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with double number sign (##table_name). Queries run quickly against temporary tables because they generally use only one table rather than dynamically joining together several tables to obtain a result set.

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.

  • Applications that use Visual Basic for Applications as the development environment can run against SQL Server, using the Jet ODBC driver.
  • Applications that use the forms and reports found in the Access user interface can access SQL Server using linked tables. If your application will use linked tables, make sure that all Access tables get moved to SQL Server to increase performance. Creating queries against a mix of Access (Jet) and SQL Server using linked tables can be very resource-intensive.

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:

  • Monitoring Transact-SQL statements being sent to the server.

    SQL Server Profiler is a useful tool for monitoring how Transact-SQL statements are sent to the database. If you run an unmodified Access application on SQL Server, you might send suboptimal Transact-SQL to the database by using Data Access Objects (DAO) with the Jet/ODBC driver. For example, a DELETE statement that uses the Jet/ODBC driver to delete 1,000 rows makes 1,000 calls to the database, negatively impacting the performance of a production database. In this example, SQL Server Profiler displays 1,000 DELETE statements, allowing you to modify the application to use Microsoft ActiveX Data Objects (ADO) with the Microsoft OLE DB Provider for SQL Server, and thereby improve the application’s efficiency.

  • Implementing efficient indexes.

    After you determine that the Transact-SQL statements being sent to the database are efficient, you can fine-tune those statements by using indexes more effectively. The Index Tuning Wizard allows you to find bottlenecks, and it makes recommendations. Your Transact-SQL statements are not modified, but their performance improves with the correct use of indexes.

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

© Microsoft Corporation. All rights reserved.