Express Essentials: Migrating from Access 2007 to SQL Server Express

Microsoft Access is without a doubt the world’s most popular database, and migrating databases from Access to SQL Server 2005 Express is a common task, especially when you want to take advantage of SQL Server Express’ multiuser capabilities. There are a number of tools that you can use to migrate Access databases to SQL Server Express. One of them is SQL Server’s Import Export Wizard, which I covered in my April 2007 column, "Importing and Exporting Data." However, the Import Export Wizard is essentially table-oriented and it doesn’t handle all of the objects that you might want to migrate. Fortunately, Access provides its own Upsizing Wizard that you can use for migrating data to SQL Server Express (or other SQL Server editions).

To migrate an Access 2007 database to SQL Server Express, first start Access 2007 and then open the database that you want to migrate. If you see the Security Warning message then you’ll need the click the Options button and select "Enable this content from the Microsoft Office Security Options" dialog. Next, on the ribbon, click the Database Tools tab. In the Move Data section of the ribbon click on the SQL Server option to start the Upsizing Wizard.

The first dialog shown by the Upsizing Wizard asks you whether or not you want to use an existing database or create a new database. For the typical migration, you would select the "Create new database" radio button and click Next. The next dialog asks you about the name of the target SQL Server system. If you’re running Access and SQL Server Express on the same system, then go ahead and use the default value of local. If you’re using Windows authentication (which is the default for SQL Server Express) check the Use Trusted Connection box. If the target SQL Server system is on another networked system, then you’ll need to supply the SQL Server instance name and the required authentication information. This is in the form \SQLEXPRESS (where you replace with the computer name running SQL Server Express). This dialog also prompts you for the name of the database that will be created. By default the Upsizing Wizard appends the value of SQL onto the source Access database name. You can accept that value or change it to a different name. Clicking Next displays a dialog that asks you what tables you want to export to SQL Server Express. Click the double arrow to export all of the tables then click Next. The Upsizing Wizard will prompt you about exporting the other database objects. You’ll want to use the default values and make sure that Indexes, Defaults, Validation rules and Table relationships, and DRI are checked and then click Next.

Finally, the Upsizing Wizard asks if you would like to modify the existing Access database. If you still intend to use your Access forms and reports, you can select either "Make a new Access client/server application to create a new Access project" (.adp), or you can select "Link SQL Server tables to existing application" to use linked tables. If you intend to use a different data access method, select "No application changes" and click Next and then Finish to run the wizard.

Depending on the size of your database the Upsizing Wizard could take several minutes to run but after it’s finished you’ll have a brand new SQL Server Express database.

Learn more from "Tools for Migrating Access to SQL Server," May 2011.