Migrating from Access to SQL Server | Mastering Dreamweaver MX Databases

No doubt, both Access and SQL Server developers find themselves migrating an occasional Access database to SQL Server. Chapter 9 provides some hints for using the Upsizing Wizard in Access to do so. In this section, we'll offer some advice from the SQL Server side of the task. Basically, you're looking at a three-step process:

Create a working version of the application for SQL Server—the Upsizing Wizard in Access can help with this step.
Optimize the database for the client/server environment.
Optimize the database structure.

What the Wizard Can't Do—Moving the Front End

SQL Server lacks a graphical interface environment, so you'll have to find some way to migrate the Access forms and reports or re-create them. Most likely, you'll convert the existing objects in another environment, such as Visual Basic or Active Server Pages (ASP) if you're creating a web application. The truth is, there's really nothing wrong with using Access as your front end.

The biggest challenge will probably be with an older Access database. If that file uses DAO, you must convert your DAO-based code to ActiveX Data Objects (ADO).

Note

You'll find a white paper on the subject of migrating from DAO to ADO at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp.

Access forms and reports are usually based on a query or a SELECT statement by way of the form's Record Source property. (See Chapter 9 for more information on using SQL to specify an object's data source.) SQL Server will need a view or stored procedure to replace that query or SQL statement. An upsized form or report will return an error if any of the following is the case:

A control refers to a parameter query. You must replace the reference with a stored procedure.
A WHERE clause or a SELECT statement refers to a control. Try using the form's InputParameters property.
Jet queries reference only the underlying data source's fields. SQL Server's T-SQL includes the data source, in the form [tablename].[columnname]. (The brackets are optional unless the name contains a space character.) Update all references to include the data source.

Optimizing the Client/Server Environment

Once you have a working application with all the pieces in tact, you should optimize the SQL Server database application for the client/server environment. The following are a few guidelines that should improve performance across the network:

Change Recordset object types to Snapshot if no updating is involved.
Use pass-through queries when possible.
Create local lookup tables for data that doesn't change, such as states, countries, and so on.
Retrieve the smallest amounts of data possible.

Optimizing the Structure

SQL Server provides several graphical interface tools that can help you improve the database's structure. Once you've got the database application running smoothly in the client/server environment, consider testing the following and making the necessary improvements:

Use SQL Profiler to record server activity in a trace file (.trc). Review it regularly to get an idea of what's going on behind the scenes that could possibly slow things down, such as errors, redundant objects, and so on.
Review the Index Tuning Wizard for suggestions on more efficient index structure.

Executing T-SQL in Code

You can connect to a SQL Server database in several ways. When the connection involves a web page, you'll find the Command object superior to the Connection object. Chapter 9 contains a section on both the Connection and the Command ADO objects. The object is virtually the same for T-SQL and SQL Server. Refer to the "Executing Jet SQL in Code" section for a short introduction to these two objects. Chapter 10 provides a more thorough discussion.

Tip

There's almost no way you can choose SQL Server and not eventually commit to learning ADO.NET. Right now, the .NET environment supports ADO, but who knows how long that will last? On the other hand, ADO.NET has a lot to offer, and it's worth at least looking into. For an introduction to ADO.NET, see Chapter 15.