How to Migrate from Access to SQL Server 2000

Writer: Adam Cogan

Technical Reviewer: Jatin Valabjee

Applies To: SQL Server 2000 Service Pack 3a (SP3a)

Summary: This document details the process of upsizing a Microsoft® Access 2000 database to a Microsoft SQL Server™ 2000 database using the Microsoft Upsizing Wizard.

On This Page

Assumptions
Who Should Read This
Introduction
What the Upsizing Wizard Does
Preparing Your Access Database for Migration
Running the Microsoft Upsizing Wizard
What to Do After Upsizing Your Database
Conclusion

Assumptions

All comparisons in this paper are made with the assumption that the following software is used:

• Microsoft® Access 2002 or later

• Microsoft SQL Server™ 2000 Standard Edition or Enterprise Edition

It is assumed that your data is currently stored in an Access database (.mdb) file, and not in SQL Server. It is also assumed that you are not currently using an Access Data Project (ADP).

This paper is based on using the linked tables method of upsizing. All comparisons and processes are based on using this method to upsize your Access database.

Who Should Read This

This paper is for Access developers preparing to migrate their Access back end to SQL Server 2000. Readers should be familiar with the differences between Access and SQL Server, detailed in the Microsoft white paper, “What's New and Different When Moving Your Back End from Access to SQL Server 2000.”

Readers should be familiar with the following features of Access:

• Writing Microsoft Visual Basic® for Applications (VBA) code behind forms

• Writing queries, modules, and macros

• Backing up and restoring data

Introduction

Microsoft Access developers generally consider a move to Microsoft SQL Server for performance, security, and stability reasons. This process is known as upsizing, and developers will find a number of key differences while migrating from Access to SQL Server.

SQL Server and Access are similar but have some major differences. They vary in the way that data is stored and indexed, the data types available, and storage capabilities. Microsoft provides the Microsoft Upsizing Wizard to assist in the migration process. It analyzes your Access database and converts your data and database structure into SQL Server format.

The Microsoft Upsizing Wizard effectively and accurately converts most of your Access database and database objects into SQL Server. However, some features in Access are not supported by SQL Server and vice versa, so it is important that you manually analyze and rectify any potential issues that may arise before, during, and after the migration process. It is also crucial that after conversion, the resulting database manually be inspected to ensure all tables, data, and relationships were correctly migrated.

This paper details:

• What the Upsizing Wizard does

• Preparing your Access database for migration

• Running the Upsizing Wizard

• What to do after upsizing your database

This paper does not cover reasons to upgrade to SQL Server from Access. More information about this can be found in the Microsoft white paper, “What's New and Different When Moving Your Back End from Access to SQL Server 2000.”

What the Upsizing Wizard Does

The Upsizing Wizard makes decisions about your data when converting from Access to SQL Server.

Tables

For each table in your Access database, the Upsizing Wizard creates a table in the SQL Server database and attempts to copy the schema, relationships, and indexes.

The Upsizing Wizard attempts to convert field validation rules to check constraints. For example, the BirthDate field in the Employees table in Northwind has a validation rule <Date(), that is, the date entered cannot be later than the current date. The Upsizing Wizard will convert this to the following T-SQL field check constraint:

 ([BirthDate] < convert(datetime,convert(varchar,getdate(),1),1))

Field default values are converted during upsizing. Validation text for fields is not converted. Instead, if you attempt to enter invalid text in a field with a validation rule, the insertion or update will fail with a SQL Server error message.

Queries

The Upsizing Wizard does not upsize any of your queries (when using linked tables). They continue to use data through the linked tables without requiring any changes to the query. However, some complex queries such as crosstab data and queries with multiple table joins should be rewritten to minimize network traffic and improve system performance. For more information about rewriting complex Access queries, see What to Do After Upsizing Your Database later in this white paper.

Data Types

Table 1 shows the mapping between Access (Jet) data types and SQL Server data types. The Upsizing Wizard will, where possible, convert the Access data types to equivalent SQL Server data types, and allocate memory space for each field. After successfully upsizing, it is crucial that you verify that the correct data types have been selected by the Upsizing Wizard.

