Share via

Move Access Data to the Cloud

  • Article

Microsoft SQL Server Migration Assistant for Access allows you to move your Microsoft Access tables and queries to Windows Azure SQL Database. This article will walk through how to use the tool to move the NorthWind database to SQL Database.

Using the Migration Wizard

Once you have it downloaded and installed, start it up and you will be presented with a migration wizard to migrate the data.

The first page of the wizard gets you started with a migration project.

Make sure to select SQL Database as the option for Migration To: drop down. In the next page of the wizard we are asked to select one or more Microsoft Access 2010 databases to upload to SQL Database.

In this case I am uploading the Northwind database from My Documents folder. After clicking Next, SQL Server Migration Wizard takes a few minutes to scan the Access databases that you selected and then prompts you for the tables and queries to upload to SQL Database.

In this example I am going to upload all the tables in the database and none of the queries. Click Next takes me to the next wizard page.

Here I need to enter in the information to connect to my SQL Database. The database must exist, since the SQL Server Migration Assistant doesn’t have the ability to create a new database. You can create a new database via the Windows Azure Platform Management Portal that is also a good place to get your SQL Database server name, administrator login, and password if you need them. Clicking Next takes you to the Link Tables step.

This screen gives you the option of linking your tables in SQL Database to your Access database using ODBC links. The local tables you chose to migrate will be renamed with the suffix “_local” and the linked tables will be created with the original table names. This allows you to continue to use all of the forms, queries, reports, macros and modules in your Access application against the SQL Database without having to make any other application changes. With your back-end data moved to SQL Database, you can now access your data from anywhere in the world and your data is stored in a managed, scalable environment.

One scenario I have run into in the past is that you have several people that do not share a file server want to update your Access database, which requires each of them to get the Access database in serial, perform the updates and email it to the next person. When you link your tables and store the data in SQL Database, all of the users can update their data in parallel, meaning that you can pass out the Access file to everyone at once.

Not clicking the Link Tables check box means that the data (in my example just the tables) will be uploaded and the Access database will not be changed.

Once you click Next the SQL Server Migration Assistant will start migrating your data, it will look like this:

In my case since the database was empty, I got an addition dialog to confirm what schemas I wanted to move from Access to SQL Database, that dialog looked like this:

If you choose to link tables, The SQL Server Migration Assistant will ask for the login and password to use when connecting to SQL Database. The dialog looks like this:

This gives you the option of storing a different login/password inside the Access database then the one you used to migrate the schema and data to SQL Database. For example, if you want the Microsoft Access database to have read-only access to the SQL Database you could enter a user with read-only permissions. For more information about creating a read-only user see this blog post. It considered best practice not to embed your SQL Database Administrator login and password into the Access database. Access will than warn you that the login and password are stored as clear text:

One more thing to note is that the login is in the form user@server. Once the SQL Server Migration Assistant is done you are presented with a Close button, however if you have errors you should view a report for each section of the migration

In my case I wanted to see what the 6 errors where about when migrating Northwind. I never expect the database to transfer 100% clean between any two different databases technologies. Here is what my report looks like:

In this case it appears like a data type in Access is not supported in SQL Database, I need to find out what those types are and make some adjustments to my SQL Server Migration Assistant solution to correctly convert those data types. The SQL Server Migration Assistant lets me drill down into the tables to see specific error, and gives me an estimate of the time it will take to correct the error, hopefully they are all roughly the same problem and I will not have to spend considerable time getting it to work.

Interested in proofing the data to make sure it got migrated correctly? Consider using the Management Portal for Windows Azure SQL Database.

Summary

That was just a quick summary of the SQL Server Migration Assistant migration wizard. The assistant is extremely robust with a reloadable solution file that lets you tune your migration, and visualize what is going on. Do you have questions, concerns, comments? Post them below and we will try to address them.

See Also