Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Access to SQL Server: The Upsizing Wizard

By Kathi Kellenberger, (first published: 2005/02/22)

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.

Total article views: 30084 | Views in the last 30 days: 27
 
Related Articles
ARTICLE

Upsizing the Access Database into the SQL Server

SQL Server and Access are usually linked together as Access used for applications at the beginning o...

FORUM

Upsizing from Access 2003 to SQL Express 2005

Lack of any upsizing being done

ARTICLE

Access to SQL Server: Getting Started with Access Projects

SQL Server 2000 and Access databases are two technologies closely linked with the new Access ADP for...

ARTICLE

Access to SQL Server: Linking Tables

SQL Server 2000 and Access databases can be configured to work closely together. If you find that th...

FORUM

ACCESS to SQL (Upsizing)

 Why is it, sometimes a database will not allow me to upsize it to SQL ?  The Upsizing option in AC...

Tags
access    
programming    
sql server 7    
upgrading    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones