How to convert the MDB (Access) file to MySQL (or a simple SQL file)?

Question

Is it possible to create a Dump SQL command command from a Microsoft Access database? I hope to convert this MDB file to a MySQL database for import, so I don't need to go through the CSV step.

I would expect that even the MSSQL dump file would still contain working SQL commands, but I don't know anything about MSSQL, please let me know.

Answer 1

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

Try software called Access to MySQL .

Access to MySQL is a small program that converts Microsoft Access databases to MySQL.

• Wizard interface.
• Transfer data directly from one server to another.
• Create a dump file.
• Select the tables to transfer.
• Select the fields to transfer.
• Transfer password protected databases.
• It supports both general security and user-level security.
• Additional transfer of indices.
• Additional transfer of records.
• Optional transfer of default values ​​in field definitions.
• Defines and transfers field types of automatic number.
• Command line interface
• Easy installation, removal and updating.

See the above link for a walkthrough with screenshots.

Answer 2

If you have access to the Linux module with mdbtools installed, you can use this shell Bash 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 call it, simply name it as follows:

 ./mdbconvert.sh accessfile.mdb mysqldatabasename 

It will import all tables and all data.

Answer 3

I changed the script to Nicolay77 in order to output the database to stdout (the usual way to unix scripts) so that I could output the data to a text file or transfer it to any program I want. The resulting script is a bit simpler and works well.

Some examples:

 ./to_mysql.sh database.mdb > data.sql ./to_mysql.sh database.mdb | mysql destination-db -u user -p 

Below is a modified script (save to to_mysql.sh)

 #!/bin/bash TABLES=$(mdb-tables -1 $1) for t in $TABLES do echo "DROP TABLE IF EXISTS $t;" done mdb-schema $1 mysql for t in $TABLES do mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t done 

Answer 4

Free database tools do not export the RELATIONSHIPS table , but you can use this : convert MS Access to MySQL with relationships

Works great and exports all relationships to MySQL.

Answer 5

Try the Data Wizard for MySQL . This is a tool for converting structure and data from any ADO-compatible source (for example, MS Access) to MySQL databases. See the Quick Start Guide for Connection Strings to create a connection string for the MS Access file.

Answer 6

OSX users can use Nicolay77 or mikkom, which use the mdbtools utility. You can install it through Homebrew . Just set your homegrown and then go

 $ homebrew install mdbtools 

Then create one of the scripts described by the guys and use it. I used mikkom alone, converted all my MDB files to SQL.

 $ ./to_mysql.sh myfile.mdb > myfile.sql 

(by the way, contains more than 1 table)

Answer 7

Try microsoft data conversion services

Answer 8

We have used ESF Database Convert many times for this purpose. DTS was usually too unreliable. And the recommendations on the MySQL page were extremely outdated.

Answer 9

I used SQLYog Ultimate to import data from the mdb file, it was a very simple process.

You may need to install this support tool.

MS Access Database Engine

and download SQLYog Ultimate below

Choose SQLYog, you can use the trial version for this

Answer 10

I use a Mac, I do this for conversion;

• Download ACCDB MDB Explorer http://accdb-mdb-explorer.en.softonic.com/mac
• Open the MDB file
• Export as SQL
• Import to MySQL using MySQL Workbench.

Answer 11

This tool for Mac MDB / ACCDB Viewer worked well for my needs. The free trial allowed me to prove that everything was done, and exported half of all the lines. The full version was required in order to export all exported db / tables.

Answer 12

If you are not too concerned about the privacy of the .mdb files you want to convert, please know that this site allowed me to recover two 15-year .mdb Access databases (remember the old days when ASP rules were web?) In just two minutes: http://www.mdbopener.com/

Databases were converted to Excel files with one sheet for each table. Just what I needed. Failed to recover my (very) old data faster ...

The identifiers stored in each table were as light as anything that could be converted to mysql again (after saving in CSV format) - again, in just a few minutes.

Answer 13

I tried https://www.rebasedata.com/convert-mdb-to-mysql-online and it works pretty well.

Online solution without the need for registration.