Table 1   A comparison between data types in Access and SQL Server

Jet	SQL Server
Text	char, nchar, varchar, nvarchar
Memo	text, ntext
Byte	tinyint
Integer	smallint
Long Integer	integer
Single	real
Double	float
Replication ID	uniqueidentifier
Decimal	decimal
Date/Time	smalldatetime, datetime, timestamp
Currency	smallmoney, money
AutoNumber	int + identity property
Yes/No	bit
OLE Object	image
Hyperlink	<no equivalent>
<no equivalent>	binary, varbinary

Forms, Reports, Controls, and Data Access Pages

The purpose of the Upsizing Wizard is to migrate your back end (that is, database and database objects) to a SQL Server environment. SQL Server differs from Access in that it is purely a database management system (DBMS). It does not have internal functionality for front ends such as customizable data entry forms and reports on data, as Access does. Instead, after you have successfully run the Upsizing Wizard, you have two options:

• Continue to use your forms and reports in Access, only now they will be reading data from SQL Server. The Upsizing Wizard tries to ensure that your forms and reports work properly after upsizing.

• Re-create your forms using .NET and migrate your reports to Microsoft Reporting Services. For more information, see the Microsoft white papers, “How to Migrate Access Forms to .NET” and “How to Migrate Access Reports to Reporting Services.”

Modules and Macros

The Upsizing Wizard does not attempt to convert any code in modules or macros to SQL Server. We strongly recommend that after your database is successfully upsized that you analyze your code and implement optimizations and new database access techniques to get the most performance from SQL Server. These processes are detailed in What to Do After Upsizing Your Database later in this white paper.

Preparing Your Access Database for Migration

Before running the Upsizing Wizard, several checks and tasks must be performed to ensure a smooth and error-free migration. You should:

1. Document the state of the database before migration.

2. Configure security on both databases to ensure you have the necessary permissions to perform the migration.

3. Configure the Open Database Connectivity (ODBC) query time-out to ensure the Upsizing Wizard runs correctly.

4. Check the Access database for incompatibilities that may arise during upsizing.

5. Perform system checks and backups to ensure a smooth migration.

   There are also third-party tools available to simplify the process of analyzing your Access database for potential upsizing issues, described in Tools to Identify Upsizing Issues later in this white paper.

Step 1: Documenting the Database

The crucial first step before performing any migration is to document the entire migration process. The documentation you produce will help to:

• Determine the system requirements for the new database.

• Estimate the cost and time involved in performing the migration.

• Determine which database objects are no longer used and do not need to be migrated.

• Ensure the accuracy of the Upsizing Wizard migration.

Some items to include in your documentation are:

• Reasons why you have chosen to migrate.

• Database information, such as the:

  • Physical name and location of the database before and after migration.

  • Size of the original Access .mdb file.

  • Initial size the database will be in SQL Server.

• Estimated system downtime.

• A list of all database objects. This is used to determine if they are required, and also to keep track of where they end up in the SQL Server database.

• A migration log detailing every database object (tables, queries, forms, macros, and modules) and their purpose. You can use Access Documenter to automatically generate information about all your database objects. To open Documenter, select Tools, select Analyze, and then select Documenter. This can then be exported to Microsoft Word by selecting the Publish it with Microsoft Word button . A sample database document generated with Access Documenter is shown in Figure 1.

• All relationships and constraints between tables. This will be used after migration to ensure that all referential integrity is maintained.

• All costs involved.

• Maintenance plans.

These are only guidelines for documentation. You should add any documentation that you feel will be useful during the migration and in the future when changes are made to the database.

Figure 1   Use Access Documenter to generate a report about all your database objects before migration

Step 2: Configuring Security

