Upsizing an Access Database to SQL Server

Once an Access database outgrows the system, you might decide to upsize it to SQL Server. (For more information on SQL Server 2000, see Chapter 8.) The upsizing process isn't rocket science, but it can be labor intensive, especially if your database wasn't originally written with conversion in mind.

Regardless of your reasons and your experience, upsizing can be a difficult process if you go into it unprepared. Knowing what the wizard can and can't handle before you start can mean the difference between a successful conversion the first time and repeating the process a few times until you finally get it right. In this section, we'll discuss the hot spots and how to defuse them before running the Upsizing Wizard.

Develop with an Eye to the Future

If there's any chance SQL Server is in your future, you can enforce a couple of simple rules when developing an Access database that will simplify any upsizing process:

• Don't include a space character in object names. Access and SQL Server both require any table or field name that contains a space character to be enclosed in brackets ([ ]). Access automatically adds the brackets, but SQL Server doesn't. The wizard will upsize the tables, but in SQL Server, you must remember to add the brackets yourself. Avoid potential errors (forgotten brackets) down the road by eliminating the cause—omit space characters in your Access object names.

• If you're in the habit of using SQL statements as the data source for controls, forms, and reports, start using fixed queries instead. The wizard will upsize these controls by converting the SQL statements to views or stored procedures. Unfortunately, the wizard can't distinguish one statement from another, which means all statements are converted, even duplicates. If you have multiple controls based on the same statement, you'll end up with a lot of duplicate views (or stored procedures)—one for each occurrence of the SQL statement. You'll have to delete all the duplicates and reset the appropriate source properties to the one remaining source. That can take some time and can be avoided by using fixed queries—at least when you have more than one object using the same query.

Upsizing Options

Moving an entire database to an entirely new format isn't a task to undertake lightly. Fortunately, the Access 2002 Upsizing Wizard has several versions under its belt, and with each new version it gets easier and more efficient. With the inclusion of Access Projects (ADP files), the Upsizing Wizard offers more than ever in the way of upsizing options:

