Migrating an Existing Access Database to an Access Project

Microsoft Access has an Upsizing Wizard to help you migrate existing Access databases to SQL Server. In this section, I cover some tips for making the migration smoother. You will then upsize the Northwind sample database that comes with Microsoft Access so you can see how the process works.

Changes to an Existing Database

Before migrating your existing Access application to an Access Project that uses SQL Server, here are some tips that will make the migration go more smoothly:

• Consider removing any spaces in your table names and, of course, if you change the name, also change all the places that reference the table name. If you do not remove them, you will later have to use brackets to refer to SQL Server tables with spaces in their names (for example, [Table Name]).

• Make sure that each table has a unique index. If you have established primary keys, that should be sufficient.

• Make a backup copy of the ACCDB file in case things go wrong.

• Check the dates in your Access tables. Most problems with upsizing occur with date conversions. For example, Access and SQL Server have different date ranges that are acceptable. SQL Server covers only the period from Jan 1, 1753 to Dec 31, 9999. Access allows dates in the range from Jan 1, 100 to Dec 31, 9999.

With these tips in mind, let’s walk through using the Upsizing Wizard to see how this works.

Using the Upsizing Wizard

As previously mentioned, you can use the Upsizing Wizard to convert an existing Access database to an Access Project for the client and SQL Server for the database. You’ll walk through the numerous steps involved in this process to upsize the sample Northwind database to a SQL Server database.

1. Open the d5atabase you want to upsize, which in this example is the Northwind database in the Samples directory of Office. To start the Wizard, click on Database Tools and then click on SQL Server on the Move Data ribbon. A screen similar to that shown in Figure 10-28 will appear.

   
   Figure 10-28

2. Select the option Create New Database and click the Next button.

3. A screen similar to the one shown in Figure 10-29 appears next. Specify the SQL Server instance that should hold the new database. Also, fill in the security information, such as whether to use a trusted connection or a login and password. Then, provide the name of the new SQL Server database. Click Next.

   
   Figure 10-29

4. As shown in Figure 10-30, you are then prompted to specify which tables to upsize. Figure 10-30 has selected all tables in the database, and they appear in the Export to SQL Server area of the screen. After selecting the tables to be upsized, click the Next button.

   
   Figure 10-30

5. A screen similar to Figure 10-31 appears, allowing you to specify which table attributes to upsize. Select the options that you want included and click Next.

   
   Figure 10-31

6. A screen, like the one shown in Figure 10-32, allows you to specify the name of the new Access Project file that should store the client user interface and business logic. After specifying the ADP file name, click Next.

   
   Figure 10-32

7. The final screen of the wizard, as shown in Figure 10-33, allows you to specify whether to open the new ADP file or keep the ACCDB file open. After selecting your preference, click the Finish button.

   
   Figure 10-33

8. The Upsizing Wizard then displays a progress bar and works its way through each object in the database that is being converted.

9. As shown in Figure 10-34, a report is displayed when the Upsizing Wizard completes. The report provides numerous details about exactly which objects were upsized, any errors that were encountered, and so on.

   
   Figure 10-34

10. Figure 10-35 shows an example of a table that was upsized to SQL Server and illustrates how the data types were changed from Access data types to the equivalent SQL Server data types.

    
    Figure 10-35

11. Figure 10-36 illustrates the fact that the existing Queries were converted to stored procedures, functions, and views depending on their purposes.

    
    Figure 10-36

12. Figure 10-37 illustrates errors that occurred in upsizing two different queries. Any errors that prevent Access from upsizing certain elements will need to be corrected by using the correct SQL Server syntax. Even if Access could successfully upsize the query, you should review the query to make sure the modification still serves the intended purpose.

    
    Figure 10-37

13. After the upsizing is completed, the new Access Project is displayed, as shown in Figure 10-38. If you had instead selected the option to keep the existing ACCDB open, that database would have been displayed instead.

    
    Figure 10-38

14. If you navigate to the Queries objects in the Database Window, you can see that the previous queries were upsized to various stored procedures, views, and functions. This is illustrated in Figure 10-39.

    
    Figure 10-39

After the database has been upsized, you must correct any errors that were encountered and retest the functionality of the database to ensure that the objects were converted correctly. After these issues have been resolved and the application has been tested, you are ready to let your users work with your new SQL Server application from the Access Project.