Exporting data from MS Access 2003 to MySQL

Most businesses use a software mix in their IT arsenal that makes business sense to them. Because of this, often they have to migrate a part, or whole of their data from one software program to another. In this article by Dr. Jay Krishnaswamy, the built-in method of exporting tables in Microsoft Access is explored to take a table in Microsoft over to MySQL, the open source database product that changed hands recently. This article steps you through the process with a number of screen shots to guide you along the way.

Introduction

It is assumed that you have a working copy of MySQL which you can use to work with this article. The MySQL version used in this article came with the XAMPP download. XAMPP is an easy to install (and use) Apache distribution containing MySQL, PHP, and Perl. The distribution used in this article is XAMPP for Windows. You can find documentation here. Here is a screen shot of the XAMPP control panel where you can turn the services on and off and carry out other administrative tasks.

You need to follow the steps indicated here:

1. Create a database in MySQL to which you will export a table from Microsoft Access 2003
2. Create a ODBC DSN that helps you connecting Microsoft Access to MySQL
3. Export the table or tables
4. Verify the exported items

Creating a database in MySQL

You can create a database in MySQL by using the command 'Create Database' in MySQL or using a suitable graphic user interface such as MySQL workbench. You will have to refer to documentation that works with your version of MySQL. Herein the following version was used. The next listing shows how a database named TestMove was created in MySQL starting from the bin folder of the MySQL program folder. Follow the commands and the response from the computer. The Listing 1 and the folders are appropriate for my computer and you may find it in your installation directory. The databases you will be seeing will be different from what you see here except for those created by the installation.

Listing 1: Login and create a database

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:Documents and SettingsJayaram Krishnaswamy>cd

C:>cd xamppmysqlbin

C:xamppmysqlbin>mysql -h localhost -u root -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| expacc |
| mengerie |
| mydb |
| mysql |
| phpmyadmin |
| test |
| testdemo |
| webauth |
+--------------------+
10 rows in set (0.23 sec)

mysql> create database TestMove;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| expacc |
| mengerie |
| mydb |
| mysql |
| phpmyadmin |
| test |
| testdemo |
| testmove |
| webauth |
+--------------------+
11 rows in set (0.00 sec)

mysql>

The login detail that works error free is shown. The preference for host name is localhost v/s either the Machine Name (in this case Hodentek2) or the IP address. The first 'Show Databases' command does not display the TestMove we created which you can see in response to the 2nd Show Databases command. In windows the commands are not case sensitive.

Creating an ODBC DSN to connect to MySQL

When you install from XAMPP you will also be installing an ODBC driver for MySQL for the version of MySQL included in the bundle. In the MySQL version used for this article the version is MySQL ODBC5.1 and the file name is MyODBC5.dll.

Click Start | Control Panel | Administrative Tools | Data Sources (ODBC) and open the ODBC Data Source Administrator window as shown. The default tab is User DSN. Change to System DSN as shown here.

Click the Add... button to open the Create New Data Source window as shown.

Scroll down and choose MySQL ODBC 5.1 Driver as the driver and click Finish. The MySQL Connector/ODBC Data Source Configuration window shows up.

You will have to provide a Data Source Name (DSN) and a description. The server is the localhost. You must have your User Name/Password information to proceed further. The database is the name of the database you created earlier (TestMove) and this should show up in the drop-down list if the rest of the information is correct. Accept the default port. If all information is correct the Test button gets enabled.

Click and test the connection using the Test button. You should get a response as shown.

Click the OK button on the Test Result window. Click OK on the MySQL Connector/ODBC Data Source Configuration window. There are a number of other flags that you can set up using the 'Details' button. The defaults are acceptable for this article.

You have successfully created a System DSN 'AccMySQL' as shown in the next window. Click OK.

Verify the contents of TestMove

The TestMove is a new database created in MySQL and as such it is empty as you verify in the following listing.

Listing 2: Database TestMove is empty

mysql> use testmove;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>