• You can convert an Access MDB file to an ADP file, which utilizes SQL Server 2000. All the data and objects are actually stored on SQL Server, but the familiar Access interface is still available, and that can mean a lot to the developer unfamiliar with SQL Server. An ADP project is not only a great database product, it can be an effective training tool for the would-be SQL Server developer. (Even though you're using Access objects, Jet is no longer an issue.)

• You can link server tables to local Access tables and keep your Access MDB file intact, and you end up with a typical front-end/back-end arrangement. You're really just creating an ODBC link to the newly upsized tables on SQL Server. Unlike the other options, you won't have to modify the forms and reports. On the other hand, if performance is a serious issue, keep in mind that the queries are still processed locally, which may slow things down.

• You can upsize the data to SQL Server 2000 by creating an entirely new database on SQL server without altering the Access application you upsized.

Note 

To run the Access 2002 Upsizing Wizard, choose Tools ® Database Utilities. Select Upsizing Wizard, and choose one of the three options discussed in the accompanying section.

Before You Start

You can't just jump into the upsizing process willy-nilly—preparation is the key to a successful conversion. Before you actually do anything, you must deal with a few matters.

Contact your SQL Server administrator to check your security roles. You must have permission to create a new database in SQL Server before you can upsize an Access file. In addition, the process requires Jet Read Design permission on all Access objects you plan to convert. If VBA modules are password-protected, you'll need that password.

Note 

Access MDE files can't be upsized. You must upsize the original MDB file.

Create a backup of the Access database before you start the Upsizing Wizard. Store that copy in a safe place—ideally on another server.

Tip 

Use the Access Documenter feature to run a report that lists all the properties for the tables, queries, fields, indexes, and relationships. Once you've upsized to SQL Server, you can refer to this list to ensure that everything made the conversion and to re-create those items that didn't. To run this feature, choose Tools ® Analyze, and then select Documenter.

Preparing the Access MDB File for Conversion

In a perfect world, a simple click would thoroughly convert everything in your Access MDB file to SQL Server, but that's not how it happens. The wizard can't yet handle several incompatibilities between the two systems. However, knowledge does give you an upper hand. If you know what to modify before upsizing, the process is often trouble free.

Find and Delete Unsupported Dates

One of the biggest discrepancies between the two systems is the dates each system supports. Access 2002 supports dates from January 1, 100, to December 31, 9999; SQL Server supports dates from January 1, 1753, through December 31, 9999.

This seemingly simple difference creates one of those between-a-rock-and-a-hard-place issues if you have dates that fall before January 1, 1753. The wizard won't upsize any table that contains an invalid date—and, yes, we mean the entire table. The wizard doesn't just skip the record with the offending date; it skips the entire table.

Note 

When encountering any type of error in a table, the wizard generally refuses to upsize the entire table. This decision actually makes sense. You're much more likely to realize a table's missing than a lone record.

Finding the Dates

To find problem dates, run a query on any table that contains a date field and use the following expression to find dates that falls outside SQL Server's date range:

datefield < #1-1-1753#

Once You Find the Dates

There's no easy way to deal with these records. The best solution is to simply delete any dates that SQL Server can't interpret. If you must keep the record, consider converting the date field to a text field and then writing a stored procedure or a user-defined function (within SQL Server 2000) that does the date arithmetic for you.

Add a Unique Index to Each Access Table

The wizard requires that each Access table have a unique index. If there's no unique index, the wizard won't upsize the table. The solution is simple enough:

• Open the table and add an AutoNumber field to the table.

• Or, choose an existing field and add the index.

To add an index to an Access table, open the table in Design view and then select the appropriate field row. Next, select Yes (No Duplicates) in the Indexed property. Just remember to remove the AutoNumber field or the index from the table afterward.

Warning 

The Upsizing Wizard won't upsize a table that contains an index based on more than three fields.

Revamping Duplicate SQL Statements

Many Access developers use SQL statements as the data source for controls, forms, and reports. You can even use the same statement for multiple objects. The wizard converts these statements into views or stored procedures, but it creates one for each instance—even if there are duplicate statements.

To avoid the laborious task of removing all those duplicate views (or stored procedures) and then resetting each object's source property, check your Access objects for duplicate SQL statements and convert them to fixed queries in Access, before you upsize.

Modify Field Size Properties

Access lets you create a primary and foreign key relationship on fields in which the field size settings are different; SQL Server doesn't. Check your primary and foreign key field size properties and make the necessary changes before upsizing. The wizard will refuse to upsize any tables in which the field size settings aren't the same.

Delete SQL DISTINCTROW

SQL Server doesn't support the SQL DISTINCTROW keyword. As a result, the wizard will refuse to upsize any query that contains this keyword (and that includes SQL statements used as the data source for an object). Most of the time, you can simply substitute DISTINCT for the DISTINCTROW keyword . Be sure to carefully test the query after modifying because the two keywords aren't always interchangeable.

Note 

Jet security won't upsize at all, and there's nothing you can do on either side of the conversion process to save any piece of it. Simply forget Jet security and learn SQL Server security.

When It's All Over

Don't be discouraged if your first attempt to upsize an Access MDB file fails miserably. However, if you follow the advice in the previous section, you have a good chance of upsizing without too many errors. Do expect a few problem spots though; upsizing is seldom completely trouble free. In the worst case, you can note the major problems, return to the Access MDB file, make changes to avoid those problems, and then try again. In fact, you might have to repeat the process several times if you're working with a complex database.

If you're unfamiliar with SQL Server, the first thing you might notice is the absence of any queries. SQL Server doesn't use queries—it relies on views, stored procedures, and user-defined functions (UDFs).

You might find that a few queries didn't upsize at all. For instance, the wizard will try to upsize a subquery by upsizing the outer SELECT (the main SELECT) first. If the wizard encounters a problem, it will abandon both SELECT statements. In this case, you'll have to rewrite the query in SQL Server yourself.

As a general rule, Select, Append, Delete, Make-Table and Update queries update to a stored procedure. Any action query that contains a nested query or a parameter, crosstab, SQL Pass-Through, Data Definition Language, or Union query must be converted manually to a view or a stored procedure.

Tip 

If you find yourself faced with reconstructing a query that simply won't upsize to SQL Server, cut and paste the SQL statement from the Access SQL window to the SQL Server Query Analyzer and begin the process there. The analyzer will provide insight into Jet SQL's incompatibilities with T-SQL. Take advantage of the analyzer's error messages—don't try to rewrite the query on your own unless you're an expert at SQL.

SQL Server 2000 supports Access 2002's extended properties, and the wizard should successfully convert most of them. However, you'll have to manually deal with any property not in the following list:

• Description

• Format

• Input Mask

• Caption

• Decimal Places

• Hyperlink (is upsized, but doesn't work)

• Row Source Type

• Row Source

• Bound Column

• Column Count

• Column Widths

• List Rows

• List Width

• Limit to List

In addition, SQL Server converts all Validation Rule settings to constraints. Be sure to open each table and check each constraint to make sure it converted properly. You might need to modify a few slightly.