One approach to migrating data from Access to MySQL is to use the export feature provided by Access itself to write out the contents of each table as a text file. Each file then can be loaded into MySQL using a LOAD DATA statement or the mysqlimport command-line utility. Suppose you export a table mytable into a file mytable.txt using CSV (comma separated values) format, and you want to import it into a table named mytable in a MySQL database named mydb. You can invoke the mysql program, then issue a LOAD DATA statement to import the file like this:
C:\> mysql mydb
mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Alternatively, use mysqlimport from the command line (type the command all on one line):
C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
mydb mytable.txt
If you need to provide connection parameters such as the hostname, user name, or password, list them on the mysql or mysqlimport command line before the database name:
C:\> mysqlimport --local --fields-terminated-by=,
--fields-enclosed-by='"'
--lines-terminated-by='\r\n'
-h some_host -p -u some_user
mydb mytable.txt
The advantage of this approach is that it requires no special conversion tools. It can be used to produce data files even on machines that have no MySQL support. (If you don't have the MySQL client programs installed on your Access machine, create the data files, then copy them to another machine where the MySQL programs are installed and load the files into MySQL from there.) The disadvantage is that the MySQL tables must already exist before you can load data into them, so you must issue the appropriate CREATE TABLE statements yourself. For the example just shown, that means you must already have created the table mytable in the mydb database before using either LOAD DATA or mysqlimport.
Converters That Generate Intermediate Files
A second approach to data transfer is to use a converter that reads an Access table and produces from it one or more files containing SQL statements that create the table for you and load data into it. Then you execute the intermediate SQL file or files using the mysql program. Several free converters that work like this are available, each of which takes the form of an Access module:
exportsql.txt
Works with Access95, Access97, Access2000. Exports all tables in a database, producing one file containing DROP TABLE statements (in case you want to remove MySQL tables created during an earlier data transfer exercise) and another file containing CREATE TABLE and INSERT statements for creating and loading the tables. The files are written to the C:\TEMP directory.
access_to_mysql.txt
Exports all tables in a database into a file C:\TEMP\mysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any existing MySQL tables and recreate them. Less sophisticated than exportsql.txt in terms of type conversion and handling of special characters.
mdb2sql.bas
Access97 only. Exports selected tables to files in a directory of your choosing. Writes a data file for each selected table, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the data files into them.
Near the beginning of the source code for each of these converters, you'll find instructions that you should read, because the details of the process for generating the SQL and data files are converter-specific. Also, be sure to note any prerequisites that must be satisfied before using the converters. These include the following:
Both exportsql.txt and access_to_mysql.txt expect to write files to the C:\TEMP directory, so you must create that directory if it doesn't exist:
C:\> mkdir C:\TEMP
Alternatively, you can modify the module source so that it writes files to another existing directory.
If you want to use exportsql.txt to convert Access2000 tables, you need to enable support for the DAO (Data Access Objects) interface. From Access, go into the Visual Basic editor, select the Tools >> References menu option, then enable the "Microsoft DAO 3.6 Object Library" option in the window that comes up.
mdb2sql.bas requires that you have Advanced Wizards installed, because it uses the Documenter function included in that Wizard set.
After following the export procedure for a converter that generates intermediate SQL files from Access tables, you'll end up with one or more files that need to be executed with the mysql program, as follows. Assuming that you want to create tables in a database named mydb, you can execute a SQL file file.sql like this:
C:\> mysql mydb < file.sql
If you need to provide connection parameters, list them on the command line before the database name:
C:\> mysql -h some_host -p -u some_user mydb < file.sql
Converters That Perform Direct Data Transfer
Some conversion tools can transfer data directly from an Access database into MySQL. That is, they create the MySQL tables for you and load the information into them as well. This avoids the need for any intermediate files. On the other hand, such tools require that you be able to connect to the MySQL server from the machine on which your Access information is stored. (This requirement is easily satisfied if you install MySQL on your Access machine.)
Tools that can perform direct data transfer are:
MyAccess
$30 shareware. (Non-registered copies are fully functional, but an annoyance dialog that must be dismissed pops up every five minutes.) Works with Access97, Access2000. MyAccess is an Access add-in that allows direct transfer when you connect from Access to MySQL over an ODBC connection.
DBTools
Free. Works with Access97, Access2000. DBTools actually is intended primarily as an application for administering MySQL, but it includes data import capabilities that can be used to read Access databases for transfer to MySQL. (It can also read data from other sources such as Excel spreadsheets, making it particularly useful for transferring to MySQL information that is stored in a variety of formats.) Because DBTools reads Access databases directly, you can use it to migrate Access tables even if you don't have Access installed locally, as long as you have the database files containing the tables to be transferred. DBTools does not require ODBC.
MySQLFront
Free. MySQLFront is similar in many ways to DBTools. It can read Access97 and Access2000 files directly. If ODBC is installed, MySQLFront can import information into MySQL from ODBC data sources over the network. (Unfortunately, MySQLFront development has ceased and it is no longer distributed by its author. However, you may be able to find it on alternate download sites by using a search engine.)
As an example how one of these tools works, here's how you'd use DBTools to perform data transfer from Access to MySQL. Begin by visiting the DBTools download page at
http://www.dbtools.com.br/, transferring the installer (a program named setup.exe), and running it. This will install DBTools on your machine.
If you want to transfer Access2000 databases, you need to enable DAO. (If you don't, DBTools will crash whenever you try to open an Access2000 database.) To turn on DAO, launch DBTools (it will tell you there is no server profile; that's normal), select the Options >> Preferences menu item, and select the DAO 3.6 option. Then quit and relaunch DBTools, because DAO isn't actually activated until the next launch after you enable it.
With DBTools running, establish a connection to your MySQL server. (Click the Server icon in the toolbar or use the Server >> Add Server menu item to define a profile for the MySQL server you want to connect to.) You must be connected to the server before you can transfer information; many of the menu items and icons in the tool bar are disabled until you establish a connection, including those related to importing data.
After connecting to MySQL, use the Import Data Wizard to select the Access database file containing the tables you want to transfer. One of the dialogs presented during this process asks you to select the file type for the kind of database you want to use. Select the Access97 type for either Access97 or Access2000 databases.
If you intend to continue using Access after transferring the tables, open the database from Access, delete the tables that you just transferred to MySQL, connect to the MySQL server, and set up links to the tables.