Migrating Access Databases to MySQL
The easiest way to migrate your Access database to MySQL is to export a MySQL-compatible SQL file using MDB Viewer and then upload this file using the wonderful Open Source app Sequel Pro.
For a general introduction to SQL files, see Exporting as SQL.
Step by Step
In MDB Viewer:
- Click the "Export SQL" button if you want to export all tables or select some tables in the sidebar and choose "Export selected tables to SQL…" from the File menu
- Select the "MySQL" format, and choose whether to export data, structure, or both
In Sequel Pro:
- Connect to the MySQL server
- Select the target database from the database popup
- Select "Import…" in the File menu
- Open the SQL file created with MDB Viewer (using UTF-8 encoding)
Access Types vs. MySQL Types
| Access Type | MySQL Type |
|---|---|
| Boolean | BOOLEAN |
| Byte | INTEGER |
| Integer | INTEGER |
| Long Integer | INTEGER |
| GUID | TEXT |
| Decimal | DOUBLE |
| Money | DOUBLE |
| Float | DOUBLE |
| Double | DOUBLE |
| Date/Time | DATETIME |
| Text | VARCHAR |
| Memo | LONGTEXT |
| Ole | LONGBLOB |
When exporting for MySQL, backticks are used to quote table and column names. Therefore table names containing spaces etc. work just fine.