Ensure that you have adequate permissions on both the Access database and the instance of SQL Server to which you are migrating. You will need at least Read Design permission on all objects that will be migrated and on all objects that act as a data source for migrated objects. For example, to properly migrate a query, you must have Read Design permissions on the query itself as well as the table or tables that the query uses as the data source. To avoid security related issues, we recommend that you remove all security from your Access .mdb file before migration.

Step 3: Configuring the Query Time-out

Because the Upsizing Wizard uses Open Database Connectivity (ODBC), which is a method of programmatic database access, to transfer all data to SQL Server, you need to ensure the ODBC connection time-out is sufficiently long to let the Upsizing Wizard perform its analysis and conversion.

ODBC settings are managed through the system registry and must be configured when Access or any other clients using Jet are closed. The registry settings are available through the Microsoft Windows® Registry Editor (Regedit.exe) under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

The default value for this is 60, which means that if it takes longer than 60 seconds to transfer the data from an Access table to SQL Server, the wizard will fail. Set the value to 0 by double-clicking the name and selecting the Decimal option in the Base options. Change the setting to 0 and click OK. Make sure to return this to its original value after your database has been successfully upsized.

Step 4: Checking the Database

The most important task before upsizing your Access database is to perform checks on the data and database objects, and rectify any issues to ensure a smooth migration. The following key items should be checked and corrected before migration.

Indexes

Issue

Tables without a unique index will be upsized, but they will be read-only in SQL Server.

Fix

Make sure you attach a unique index to each table before upsizing.

Issue

There is a field in any table that contains a null value for more than one record and has the following attributes:

• The Indexed property is set to Yes (No Duplicates).

• The Required property is set to No.

The Upsizing Wizard successfully upsizes a table with this field, but not the data. This is because SQL Server will not allow more than one of the same value in a field with a No Duplicates attribute, including a null value.

Fix

If a field in your table is indexed using the No Duplicates option, do not add more than one record that contains a null value for the column in question.

Tables and Fields

Issue

Hidden database objects in Access will be skipped by the Upsizing Wizard.

Fix

If you want the hidden objects to be upsized by the Upsizing Wizard, right-click them and select Properties. Clear the Hidden check box as shown in Figure 2, and click OK.

Figure 2   Clear the Hidden check box for any database objects that you want to upsize

Issue

Any of your Access tables, queries, or fields contain SQL Server reserved words. Some reserved keywords will not fail, but we recommend that you change them to avoid future issues. The following reserved keywords will fail in the Upsizing Wizard:

• AUTHORIZATION

• CASE

• CHECK

• COLLATE

• COLUMN

• CONTAINS

• CONTAINSTABLE

• ESCAPE

• FETCH

• FILE

• FREETEXT

• FREETEXTTABLE

• FULL

• IDENTITYCOL

• INNER

• JOIN

• KEY

• LEFT

• NATIONAL

• OPENDATASOURCE

• OPENQUERY

• OPENROWSET

• OUTER

• RESTRICT

• RIGHT

• ROWGUIDCOL

• SCHEMA

• WHEN

A complete list of SQL Server reserved keywords is available at https://msdn2.microsoft.com/library/aa238507.aspx.

For example, if you had a table named Case (that is, a reserved SQL Server keyword) in your Access database, attempts to upsize the table would return an error similar to that in Figure 3.

Figure 3   Attempting to upsize an object with a name conflicting with a SQL Server reserved keyword will cause the Upsizing Wizard to fail

Fix

Before upsizing, identify and rename any database objects with names that are reserved SQL Server keywords. Ensure referential integrity is preserved (in the case of tables) after renaming your objects.

Issue

Illegal characters are used in Access table or field names, for example Custo"mers. Single quotation marks ('), quotation marks ("), and asterisks (*) will cause the Upsizing Wizard to fail. In the Upsizing Wizard, quotation marks are replaced by an underscore, and relationships with any fields containing quotation marks are invalidated because the field was renamed.

The Upsizing Wizard will fail, displaying the following error message:

