Summary: This article describes the process and discusses the tools you can use to migrate your Access database to SQL Server. (4 printed pages)
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 DBbased 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:
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.
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:
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:
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.
SQL Server stored procedures that have parameters need a different syntax from Access queries, for example:
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.
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 Servercompliant 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.