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 like GROUP, PRIMARY & CROSS.
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.