Edit: added sample table output in MySQL
MySQL Workbench 6.2 introduces support for MS Access migration. This tutorial should help you get your Access tables, indexes, relationships and data in MySQL.
Preparation
Because MS Access ODBC drivers are only available for Windows, migrating from it is also only possible from Windows. As for the destination MySQL server, you can have it in the same local machine or elsewhere in your network.
MS 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, 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 Admin are explained below. Unfortunately, the Access UI for that seems to change every version, but we’ll cover at least version 2007.
Preparing a Database Under MS Access 2007
- Open the database in Access
- Under the “Database Tools”, click the “Macro -> Visual Basic” button to open the VB console
- To confirm that you’re logged in as “Admin”, type the “? CurrentUser” and press Enter, in the “Immediate” panel:
? CurrentUser
Admin - Type the following command to grant access:
CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin”
- Quit
Open MySQL Workbench and start the 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 in the main menu.
A new tab showing the Overview page of the Migration Wizard should appear.
ODBC Drivers
To check if you have the ODBC driver installed, click “Open ODBC Administrator” to open the system ODBC tool and look at the Drivers tab.
Important: MySQL Workbench has 32bit and 64bit executables. The ODBC drivers you use must be of the same architecture as the Workbench binaries you are using. So if you’re using Workbench 32bits, you must have 32bit ODBC drivers. Same for 64bits. Because Office 2007 and older was 32bit only and even Office 2010 installs as 32bit by default, you may need to install Workbench 32bits to migrate from Access, even if you have a 64bit machine. If during migration you get an ODBC error about “architecture mismatch between the Driver and Application”, you installed the wrong Workbench.
In the User DSN tab, click on Add… to create a DSN for for your database file. For the example, we created one for the northwind sample database.
Set up the parameters for your source Access database
Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use and the parameters for the connection.
If you open the Database System combo box you’ll find a list of the supported RDBMSes. Select Microsoft Access from the list. Just below it there’s another combo box named Stored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name of your preference.
The next combo box is for the selection of 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 DB 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 characterset used by your edition of Office, otherwise the data will be copied incorrectly.
Click on the Test Connection button to check whether an ODBC connection can be established. If you put the right parameters you should see a message reporting a successful connection attempt.
Set up the parameters to connect to your target MySQL database
Click on the Next button to move to the Target Selection page. Once there set the parameters to connect to your MySQL Server instance. When you are done click on the Test Connection button and verify that you can successfully connect to it.
Select the objects to migrate
Move to the next page using the Next button. You should see the reverse engineering of the selected database in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented a page showing the progress as shown in the image below.
Wait for it to finish and verify that everything went well. Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:
As you can see the Migration Wizard discovered table and view objects in our source database. Note that only the table objects are selected by default to be migrated. You can select the view objects too, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them yet) so let’s leave them off for now. The same applies for stored procedures, functions and triggers.
If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:
The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed as you can see in the image above). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the Objects to Migrate list and that the Migrate Table Objects checkbox is checked. Most of the time you’ll want to migrate all objects in the schema anyway, so you can just click Next.
Review the proposed migration
Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like this:
As you can see in the image above there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the Show Code and Messages button. If you click on it you can see (and edit!) the generated MySQL code that corresponds to the selected object. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the Northwind row and rename it.
An interesting option in the View combo box is the Column Mappings one. It will show you all of the table columns and will let you individually review and fix the mapping of column types, default values and other attributes.
Run the resulting MySQL code to create the database objects
Move to the Target Creation Options page. It will look like this:
As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server. You can view its progress in the Create Schemata page:
Once the creation of the schemata and their objects finishes you can move to the Create Target Results page. It will present you a list with the created objects and whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:
You can still edit the migration code using the code box to the right and save your changes by clicking on the Apply button. Keep in mind that you would still need to recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the Recreate Objects button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the Next button.
Transfer the data to the MySQL database
The next steps in the Migration Wizard are for the transference of data from the source Access database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.
There are two sets of options here. The first one allows you to perform a live transference and/or to dump the data into a batch file that you can run later. The other set of options gives you a way to tune up this process.
Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It will take a little while to copy the data. At this point the corresponding progress page will look familiar:
Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:
And that should be it. Click on the Finish button to close the Migration Wizard.
A little verification step
Now that the Northwind database was successfully migrated, let’s see the results. Open an SQL Editor page associated with your MySQL Server instance and query the Northwind database. You can try something like “SELECT * FROM Northwind.customers”. You should get something like this:
