Migrating Access to SQL Server/SQL Azure using SQL Server Migration Wizard for Access
A great deal of business information is stored in Microsoft Access. When it comes time to share that information, being in Access can hinder the maintenance and sharing of the application. One solution available is to migrate Access applications into the cloud, either completely or in part. Fortunately, it's not difficult to migrate Access data to SQL Server. In the past, we may have used the Upsizing Wizard in Access, but a more recent tool provides a great deal more functionality resiliency of the migration of schema and data.
SQL Server Migration Wizard for Access
The tool we need is the SQL Server Migration Wizard for Access (SSMWA; wizards are also available for Oracle, Sybase and MySQL, but those are other posts). There are two editions available right now-one for migrations to SQL Server 2005, and one for SQL Server 2008/SQL Azure. The SSMWA can be downloaded from:
This link points to the current version at the time of writing, you might want to make sure there isn't a more recent version before downloading.
Again, we need the SQL 2008 version to get to migrate to SQL Azure. Both 32- and 64-bit editions are installed; before using the SSMWA, we need to check Access and use the appropriate version. If we use the 64 bit SSMWA with 32-bit Access, we'll get an error (see below). We're going to use the wizard interface for our migration. This is not a requirement-migration can be set up manually, which gives us greater control over the mappings from source data type to destination data type, as well as metadata migration.
After downloading, it's time to Install the SSMWA. When we first run the SSMWA, we are asked for the licensing information. There is no cost for the SSMWA, but a license is still a requirement. We only need to do the licensing the first time we run the SSMWA.
In order to obtain a license, we need Windows Live ID. Follow the registration link, provide the contact information in the licensing form, save your profile, and the license will be ready to download to your machine. Save the license to the license directory (from the License Management window). Once the license is saved, click the "Refresh License" button, and the license should be refreshed, as seen below.
Click OK, and the actual wizard starts:
The database connections and type mapping details are stored in a project model. Here we create the project, and choose the target database. If we're using the wizard for SQL Server 2008, this is the place where we choose SQL Server or SQL Azure.
On the next screen, we add Access database(s) we want to migrate.
Next, we choose the table and query objects to migrate. Tables and data will be migrated, while Access queries will be turned into SQL Server views and migrated.
Now we connect to SQL Azure. In order for us to connect, the database needs to exist on SQL Azure, and must be created ahead of time via the SQL Azure portal, or another tool which can create databases on SQL Azure-the SSMWA cannot do so. Also note that the username needs to be in the @ format.
We can choose to automatically link the Access application to the newly migrated tables in the target database. This way the Access application can use the migrated tables immediately.
After this, the migration begins. The first step is comparing the source and target databases, and building load packages.
We're presented with a confirmation of the objects to synchronize. In our case, since this is a fresh migration, all of the objects will be created anew.
The wizard then starts to create the objects.
After object creation, data are migrated.
Once convert/load/migrate is done, we can get view reports regarding the outcomes of the migration. Below we can see the conversion was not completely successful.
Should anything not migrate cleanly, the report details what all went wrong or was incompatible. This is important information, and the issues presented may need to be addresses before the application is used.
Now is the time to review the migration and run some test queries. If we're not happy with the migration, we can change type mappings and other details and run the import again (remember to drop the tables/views or delete the database before starting over).
We're now ready to use our newly migrated database! If you chose to link the application to the migrated tables, your application should work. Or, it's time for the next steps in development.
Recommended Best Practices
As with any operation of this type, it's a good idea to practice the migration once or twice, using a backup of your Access application and a non-production SQL Server.
Once the test migration is complete, do a sanity check purposes, especially looking at the data types of the migrated fields. An unexpected data type conversion can cause problems down the road. Some conversions to look at are boolean to bit fields, which can affect queries using 'No' as the field value, which is a legal value in Access, but not in SQL Server. Other field mappings which may affect future application performance are doubles to floats, which may cause imprecision in the math, or integers to smallint, which may not be able to store large enough numbers.
Incorrect Bit Edition Error message
As mentioned above, both a 32-bit and 64-bit edition of the SSMWA are installed, and the edition we use must be the same as our Office installation. If the wrong edition of SSMWA is run, the following error will the thrown:
Access Object Collector error: Database
Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154. This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. Please run SSMA in 32-bit (or 64-bit) mode or consider updating your connectivity components from http://go.microsoft.com/fwlink/?LinkId=197502.
An error occurred while loading database content.
To c heck the version of Access 2010, look under File >> Help, and we can see the bit depth.
MESCIUS inc.