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
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.