MS Access to SQL Server 2000 Part 1: The Upsizing Wizard

Introduction

Even though I have been SQL Server DBA for two and a half years, I have always been a fan of MS Access. While Access is not intended to store Enterprise-wide data as SQL Server is, you can do just about anything with its reports and forms, especially if you're not afraid to write a little VBA code once in a while. It is used frequently as the client for SQL Server database applications. In this series of articles, I will explain three ways to link SQL Server tables in MS Access and offer some pointers along the way on creating your first SQL Server back end / MS Access front end app.

Designing a database, creating tables, forms, queries, and reports in Access are beyond the scope of this series. I'm going to assume that you know clicking

"Next" moves you to the next step in a wizard and know to click "OK" to accept changes. I also will assume that you have the necessary privileges and permissions in SQL Server. To view the steps of each example a link opens a second browser. You will find "Back" and "Next" links below each screen shot image.

Using the Upsizing Wizard

The Upsizing Wizard is a handy tool that ships with MS Access. I first used the tool several years ago to port an Access 97 database to SQL Server 6.5. The idea here is that you build your database and application in Access and then use the tool to upload the same tables to SQL Server. I don't really recommend this approach, however. I think it is better to create the tables in SQL and just link to them within Access from the start. Sometimes, however, the data in a legacy MS Access solution must be uploaded to SQL, and here is one way to do it.

You may be familiar with the sample database Northwind that is installed with SQL 2000. It's also available as an Access database. I'm going to use the Access version of Northwind as a sample database in this article.

The Upsizing Wizard can be found on the Access menu at Tools--> Database Utilities--> Upsizing Wizard. I did a complete Office 2003 install, so if you are missing the Upsizing Wizard, you may have to locate your Office CD to add that feature.

Before you do anything, make a backup copy of your Access database!

Open Northwind.mdb, or your Access database, and start up the wizard. Complete the following steps to upsize your tables to SQL Server:

• Select whether you would like to create a new SQL database or use an existing database. I chose to create a new database.
• Provide connection information and the new database name.
• Choose which tables to upsize.
• Select which properties to include.
• Choose application changes.
  • Create a new Access client/server application. With this option, you create a new Access Project application. More about this in article three.
  • Link SQL Server tables to existing application. This option links the SQL tables to the existing database and was chosen for this example.
  • No application changes. With this option, you create the tables in SQL and upload the data but do not maintain a link.
• Click Finish to complete the wizard.

View the Upsizing Wizard steps

Once the wizard completes all of the work, a lengthy report appears on the screen with details about what was done. Print the report now if you think you will need it; you won't be able to run it again.

The original tables were renamed to tablename_local, and now links to SQL can be seen along with the Access tables. If you hover over the linked table name with the cursor, the connection string will be displayed as a tool tip.

Figure 1. Renamed Access tables and linked SQL tables

That was pretty easy, but did it break the application? No, the forms and reports still work! Your Access application may or not break when the data is upsized to SQL. Be sure to test your application thoroughly. I found an article on Microsoft's site about the Upsizing Wizard that stated any DAO code must be converted to ADO code before it will work with SQL Server. Luckily, I had never heard that advice when I was a developer. I found that ADO worked great with Active Server Pages and Visual Basic 6, but was just difficult to use in Access. I was pleased to see that Access 2003 sets references to both libraries by default.

What about the primary keys, foreign keys and constraints? By running sp_helpconstraint, I see that nothing was lost.

Figure 2. Constraints on Order Details

One common problem with data upsized to SQL from Access is invalid dates. Access allows a much greater range of dates than does SQL Server 2000. According to SQL Books Online, the datetime data type accepts dates between January 1, 1753 through December 31, 9999. Access allowed me to type in a date from the year 101. If you have a database about historical documents you may need a wider date range than SQL provides. For most businesses SQL's dates work fine. Just like SQL Server's constraints, Access allows the developer to define validation rules for data to prevent some data entry problems. The developer can also add validation of data to forms. If the dates in your Access application do not have any validation checking, run some queries to find and clean up problematic dates before upsizing.

Conclusion

As I mentioned earlier, if you have a choice, create your database in SQL Server or MSDE from the beginning. If you have a legacy Access application that now must be upsized, the Upsizing Wizard is a handy tool. Once your data resides in SQL make sure you test your application and test again! In my next article, I'll show how to link to an existing SQL database.