Migrating Your MS Access Database to MS SQL Server 7.0

  • Article

Updated : July 19, 2001

On This Page

Introduction
SQL Server Tools Used in Migrations
Moving Tables and Data
Migrating Microsoft Access Queries
Additional Design Considerations
Connecting Your Applications
Conclusion
Appendix A: Differences Between Microsoft SQL Server and Microsoft Access Query Syntax
Appendix B: Knowledge Base Article on Optimizing for Client/Server Performance
Finding More Information

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 document 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 assist you with the migration of your Access data and applications.

SQL Server Enterprise Manager

SQL Server Enterprise Manager allows for 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 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 Import Wizard and DTS Export Wizard allow you to interactively create DTS packages that use OLE DB and ODBC to import, export, validate, and transform heterogeneous data. The wizards also allow 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 Import 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 enable 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

SELECT

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.

    Cc917601.acc1(en-us,TechNet.10).gif

  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 that are 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.

The 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 but 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

Cc917601.acc2(en-us,TechNet.10).gif

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 the word "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 predating Office 2000 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 improving 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.

For more information about optimizing for client/server performance, see Appendix B in this document.

Conclusion

Migrating an Access 2000 database to SQL Server is a process that requires using only the Upsizing Wizard. Upgrading without the wizard requires moving the data and transferring the queries to the appropriate form (SQL Server scripts, views, and stored procedures). In either case, the application must be tuned or migrated to ensure it runs efficiently in the client/server environment.

SQL Server 7.0 provides the tools you need to migrate your database from Access.

Appendix A: Differences Between Microsoft SQL Server and Microsoft Access Query Syntax

Access

SQL Server

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

Not Supported: COMPUTE, FOR BROWSE, OPTION

Supported clauses/operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
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

Appendix B: Knowledge Base Article on Optimizing for Client/Server Performance

ACC: Optimizing for Client/Server Performance

Last reviewed: February 4, 1998

Article ID: 128808

The information in this article applies to: Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article lists tips to help you optimize performance when you use Open Database Connectivity (ODBC) data sources with Microsoft Access.

This article assumes that you are familiar with client/server environments and architectures.

Methods of Accessing Data

To access server data with a client/server application, you can use one or more of the following methods:

Attached tables and views - SQL pass-through queries to send SQL statements directly to the server

Attached Tables and Views - The most efficient way to handle server data is to attach SQL tables and views from the server. Microsoft Access stores field and index information for attached tables. This improves performance when you open the tables. Note that you must re-attach remote tables if you make changes to fields or indexes on the server.

Using SQL Pass-Through Queries

In many applications, you can use both Microsoft Access queries based on remote tables and SQL pass-through queries. Pass-through queries offer the following advantages:

  • Microsoft Access does not compile a pass-through query. It sends the query directly to the ODBC server, speeding up the application.

  • Microsoft Access sends the pass-through query to the server the way you enter it. Therefore, more processing occurs on the server and less data is exchanged over the network.

    Forms, reports, and Microsoft Access queries can be based on pass- through queries using ODBC attached tables.

    • You can use SQL Server-specific functionality, such as stored procedures and server-based intrinsic functions that have no equivalent in code.

    • Update, delete, and append pass-through action queries are much faster than action queries based on attached remote tables, especially when many records are affected.

    Pass-through queries have the following disadvantages:

    • An SQL pass-through query always returns a snapshot that cannot be updated. A Microsoft Access query usually returns a dynaset that reflects other users' changes and can be updated.

    • You type the commands directly into the SQL Pass-Through Query window with the syntax that your server requires. You cannot use the graphical query by example (QBE) grid.

    • To use a parameter with a pass-through query, you must run the query in code and modify the query's SQL property. For an example of how to modify the SQL property, see "Building Applications with Microsoft Access 97," Chapter 19, "Developing Client/Server Applications," pages 550-551.

Request Less Data from the Server

Requesting data from the server costs processing time. To optimize performance, request only those records and fields that you need.

Reduce the number of bound controls, such as text boxes, combo boxes, list boxes, and subforms that you use. When a form is opened, each of these controls requires a query to be sent to the server.

If the attached ODBC tables have Memo or OLE object fields, you can set the Visible property of the fields to No and add a toggle button to set the Visible property to Yes so that users can choose to view it.

Some attached tables (for example, a table containing the names and abbreviations of the 50 states) do not change frequently. You can speed up form loading and reduce server load by using a local copy of these tables. You can also provide a way to update the local copy with more recent data from the server.

Optimizing Queries

