How To Export MS Access Database To MySQL Database

Here are few tips (with undocumented features) to help you export huge MS Access database (production database with real data) to MySQL.

Use mdbtools utilities as described below:

mdb-tables displays the list of tables in MS Access

mdb-scheme export the MS Access schema to MySQL database. The documentation doesn’t mention that MySQL is supported. You should use the following command:

mdb-schema [-S] database mysql

You may need to replace column names if they collide with MySQL reserved keywords. few column names I needed to change were: GROUP, PRIMARY & CROSS. I changed the column names to GROUP_, PRIMARY_ & CROSS_ respectively. MySQL error messages are very cryptic (unhelpful).

The schema created contains drop tables. You can use grep -v to exclude them. Additionally you should add drop database followed by create database commands at the very top of the file when you are importing to a new database. This is essential because you may have to go through multiple passes. Having drop table when a table is not there creates problem as also not having them creates problem when the table has already been loaded. So I remove the drop tables and instead drop and re-create the database for each run by the process described above.

mdb-export exports MS Access tables to CSV format. What is not emphasized is that you can directly create INSERT statements instead of a CSV file. Converting it directly to SQL INSERT statements is very helpful, especially for converting data types. Use it like shown in the sample:

mdb-export -I -R’;\n’ MSAccessDatabase.mdb Table > Table.sql
Note: Replace MSAccessDatabase.mdb with the full path of your MS Access Database, Table is Table name. You can save the output in any file, I just chose Tablename.sql for convenience.

The -R’;\n’ ensures that each SQL INSERT is followed by a semicolon and then a new line.

You may need to modify this to substitute column names, if you have previously changed them while importing the schema to prevent conflict with reserved MYSQL keywords.