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 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.

Advertisements

One response to “Export MS Access Database To MySQL Database

  1. how to call enum value to a java combo box?
    or is there a way to call a multivalued attributed from mysql to a combo box? please help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s