Even though MS Access is quite a decent software in the market, it has its limitations. Now people have opted for MySQL—a software with better performance and reliability. It is more flexible on the way information provided to it can be used. Recently, many software companies are gradually transferring their data depositories from MS Access to MySQL.
In this article, for anyone who’s ready to make the switch, we have laid down the instructions on how to transfer information from MS Access to MySQL.
Various Methods by Which You Can Transfer Databases from Microsoft Access to MySQL
The first step in migrating your information from MS Access to MySQL is that you copy all of your content in your tables from an MS Access database to the MySQL server. For transferring tables, you can choose one from a number of methods possible. For anyone who wants to continue using the MS Access interface for their data, for them the next step is to replace the tables with links: To do this delete the tables in your Access database. Then, establish a connection via Open Database Connectivity (ODBC) from the MS Access to MySQL server. Lastly create the tables again as links to MySQL tables. This is only for those who wish to continue using the MS Access interface, otherwise this is not necessary.
For some transfer methods you need to make an open database connection to the MySQL server. To do this you can either use the MySQL Connector/ODBC or the MySQL-specific ODBC driver.
How to Tell Microsoft Access to Export Its Own Tables
The first method you can use to migrate data from MS Access to MySQL is using the export tool by MS Access to convert the contents of each table as a text file. Then load each file into MySQL using the “mysqlimport” command-line utility or the “Load Data” statement. For example, to import a file you made called “a_table.txt” into your MySQL database, you the mysql program and just issue a “Load Data” statement to import this file as explained below:
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’;
Otherwise, you can use the mysqlimport from the command line:
C:\> mysqlimport -h <host> -u <user> -p <db name>
–local –fields-terminated-by=, –fields-enclosed-by='”‘
–lines-terminated-by=’\r\n’ a_table.txt
To provide connection parameters like hostname, username, or password, write them on to the mysql or mysqlimport command line before writing the database name.
The best thing about this method is that by using this you need no special extra conversion tools. This means that you can create data files without needing your device to support MySQL. For example you just basically need to create data files in your Access machine and copy them onto any device that supports the MySQL programs. Then you can use those files there. However, for this MySQL tables should exist before you can input data on to them, hence, you must use appropriate commands yourself to do so beforehand. As explained, it means that first you have to create a table in your database before you can load data using either of the two functions available: “Load Data” or “mysqlimport”
How To Generate Intermediate Files
The second method you can use to transfer data is to use a converter that can read a MS Access table and produce more than one file from it which contains the SQL statement used to create the table and fill it out. Following this you can start the intermediate SQL file or files using the “mysql” program. Below are listed some of such converters which are available for free:
- ● mdb2sql.bas
This program will let you export tables to files in a location you choose. It will write date file for every table you select and also create a file containing the SQL script with “Create Table” statements which will obviously be used to create tables. The “Load Data” statements are used to import data files onto them. However, the problem with this program is it only works with Access97.
● exportsql.txt
This converter will export the tables into a database. It will produce a single file which will contain “Drop Table” statements. The other file created will contain “Create Table” and “Insert” statements which will be used to load or create tables. This program works with MS Access96, MS Access97 and MS Access2000.
● access_to_mysql.txt
This program will export all of the tables into a single file containing all of the statements to recreate any already existing MySQL table. This converter is more simple to use than exportsql.txt in terms of handling or converting data.
Using these converters it is highly likely you will have a lot of intermediate SQL files generated from the tables in MS Access. To transport all of the data into MySQL, you will need to run the mysql program as instructed. For example, if you want to create tables in a data based, let’s supposed it is named “dbx” you can use the following code to execute a SQL files (e.g. named tex.sql)
C:\> mysql dbx < tex.sql
Connection parameters need to be listed on the command line before the database name, explained below:
C:\> mysql -h some_host -p -u some_user dbx < tex.sql
Converters to Transfer Your Data Directly
If the process of creating intermediate files seems too much of a headache for you, there are some converters which can directly transfer you MS Access database into MySQL server. What they do is that they automatically create tables in MySQL for you and fill them with information as well. However, for these converters to work, you must be connected to the MySQL server on the machine on which all of your MS Access data is present. To do this, you just need to install MySQL onto your device with MS Access.
One of these converters is the Access to MySQL database migration utility. It is made by Intelligent Converters. The best thing about it is that it is compatible with any MS Access or MySQL version that is running on your computer. Using the Select-queries option you can filter your data for conversion in order to decide how you want the tables on MySQL to be displayed. This also allows you to match the MySQL database exactly with MS Access date meaning that data transfer is extremely accurate and convenient.
