MS-Access / Getting Started

---

SSMA

SSMA is a more generic tool than the Upsizing Wizard and is designed to migrate data from various databases into SQL Server. SSMA is free and can be downloaded from the Microsoft sites.

Note: The version of SSMA described here is 5.0 for Access, upsizing to either SQL Server or SQL Azure.

After you download and install SSMA on a 64-bit version of Windows, you will find two program shortcuts, one for 32-bit Access and the other for 64-bit Access. If you try to run the wrong version, you will see a message warning that you are running the incorrect version of SSMA for the available Access libraries. If there is a problem with any libraries, or you install SSMA on a computer without Access 2010, then links are provided to download the Access 2010 runtime libraries required by SSMA. For a 32-bit Access installation, run the 32-bit version of SSMA.

The Migration Wizard

When you start SSMA, by default, the Migration Wizard is active and ready to guide you through the migration process. The first page of the wizard, in which you name your project and specify either a SQL Server or SQL Azure migration.

On the next page, you can add your multiple Access databases to the project, after which the following list appears from which you the select individual tables and queries in each database to be migrated.

Following the previous selections, you are able to specify the SQL Server and either select an existing database or type in a new database name. On the next page in the Migration Wizard, you can select the Link Tables check box (the default is not-selected), which replaces your tables with links to SQL Server. You will most likely want to select this option. The next page is the synchronization page, which provides a detailed roadmap of how the migration will proceed; you can leave all the options on this page unchanged when performing this for the first time.

After the conversion is run, a summary page opens. This page contains links to lists describing any problems that occurred during the migration.

One very nice feature in SSMA is a graphical view, with which you can easily see all the objects in your database and identify and investigate those objects that were not converted.

Mapping Data Types

SSMA has a mapping system for converting Access to SQL Server databases, which means that you can fully customize your data type mappings. You display this by clicking the Tables folder in the Access Metadata Explorer.

Using Schemas

SSMA does not offer an option to simply assign combinations of your tables onto different schemas, but it is still possible to make use of schemas with SSMA. In this example, you want to consider a more sophisticated conversion, for this type of conversion you will not want to use the wizard, but perform the conversion with a series of your own steps.

You start by creating a new project, and then adding your Access database.

Highlight the database, and then edit the data type mapping to use non-Unicode data types.

Because this version of SSMA doesn't seem to easily refresh the schema list, once you have registered your database it is a good idea to create a blank database and define some schemas.

Returning to SSMA, click Connect To SQL Server, and then select the SQL Server database.

After you click to highlight the Tables folder in the Access Metadata Explorer, you can select the Schema tab, and then change the Schema from dbo to Companies.

Select the tables that you want to migrate into the active schema.

Next, click the Convert, Load And Migrate icon to migrate these tables. Clear these table selections and repeat the procedure to migrate the other tables to their appropriate schemas until you have migrated the set of tables as shown.

The only restriction on this approach is that because you are effectively performing multiple migrations, SSMA cannot implement all your relationships across the schemas; you can use the database diagram in SQL Server to add back any missing relationships.