The principal method for optimizing queries (after adding appropriate indexes on the server) is ensuring that processing takes place on the server. You should avoid functionality that is not supported by the server, such as functions specific to Microsoft Access or user-defined functions. For detailed information about what must be processed locally, see the Microsoft Jet Database Engine ODBC Connectivity white paper (for more information about this white paper, see the "Microsoft Jet Database Engine ODBC Connectivity White Paper" section later in this article).

To see the Select statement that is being sent to the server, you can set the TraceSQLMode setting. In Microsoft Access 7.0 and 97, you have to edit the Registry to add the option for TraceSQLMode.

For more information on editing the Registry to change ODBC settings, please see the following article in the Microsoft Knowledge Base:

Article-ID: 139044

Title: ACC95: How to Add Former MSACC20.INI ODBC Section to Registry

For more information about the TraceSQLMode setting in Microsoft Access 97 search for "TraceSQLMode" using the Find option of the Microsoft Access 97 Help Topics.

For more information about the TraceSQLMode setting in Microsoft Access 2.0, search for "MSACC20.INI" then "Customizing MSACC20.INI Settings" using the Microsoft Access Help Menu.

TryJetAuth Setting

The TryJetAuth setting controls whether Microsoft Access first attempts to log on to your server using the Microsoft Access login ID and password. (By default, the Microsoft Access login ID is "admin" and the password is blank.) If this fails, Microsoft Access prompts you for a login ID and password. Unless you have set up Microsoft Access to use the same login ID and password as your ODBC server, add the line "TryJetAuth=0" to the HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Office \8.0\Access\Jet\3.5\Engines \ODBC key of the Windows Registry. This prevents Microsoft Access from attempting to log on with the wrong ID.

Note: In Microsoft Access 2.0, add the line "TryJetAuth=0" to the MSACC20.INI file.

Timestamps

On servers that support them (such as Microsoft SQL Server), timestamp fields make updating records more efficient. Timestamp fields are maintained by the server and are updated every time the record is updated. If you have a timestamp field, Microsoft Access needs to check only the unique index and the timestamp field to see whether the record has changed since it was last retrieved from the server. Otherwise, Microsoft Access must check all the fields in the record. If you add a timestamp field to an attached table, re-attach the table in order to inform Microsoft Access of the new field.

Transactions

Using transactions when you update or insert records on attached tables in Access Basic can improve performance. Transactions enable the Jet database engine to accumulate multiple updates and write them as a single batch. With Microsoft SQL Server, keep transactions short because they generate locks that prevent other users from reading data affected by the current transaction. Although you can nest transactions using the Visual Basic development system, most servers do not support nested transactions. Microsoft Access sends only the first-level transaction to the server.

Optimizing Recordsets and Code

You can store all or part of the data contained in Recordset objects of the Dynaset type in local memory by setting the CacheSize and CacheStart properties.

The cache size can be between 5 and 1200 records. If the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Applying the FillCache method fills the cache with server data. To recover the cache memory, set the CacheSize property to zero.

When you use Recordset variables, use only the functionality that you need. For example, you can use the DB_APPENDONLY option on the OpenRecordset method if you only need to add new records to a recordset. If you do not need editing or updating ability, base your recordsets on SQL pass-through queries. The fastest method to insert and update records is to use SQL pass-through queries in code.

MSysConf Table

You can create a table named MSysConf on your server to control background population (the rate at which Microsoft Access reads records from the server during idle time). You can use this table to set the number of rows of data that are retrieved at one time and the number of seconds of delay between each retrieval. If you experience excessive read-locking on the server, you can adjust the table settings to increase background population speed. If too much network traffic is generated by background population, you can adjust the settings to slow it down.

Microsoft Jet Database Engine ODBC Connectivity White Paper

An important source for additional information is the Jet Database Engine ODBC Connectivity white paper. This document discusses the Microsoft Jet database engine version 3.0 and how it uses ODBC.

References

For more information on optimizing client/server applications, see Microsoft Access "Building Applications," Chapter 19, "Developing Client/Server Applications," pages 523-556

For information on how to obtain the Jet Database Engine ODBC Connectivity white paper, please see the following article in the Microsoft Knowledge Base: ARTICLE-ID: 128385 TITLE : ACC95: Jet & ODBC White Paper Available on MSL

You can also obtain this article through Microsoft FastTips by ordering item number 128385 from the FastTips Main Menu.

Keywords : OdbcOthr kbusage kbfaq

Version : 2.0 7.0 97

Platform : WINDOWS

Hardware : x86

Issue type : kbhowto

Finding More Information

You can use these additional resources to find out more about migrating to SQL Server:

  • Client/Server development information

  • SQL Server Books Online

  • Microsoft Knowledge Base: support.microsoft.com

  • Chapter 15, "Developing Client-Server Applications," in the Access97 Developer's Handbook, by Paul Litwin, Ken Getz, and Mike Gilbert.