Server Error 170: Line 5: Incorrect syntax near 'ers_FK00'. 
Server Error 103: The identifier that starts with ' 
     FOREIGN KEY (CustomerID) REFERENCES Custo_mers(Customer_ID) , 
     EmployeeID int , OrderDate datetime , RequiredDa' is too long. 
     Maximum length is 128. Server Error 105: Unclosed quotation mark 
     before the character string ' PRIMARY KEY NONCLUSTERED (OrderID) )'.

Fix

Rename any tables with these illegal characters in your Access database. Ensure that any relationships and referential integrity still exist for the renamed tables.

Issue

Records have dates that are outside the range of supported dates in SQL Server. SQL Server supports dates ranging from January 1, 1753 to December 31, 9999. This is usually not an issue, although sometimes data is incorrectly entered, for example 1/1/199 instead of 1/1/1999. The data in tables containing invalid dates will be skipped when upsizing. (The table structure will be upsized, however.)

Fix

Write a query in Access that will go through and verify that all dates are within the required date range. For example, in an Orders table with a DateShipped field with data type Date/Time, the query to run would be:

SELECT * FROM Orders 
     WHERE DateShipped < #1/1/1753# OR DateShipped > #31/12/9999#

Run a query on every field that has a Date/Time value. If there are a number of values that are clearly incorrectly entered (for example, 1/1/199), you can run a SQL UPDATE query to update all the values in one operation. For example:

UPDATE Orders SET DateShipped = #1/1/1999# WHERE DateShipped = #1/1/199#

This query updates all values in the DateShipped column in the Orders table to 1/1/1999 where the DateShipped value is equal to 1/1/199.

Issue

You have defined an Access table relationship where the related fields are not the same. For example, you have a Customers table linked to an Orders table, with the linked field being CustomerID. However, the field size of CustomerID in the Customers table is 15, and the field size of CustomerID in the Orders table is 10. In this case, the Upsizing Wizard will fail.

Fix

Change the linked field lengths and types to be the same in a table relationship. In the previous example, you would increase the size of the CustomerID field in the Orders table to match the size of CustomerID in the Customers table, that is, 15. Make sure to increase the field size of the smaller field, rather than decrease the field size of the larger field, to avoid data loss.

Issue

You have Access table names prefixed with sys. SQL Server creates several system tables in each database that keep track of information such as database object permissions and tables.

Fix

The SQL Server system tables are all prefixed with sys, so we recommend that you change the names of these tables in Access to avoid confusion and naming conflicts when migrating.

Step 5: Performing System Checks and Backups

Ensure you have adequate space on the computer that will contain the new SQL Server database. As a general rule, allow five times more space for the SQL Server database than the Access database. For example, if your Access database is 100 megabytes (MB), allow 500 MB for the Upsizing Wizard and the resulting SQL Server database.

It is also important at this stage to perform a backup of the Access database to preserve all of the database reconfiguration before you run the Upsizing Wizard.

Tools to Identify Upsizing Issues

Several third-party tools are available to help analyze your Access database to detect and correct the issues previously described.

