12 Apr 2006

Converting Access Database to MySQL

This was something that I was struggling to find decent info on but eventually managed to get it working. So, here’s how to do it:

1. Create your database in MySQL

Open up MySQL Front or whatever MySQL administration software you use.
Add a new database (in MySQL Front, right click on the data list on the left hand side and select “Create Database”
Give your database a relevant name. There’s no need to add any tables to the database.

Go to Start > Programs > Administrative Tools > Data Sources (ODBC)
Make sure you’re under the User DSN tab and click on the button “Add”
You’ll now get a list of database drivers. Scroll down to the bottom and look for your MySQL driver (it’ll be the one with the words ‘MySQL’ in the title. Click on this to highlight it then click the ‘Finish’ button.
You’ll now get the MySQL DNS configuration form.
Under “Data Source Name”, give your datasource a name relevant to your project.
Under “Database Name”, enter the name of the database that you created in stage 1.
Fill in the rest of the boxes with your MySQL setup info (if you don’t know any of this, then you need to go and find a tutorial on installing MySQL).
Click on “Test Data Source” and, if all’s well, you’ll get a message congratulating you on doing everything right.
Click “Okay” and the window should close and your new datasource should be visible in the list.

Open up your Access database that you want to convert.
Select the first table in your list and then go to File > Export.
In the window that pops up, change the “Save As Type” dropdown to “ODBC Databases”.
That window will close and another will open asking you to enter a name for the table you’re exporting. If you want to create an exact copy of your Access database, just leave this as is. The name you put here will be the name of the current table in your MySQL database. Click “Okay”.
Another window will open up to select your datasource. Click on the “Machine Data Source” tab and a list of sources will come up. If all has gone to plan, the source you created in Step 2 will be listed.
Click on the datasource name that you created and then click “Okay”. If you don’t get an error message, everything worked.
Repeat this for the rest of the tables in your database.

If you go back into MySQL Front (or your own software) and refresh the view, you should see all your imported tables in the database you created to start with. You’ll need to go through and update indexes and perhaps change some fields types but your data and structure should match your Access database.