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

sql

data-type-mismatches

conversion-tools

database-migration

byNikita Barsukov·Oct 7, 2024

For MDB to MySQL conversion, first export tables from Access to a compatible format such as CSV or SQL. In Access, go to External Data > Export > ODBC Database for MySQL-compatible SQL files, or to directly export to CSV.

Example Access Export:

CREATE TABLE `Customers` (
  `CustomerID` INT,  // Who said you are just a number? This, unfortunately
  `CompanyName` VARCHAR(255), // No verbose company names, please
  ...
);

Then, import to MySQL:

mysql -u myuser -p mydb < my_sql_file.sql

Remember to tailor myuser, mydb, and my_sql_file.sql to suit your case. Syntax compatibility checks are recommended when using SQL files.

Conversion using software

The Access to MySQL software offers a frictionless conversion with an intuitive wizard interface. It allows direct data transfer without the need for intermediary formats, or the creation of dump files.

Key capabilities encompass:

Transfer of custom-selected tables and fields
Conversion of password-database
Retention of indexes, records, and default values
Handling of auto-number field types

For Linux devotees, mdbtools can circumvent the CSV stage, directly converting MDB to MySQL script.

Automation with scripts

A bash script, such as to_mysql.sh, can automate your conversion from MDB to MySQL, saving precious time. Scripts make use of mdb-schema and mdb-export to translate the database's structure and data to that of MySQL.

Pre-conversion pointers

Before diving into the conversion process, consider these steps:

Dropping existing MySQL tables to avoid potential naming conflicts.
Using mdb-tables to decide which tables you want to convert.
Preserving date accuracy by specifying the date formats in the mdb-export command.

Deep dive into tools

Mdbtools, readily installable on OSX with Homebrew, is a toolkit for direct conversion. It includes mdb-schema and mdb-export for bypassing intermediary formats.

Mdbtools features:

Direct SQL commands output from Access database with mdb-to-mysql
Streamlines the process, skipping the CSV step
Convert and pipe data flexibly to a file or another program

Caveats during conversion

During conversion, be mindful of a few tricky points:

Data type mismatches: Ensure that the data types in Access correspond cleanly to MySQL types.
Character encoding: Mind the text encoding to prevent character mismatches.
Macro and VBA conversions: These won't translate directly to MySQL. Consider reworking the logic in MySQL.