How can I convert an MDB (Access) file to MySQL (or plain SQL file)?

Question

Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don't have to go through the CSV step.

I would expect even an MSSQL dump file to still contain workable SQL commands, but I know nothing of MSSQL, please let me know.

Answer 1

You want to convert mdb to mysql (direct transfer to mysql or mysql dump)?

Try a software called Access to MySQL.

Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.

• Wizard interface.
• Transfer data directly from one server to another.
• Create a dump file.
• Select tables to transfer.
• Select fields to transfer.
• Transfer password protected databases.
• Supports both shared security and user-level security.
• Optional transfer of indexes.
• Optional transfer of records.
• Optional transfer of default values in field definitions.
• Identifies and transfers auto number field types.
• Command line interface.
• Easy install, uninstall and upgrade.

See the aforementioned link for a step-by-step tutorial with screenshots.

Answer 2

If you have access to a linux box with mdbtools installed, you can use this Bash shell script (save as mdbconvert.sh):

#!/bin/bash  TABLES=$(mdb-tables -1 $1)  MUSER="root" MPASS="yourpassword" MDB="$2"  MYSQL=$(which mysql)  for t in $TABLES do     $MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS $t" done  mdb-schema $1 mysql | $MYSQL -u $MUSER -p$MPASS $MDB  for t in $TABLES do     mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t | $MYSQL -u $MUSER -p$MPASS $MDB done 

To invoke it simply call it like this:

./mdbconvert.sh accessfile.mdb mysqldatabasename 

It will import all tables and all data.