SSW Upsizing PRO! (https://www.ssw.com.au/ssw/UpsizingPRO/) is a tool that checks your Access database for any potential problems that may arise because of incompatibilities between SQL Server and Access. As shown in Figure 4, it produces a detailed report of any issues found, with suggested actions to correct the errors.

Figure 4   SSW Upsizing PRO! helps identify potential migration issues not detected by the Microsoft Upsizing Wizard

FMS Total Access Analyzer (https://www.fmsinc.com/Products/analyzer/index.html) performs detailed analysis of your Access database to generate documentation of all objects and code. It examines your database to reveal complex relationships among objects, hidden problems, and unused objects. You can use Total Access Analyzer to identify and fix any problems in your Access database before running the Upsizing Wizard.

Running the Microsoft Upsizing Wizard

The Microsoft Upsizing Wizard upgrades an Access database to a new or existing SQL Server database by upsizing the data and data definitions, and migrating database objects. It creates a report containing detailed information about all new database objects created, as well as any errors encountered during the process. This report is stored as an Access report snapshot in the same folder as the database and can be exported as a Word or Excel document.

The Northwind Access database is used throughout this example to demonstrate the upsizing process. The Northwind database is shipped with Access and can be found in the Samples subdirectory of the Access installation directory. Alternatively, it can be downloaded from https://www.microsoft.com/downloads/.

Follow these steps in the Upsizing Wizard:

1. Create a new SQL Server database.

2. Select upsizing options.

3. Link your Access tables to SQL Server.

Step 1: Creating a New SQL Server Database

Run the Upsizing Wizard by opening the database to be upsized in Access. Click Tools, click Database Utilities, and then click Upsizing Wizard (see Figure 5).

Figure 5   Run the Upsizing Wizard by opening the database to upsize and selecting the option from the Tools menu

Select the Create New Database option, and click Next. We recommend that you run upsizing on a local SQL Server database because of performance. Specify your SQL Server instance, usually (local), select Use Trusted Connection (or specify a Login ID and Password if you defined it earlier), and type the desired name for the new database (as shown in Figure 6). Click Next to continue.

Figure 6   Specify your SQL Server instance and the name for the new database

Step 2: Selecting Upsizing Options

Click the unfold button to select all tables for upsizing. Although you can select the tables you want to upsize, we recommend that you upsize all the tables at once to preserve table references. Click Next to continue.

As shown in Figure 7, ensure Indexes, Defaults, Validation rules, and Table relationships are selected for upsizing. Select Use DRI to use Declarative Referential Integrity (DRI) for relationships. DRI is more efficient than using triggers for referential integrity. Also, choose No, never for Add timestamp fields to tables, and make sure data gets upsized by clearing the Only create the table structure; don't upsize any data box. Click Next to continue.

Figure 7   Make sure everything gets upsized

Step 3: Linking Your Access Tables to SQL Server

On the next screen, select Link SQL Server tables to existing application. This option modifies your Access database so that your queries, forms, reports, and data access pages use the data in the new SQL Server database instead of the data in your Access database. No changes need to be made to make your database objects work with the new SQL Server database.

When you select this option, the Upsizing Wizard makes the following changes to your Access database:

1. All tables being upsized are renamed with the suffix _local. For example, if you upsize a table named Customers, it will be renamed to Customers_local.

2. A new linked table is created for each table to be upsized. This linked table is a connection to the related SQL Server table. No data or structural information is stored in the linked table. When you open a linked table, it appears and can be edited in the same way as a normal Access table. However, in the background, it is accessing data from its corresponding SQL Server table. The Upsizing Wizard will give the linked table the same name as the original table. In the preceding example, a linked table called Customers is created (see Figure 8).

   

   Figure 8   The Upsizing Wizard creates a linked table in place of the original Access table

Click Next and Finish to start the Upsizing Wizard.

If there are any errors, note them (export the report to Word) and make the required changes. For more information about how to repair common upsizing issues, see Preparing Your Access Database for Migration earlier in this white paper. You can then safely rerun the Upsizing Wizard. Delete any SQL Server database that the Upsizing Wizard partially created in previous upsizing attempts.

What to Do After Upsizing Your Database

After running the Upsizing Wizard, ensure that everything migrated successfully. You should verify that all tables are the same, and ensure that the number of records in each table matches those in Access. Also, ensure that all table relationships and indexes were correctly migrated. You should then:

• Reconfigure security to use Windows Authentication or SQL Server Security.

• Rewrite any crosstab queries to work with SQL Server.

• Modify your forms and reports to request a minimal amount of data from SQL Server.

• Use SQL Server views for any queries joining multiple tables.

• Update your code to use the ADODB data access libraries.

• Normalize your database structure to effectively use SQL Server.

Reconfiguring Security

SQL Server uses integrated (Windows) security as well as its own security model. It does not support Jet security, that is, the security model used in Access. You will need to re-create user groups and users, and set database access permissions for them.

For a guide for implementing security in SQL Server, see https://msdn2.microsoft.com/library/aa905156.aspx.

Rewriting Crosstab Queries

When you upsize your database by using the linked tables upsizing method, your existing queries will continue to work. However, all of the data is now stored in SQL Server and is retrieved through an ODBC connection to Access when needed. Complex queries such as crosstab data will take a long time to run, because Access needs to get all the records from all the required tables over the network from SQL Server and then perform processing on the client.

To improve performance of your crosstab queries after upsizing, you generally need to rewrite them to work efficiently with the client/server application architecture.

SQL Server does not support the Access TRANSFORM keyword for crosstab queries. The best way to re-create your crosstab queries in SQL Server is to use a third-party tool.

Using the Relational Application Companion (https://www.rac4sql.net/), you can visually design crosstab queries that work in SQL Server. You can store any crosstab results in a SQL Server table for later retrieval.

AGS Crosstab Builder for SQL Server 2000 (https://www.ag-software.com/?tabid=21) provides a wizard-style interface to generate crosstab results from standard SQL statements. You can export the query results to a standard SQL Server table.

Modifying Forms and Reports

Access developers often make use of record search forms, which allow users to specify criteria to find a filtered set of records.

Typically, when a user specifies search criteria and clicks the Search button, your code executes a query that retrieves every record from the required tables and applies a filter to return the desired results set.

While this method is flexible and powerful in that the filter can be cleared and a new filter applied on the same results set for a new search, major performance problems can occur when attempting to use a SQL Server database as your form back end with the same code.

For example, if you had a product search form to search on the Product Name field in the Northwind Products table, the code would typically display the products form, set the record source for the form, and apply a filter to the records, based on the criteria entered:

Instead of retrieving the whole set of records using the form filter behind the forms to get the desired result set, you should change the recordsource property of the form to include a WHERE clause, so that the filtering will occur on the server and the filtered set of results will be returned from SQL Server:

Private Sub btnSearch_Click()
     DoCmd.OpenForm "Products"
     Text0.SetFocus
     Form_Products.RecordSource = "SELECT * FROM Products WHERE 
     ProductName LIKE '*" & Text0.Text & "*'"
End Sub

Also, change the record source of the actual Products form to ensure that it does not attempt to retrieve any records from the Products table when it is opened (see Figure 9). The most efficient and simplest way to do this is to add a SQL WHERE clause and add the condition 1 = 2. (This ensures no records are retrieved, because 1 = 2 will never equate to true.)

When the user searches using the search form, the record source will be modified (as shown previously) to determine which records the form will retrieve.

Figure 9   Change the record source of your form so that it retrieves no records (this will be handled by the code)

Using Views for Queries with Complex Joins

When you upsize your Access database, linked tables are created in place of your existing Access tables. As previously mentioned, these tables provide access to the data in SQL Server. They do not store any data of their own.

When a linked table is queried (through an Access query or otherwise), every record in that table is returned from SQL Server, irrespective of any filtering through the SQL WHERE, UNION, or JOIN clauses. For example, the following query would cause the entire Customers and Orders tables to be returned to Access from SQL Server:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName,
     Customers.Address
FROM Customers INNER JOIN Orders
     ON Customers.CustomerID = Orders.CustomerID;

The join would be performed in Access after all the data had been returned. This could become a major issue in instances where you are joining multiple tables in an Access query. Every single record of every single table in the query will be returned over the network.

In situations where you have queries incorporating multiple table joins, a better solution would be to create a SQL Server view and modify your Access queries to request data from that view (instead of directly from the joined tables). Using a view, SQL Server performs any table joins and filtering on the server side, and only returns the filtered set of data, which can vastly improve network and system performance.

Follow this procedure to use views:

1. Create a view in SQL Server performing the same function as the Access query. Using the preceding example, the SQL Server view can be created using the same query.

2. In Access, ensure the database with the query is open. Click File, click Get External Data, and then click Link Tables.

3. In the file browser dialog box, select ODBC Databases () from the list as shown in Figure 10.

   

   Figure 10   Select an ODBC data source to use for the linked view

4. In the Select Data Source window, click New. Scroll down the list, select SQL Server, and then click Next. Type the desired data source name, and then click Next and Finish to close the wizard. Type a description and enter (local) (or the network location of your SQL Server instance if not on the local computer) as shown in Figure 11. Click Next to continue.

   

   Figure 11   Choose a description and specify the SQL Server instance for your ODBC database connection

5. Select With Windows NT authentication (or type the SQL Server Login ID and Password if you specified it earlier). Click Next to continue.

6. Select the Change the default database to check box and select the database you created when you ran the Upsizing Wizard.

7. Click Next on the following dialog box, and then click Finish and OK on the window that appears to complete creating the data source.

8. Your new ODBC data source should now appear in the list as shown in

9. Figure 12. Select it and click OK.

   

   Figure 12   Your newly created ODBC data source should now appear in the list of data sources

10. In the Link Tables dialog box, scroll down and locate the view you created in step 1. Click OK and select a field to use for the unique identifier (recommended) or click Cancel to bypass this step (not recommended).

11. The new view should now appear as a linked table in the table list. Modify your query to use the view. For example, the query:

    SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName, 
    

     Customers.Address FROM Customers INNER JOIN Orders      ON Customers.CustomerID = Orders.CustomerID;

would be changed to:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">

SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName,      Customers.Address FROM dbo_vwOrders;

Changing Your DAO Code to Use ADODB

Microsoft Data Access Objects (DAO) is the most common method of programmatically working with data in an Access database. It is used in Access front ends (forms, reports, and modules) for such functions as performing calculations on data.

Now that your database is upsized to SQL Server, the performance of your existing DAO code may decline. This is because DAO is optimized for direct access to Access data. Now that data is being retrieved from SQL Server through an ODBC connection (through linked tables), several performance bottlenecks become apparent.

The most efficient and consistent way to use data from SQL Server from your Access front end is through Microsoft ActiveX® Data Objects (ADO). ADO provides a method of connecting directly to the SQL Server database without having to use the ODBC connection used by linked tables.

The DAO object model is similar to the ADO object model. However, there are some differences in the object models that you must fix in your front end for ADO to work.

For example, DAO code to open a database and open a recordset in the database would look like:

Dim db as Database
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset(Employees)

To convert the code to use ADO, add a reference to the ADO libraries. Click Tools, and then click References in the Visual Basic editor and select the Microsoft ActiveX Data Objects item (as shown in Figure 13). Then use the following code in place of the DAO code:

Dim cnn as ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb;"
Dim rs as ADODB.Recordset
rs = New ADODB.Recordset
rs.Open "Employees", cnn

Figure 13   Add a reference to ADO to use the new data access objects in your Access front end

Note that if you do not see the ADO library in your list, you need to install the Microsoft Data Access Components (MDAC). MDAC can be downloaded from https://msdn2.microsoft.com/data/aa937695.aspx.

More information about DAO and ADO can be found at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp?frame=true.

Normalizing Your Database Structure

Access databases often have a flat structure, that is, there are few tables and many fields in each table. Access works efficiently with tables containing many fields and fewer joins between tables, whereas SQL Server works more efficiently with narrower tables containing fewer fields and more relationships between tables. Combined with properly used indexes, you will see a vast improvement in database reads and updates, because SQL Server is efficient at selecting rapid, efficient joins between tables.

For more information about normalizing your database in SQL Server, see https://msdn2.microsoft.com/library/aa933055.aspx.

Conclusion

The Microsoft Upsizing Wizard is a tool provided as part of Microsoft Access to assist in migration of your Access database investment to Microsoft SQL Server. The Upsizing Wizard analyzes your Access database structure and objects, and it makes decisions about conversion options.

For the Upsizing Wizard to make an error-free, accurate migration of your database, it is imperative that you take steps to prepare your database for upsizing. It is also important that you are aware of the options presented in the Upsizing Wizard so that educated decisions can be made when upsizing. After a successful migration, you also should take steps to optimize the data flow between Access and SQL Server and configure the SQL Server database for security and performance.