Migrate Microsoft Access to MySQL

Do I Need to Migrate to MySQL?

Using MySQL server as a data storage instead of Microsoft Access offers multiple benefits. So, if you are constructing a complex multi-user system, you defenitiely need to migrate data from MS Access to MySQL. If MS Access database is used by a single user for personal purposes there is not much benefit to using MySQL in such a situation.

Migration Strategies

[1] There is the standard way to migrate Microsoft Access data to MySQL server. MS Access can export its tables to a variety of formats, including ODBC. Using this feature you will be able to export an Access table to MySQL via Connector/ODBC driver. To migrate MS Access table to MySQL server right-click on the table and select "Export" option. After several steps the data will be tranferred to MySQL. You can find more details about this approach here:

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-export.html

[2] Another approach to migrating data from MS Access to MySQL is handled by Microsoft Access export feature. MS Access itself can export the data of each table into a text file. Each of these text files can be imported into MySQL database using LOAD DATA statement or the mysqlimport command-line utility.

Let's assume that table "a_table" has been exported into comma separated values file "mytable.txt". Now it is a time to import it into MySQL database. For this purpose launch mysql console client and compose LOAD DATA statement as follows:

   C:\> mysql -h <host> -u <user> -p <db name>
   mysql> LOAD DATA LOCAL INFILE 'a_table.txt'
       -> INTO TABLE a_table
       -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
       -> LINES TERMINATED BY '\r\n';

Of course, it is necessary to replace <host>, <user> and <db name> with actual values. And this is how it can be done using mysqlimport tool:

   C:\> mysqlimport -h <host> -u <user> -p <db name> 
           --local --fields-terminated-by=, --fields-enclosed-by='"' 
           --lines-terminated-by='\r\n' a_table.txt

All the command must be typed in one line. As before, it is necessary to replace <host>, <user> and <db name> with actual values. The main benefit of this approach is that no special conversion tools are required. It works even on a machine having no connection to MySQL server. In such case just export MS Access data into text files, copy them to machine that can conect to MySQL server and import contents of the files into MySQL database from there.

The disadvantage is that the MySQL tables must be created before you can load data. This can be done manually from mysql console client using CREATE TABLE statements as follows:

   CREATE TABLE a_table (
       -> <1st column name> <type>, 
       -> <2nd column name> <type>, 
       -> ... 
       -> );

Column names should be enherited from MS Access tables to preserve database structure and to make it easy to create indexes and constraints (that has to be done manually in this approach). Appropriate mapping for Microsoft Access types into MySQL can be found using this table.

[3] ExportSQL by Cynergi automates creation of tables and indexes, export of Microsoft Access data into MySQL format. It is a Microsoft Access VBA script that converts Access database into file with SQL statements to create tables and fill them with the data. To use this solution you should download the script from the URL above and follow these steps:

• Open the Access .mdb/.accdb file of the database you wish to migrate
• In the default database objects window, click on "Modules", and then select "New"
• The code window with some pre-written text (code) appear. You should delete this text.
• Copy-and-paste the entire text of ExportSQL script to the code module window
• Push the compile button (it looks like 3 sheets of paper with an arrow pressing down on top of them)
• Close the code module window. You will be prompted to save the code, answer "Yes" and specify name for the module. Now the module is included in MS Access database.
• Re-open the code module by double-clicking on it, or push "Design" highlighting the module name.
• Move the cursor to where the first "Function" keyword appears. Press F5 or select "Run", "Go/Continue" from the top menu.

Choosing the Migration Tool

Are these migration strategies suitable for you? If the Microsoft Access database is small and does not have complex structure, the answer is "yes". Otherwise, you should know that the following manual steps are required to complete MS Access to MySQL migration:

• convert character sets (if necessary)
• create relationships between tables
• convert MS Access queries into MySQL views

To have all these tasks automated you can try some of commercial tools that can migrate data from MS Access to MySQL directly, without any intermediate steps.

MS Access to MySQL Converter

Although it is very easy to use, MS Access to MySQL converter by Intelligent Converters provides enough capabilities to migrate very large and complex MS Access databases. At the same time the migrated data is accurate because the program does all necessary data transformation, supports all Microsoft Access types and attributes, converts all indexes and relationships between tables, supports Unicode and much more.

If you need more control over the migration or just don't have direct access to the destination MySQL server, Access-to-MySQL can export data into MySQL script file containing SQL statements to create tables, indexes and constraints, to insert the data and create all views. This script can be manually imported to MySQL server using the standard tools like mysql console client or phpMyAdmin.

Click here to learn more about MS Access to MySQL converter.