MySQL Workbench Manual  /  Database Migration Wizard  /  Microsoft Access Migration

10.4 Microsoft Access Migration

Note

This feature was added in MySQL Workbench 6.2.0.

General Information

Microsoft Windows is required because Microsoft Access ODBC drivers are only available on Windows. As for the destination MySQL server, you can have it in the same local machine or elsewhere in your network.

Preparing a Microsoft Access Database for Migration

Microsoft Access stores relationship/foreign key information in an internal table called MSysRelationships. That table is protected against read access even to the Admin user, so if you try to migrate without opening up access to it, then you will get an error like this:

[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'msysobjects'. (-1907) (SQLExecDirectW)

The steps to grant read access to the Admin are explained below.

Note

The screen shots in this documentation use Microsoft Access 2007.

  • Open up database in Microsoft Access

  • Under the Database Tools menu, click the Visual Basic macro button to open the Visual Basic (VB) console.

    Figure 10.21 Locating the Visual Basic Macro Database Tool

    The figure content is described in the surrounding text.

  • To confirm that you're logged in as the "Admin" user, locate the Immediate panel and type the "? CurrentUser" and press Enter. This should output "Admin" under "? CurrentUser" in the panel.

  • Also in the Immediate panel, type the following command to grant access:

    CurrentProject.Connection.Execute "GRANT SELECT ON MSysRelationships TO Admin"

    Figure 10.22 GRANT SELECT ON MSysRelationships TO Admin

    The figure content is described in the surrounding text.

  • Quit the Microsoft Access application

Start the MySQL Workbench Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through Database, Migrate from the main menu.

Figure 10.23 Start the Migration Wizard

The figure content is described in the surrounding text.

A new tab showing the Overview page of the Migration Wizard should appear.

Figure 10.24 Migration Overview Page

The figure content is described in the surrounding text.

Setting Up ODBC Drivers

To check if you have the ODBC driver installed, click Open ODBC Administrator from the MySQL Workbench migration overview page to open the system ODBC tool. Then, select the Drivers tab.

Figure 10.25 Checking the ODBC Drivers for Access Support

The figure content is described in the surrounding text.

Important

MySQL Workbench has both 32-bit and 64-bit executables. The ODBC drivers you use must be of the same architecture as the MySQL Workbench binaries you are using. Because Office 2007 and older was 32-bit only and even Office 2010 installs as 32-bit by default, you may need to install the 32-bit version of MySQL Workbench to migrate from Access, even if you have a 64-bit machine. If during migration you get an ODBC error about "architecture mismatch between the Driver and Application", you installed the wrong version of MySQL Workbench.

In the User DSN tab, click on Add... to create a DSN for your database file. For this example, we created one for the northwind sample database.

Figure 10.26 Adding a New DSN

The figure content is described in the surrounding text.

Setting Up Source Parameters

Click on the Start Migration from the Overview page to advance to the Source Selection page. Here you need to provide the information about the Access database you are migrating from, the ODBC driver to use, and the parameters for the Access connection.

Open the Database System combo box for a list of supported RDBMSes, and select Microsoft Access from the list. There is another combo box below it named Stored Connection. It lists saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page, along with a name for the saved connection.

The next combo box selects the Connection Method. This time we are going to select ODBC Data Source from the list. This allows you to select pre-existing DSNs that you have configured in your system.

The DSN dropdown will have all DSNs you have defined in your system. Pick the one you created for the Access database being migrated from the list.

In the Default Character Set field you can select the character set of your database. If your Access version uses western/latin characters, you can leave the default cp1252. However, if you use a localized version of Access, such as Japanese, you must enter the correct character set used by your edition of Microsoft Office, otherwise the data will be copied incorrectly.

Figure 10.27 Access Source Selection

The figure content is described in the surrounding text.

Lastly, click Test Connection to check whether an ODBC connection can be established. If you entered the correct parameters then you should see a message reporting a successful connection attempt.

Setting Up Target Parameters

Next, set up the target (MySQL) database parameters by defining the parameters that connect to your MySQL Server instance. When finished, click Test Connection to verify the connection definition.

Figure 10.28 Target Database Selection

The figure content is described in the surrounding text.

Select the objects to migrate

Next, you should see the reverse engineering of the selected database objects progress. At this point, the migration wizard is retrieving relevant information about the involved database objects (such as table names, table columns, primary and foreign keys, indexes, triggers, views, and more). You will be presented a page showing the progress as shown below.

Figure 10.29 Reverse Engineer Source

The figure content is described in the surrounding text.

Wait for it to finish and verify that everything went well. Next, the Source Objects displays a list with the objects that were retrieved and are available for migration. It will look similar to:

Figure 10.30 Source Objects

The figure content is described in the surrounding text.

In the above example, the migration wizard discovered table and view objects for our source database. Only the table objects are selected by default for migration.

Note

You can also select the view objects but you must also provide their corresponding MySQL equivalent code later (no automatic migration is available for them) so our example will leave the views unchecked. The same applies for stored procedures, functions and triggers.

Click Show Selection to configure exactly which objects you want to migrate, as seen below:

Figure 10.31 Source Objects Selection

The figure content is described in the surrounding text.

The objects on the right will be migrated. The filter box can filter the list (wildcards are allowed, as demonstrated above). By using the arrow buttons you can filter out the objects that you do not want to migrate. Before continuing, clear the filter text box to check the full list of the selected objects. Our example migrates all of the table objects so all of them are in the Objects to Migrate list, and the Migrate Table Objects check box is checked.

Review the Proposed Migration

At this point, the migration wizard converts the selected objects into their equivalent objects into the target MySQL server, and it also generates the MySQL code needed to create them. You might have to wait before the Manual Editing page is ready, but here is the initial page:

Figure 10.32 Manual Editing: Initial Page

The figure content is described in the surrounding text.

The View combo box changes the way the migrated database objects are shown. Click Show Code to view and edit the generated MySQL code that corresponds to the selected object. Additionally, you can double-click on a row in the object tree to edit the object name, or double-click the database row to change its name.

Figure 10.33 Manual Editing: All Objects

The figure content is described in the surrounding text.

The View combo box also has a Column Mappings option. It shows the table columns and allows you to review and fix the mapping of column types, default values, and other attributes.

Figure 10.34 Manual Editing: Column Mappings

The figure content is described in the surrounding text.

Create the Database Objects

Next is the Target Creation Options page:

Figure 10.35 Target Creation Options

The figure content is described in the surrounding text.

Here are options for executing the generated code in the target RDBMS (your MySQL instance from the second step), or you can dump it to an SQL script file. Leave it as shown above and move to the next page. The migrated SQL code will be executed on the target MySQL server. You can view its progress in the Create Schemata page:

Figure 10.36 Create Schemata

The figure content is described in the surrounding text.

Once the creation of the schemata and objects finishes, you can move to the Create Target Results page. It presents a list of created objects and includes any generated errors while they were created. It will look similar to:

Figure 10.37 Create Target Results

The figure content is described in the surrounding text.

You can edit the migration code using the code box to the right, and save your changes by clicking Apply. If edits were made, you still need to recreate the objects with the modified code in order to perform the changes. This is done by clicking Recreate Objects. In this tutorial we are not changing anything, so leave the code as it is, and continue on to the Data Transfer Setup page.

Transfer the Data to the MySQL Database

The next step transfers data from the source Access database into your newly created target MySQL database. The Data Transfer Setup page allows you to configure this process.

Figure 10.38 Data Transfer Setup

The figure content is described in the surrounding text.

There are two sets of options here. The first allows you to perform a live transference and/or to dump the data into a batch file that you can execute later. The other set of options allows you to alter this process.

This tutorial uses the default values for the options in this page as shown in the above screenshot. Next, the data is transferred. At this point the corresponding progress page will look familiar:

Figure 10.39 Bulk Data Transfer

The figure content is described in the surrounding text.

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process. Now, review and click Finish to close the wizard.

Verification

Now that the Northwind database was successfully migrated, next we will view the results. Open an SQL Editor that is associated with your MySQL Server instance, and then query the Northwind database. You can try something like "SELECT * FROM Northwind.customers":

Figure 10.40 Verify Your Results

The figure content is described in the surrounding text.


User Comments
Sign Up Login You must be logged in to post a comment.