Migrate MS Access database (.mdb) to MySQL (.sql) database using MySQL ODBC Connector

Hello coders, this blog post provides information about conversion of MS Access Database to MySQL database. First of all, here is a brief overview of the need to migrate .mdb file to .sql file.

- Microsoft Access is used as Database Management System (DBMS) for Windows Desktop Applications. Now, suppose that you have created a similar application on Web and you want to use same database as used in Windows Application.
- You can not use the database created in Microsoft Access on Web Server as it is. You have the following options for using the same database for your Web Application.

• Using Microsoft SharePoint – MS Access can be used on SharePoint to build Web Database Applications.
• Migrating MS Access database to Web based database, i.e, MySQL, MS SQL Server or others. This post discusses about this method.

If the conversion of .mdb file can be done to .sql file, then the .sql can be imported to any Web based database. There are online tools available for this. But this blog post discusses about migration without using online available tools.
Now, we will explain how to migrate .mdb file (MS Access database) to .sql file.

For this migration, we will export tables from MS Access to MySQL database using MySQL ODBC connector. There is a full documentation on MySQL ODBC connector on MySQL developer website at the following URL:
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-introduction.html.

1. MySQL ODBC connector can be downloaded from the following URL:
   https://dev.mysql.com/downloads/connector/odbc/5.3.html. Here, you can download it according to your operating system. 
2. After installing MySQL ODBC connector, it needs to be configured to create a Data Source Name (DSN). The documentation to create DSN using ODBC Data Source Administrator on Windows can be found at https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html. 
3. This Data Source Name (DSN) will be used during migration of MS Access tables to MySQL database. During creation of DSN, there is a Connection Parameters Dialog box(as shown below) where Database connection parameters like Database Host, Database User, Database Password and Database Name need to be filled. During migration, this database connection will be used.
   
4. The documentation for exporting MS Access database to MySQL database can be found at https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-export.html. 

Brief Overview of Export Process:

• Create a new database in MySQL. Its connection details are filled in Connection Parameters Dialog box during creation of DSN (as described above) as tables in MS Access will be exported in this database.
• Open MS Access .mdb file to be exported. In the left panel, there are table names and when a table name is clicked, the table data is shown in the right panel. Right click on the table you want to export, and in the menu (as shown below) that appears, choose Export ->ODBC Database.
  
• The Export dialog box appears (as shown below). Enter the desired name for the table after its import into the MySQL server, and click OK.
  
• The Select Data Source dialog box (as shown below) appears. It lists the defined data sources for any ODBC drivers installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the Connector/ODBC DSN to which you want to export your table (DSN created above).
  
• A dialog box appears with a success message if the export is successful. In the dialog box, you can choose to save the export steps for easy repetitions in the future.
  
• If you see the error message (as shown below) instead when you try to export to the Connector/ODBC DSN, it means you did not choose the Database to connect to when you defined in to the DSN. Reconfigure the DSN and specify the Database.
  

After the tables from MS Access are exported to MySQL database, you can export the MySQL database dump as .sql file which can be imported to other Web based DBMS which you are using.

References:

1. https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-introduction.html.
2. https://dev.mysql.com/downloads/connector/odbc/5.3.html.
3. https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html.
4. https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-export.html.