Exporting data from MS Access 2003 to MySQL

Exclusive offer: get 50% off this eBook here
Choosing an Open Source CMS: Beginner's Guide

Choosing an Open Source CMS: Beginner's Guide — Save 50%

Find the best CMS and start working with it to create web sites, blogs, communities, e-commerce sites, and intranets

$23.99    $12.00
by Dr. Jay Krishnaswamy | July 2009 | PHP

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.

Exporting data from MS Access 2003 to MySQL

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.

Exporting data from MS Access 2003 to MySQL

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

Exporting data from MS Access 2003 to MySQL

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.

Exporting data from MS Access 2003 to MySQL

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.

Exporting data from MS Access 2003 to MySQL

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

Exporting data from MS Access 2003 to MySQL

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.

Exporting data from MS Access 2003 to MySQL

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>
Choosing an Open Source CMS: Beginner's Guide Find the best CMS and start working with it to create web sites, blogs, communities, e-commerce sites, and intranets
Published: April 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Export the Employees table from MS Access to TestMove

Open the Northwind.mdb (or any other mdb file) file as shown in the next figure.

Exporting data from MS Access 2003 to MySQL

Highlight the Employees table and make a right click to bring up a drop-down menu as shown.

Exporting data from MS Access 2003 to MySQL

Click on Export to open the Export Table 'Employees' To... window. Click on the Save as type drop-down to reveal the various file types that you can export to as shown.

Exporting data from MS Access 2003 to MySQL

Click on ODBC Sources the Export window pops-up as shown.

Exporting data from MS Access 2003 to MySQL

Click OK. This opens the Select Data Source window. Click on the Machine Data Source tab to show the various available DSN's. You will notice the AccMySQL created earlier.

Exporting data from MS Access 2003 to MySQL

Choose AccMySql and click OK. The program returns you to MS Access's Northwind database without giving you an indication about the success/failure of the export. If there is an error it however gives a error message.

Verifying and accessing exported table

Now go back to the DOS screen. I assume you have not closed down the mysql > prompt. Enter commands as shown in the listing.

mysql> use testmove
Database changed
mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees |
+--------------------+
1 row in set (0.00 sec)

mysql>

Now you can see that the Employees table has entered the TestMove database in MySql. Probe a little further by entering a select statement to see how much of the data has come in.

mysql> use testmove
Database changed
mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees |
+--------------------+
1 row in set (0.00 sec)

mysql> Select * from Employees;
+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
| EmployeeID | LastName | FirstName | Title
| TitleOfCourtesy | BirthD
ate | HireDate | Address
| City | Region |
PostalCode | Country | HomePhone | Extension | Photo
| Notes | ReportsTo |
+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
| 1 | Davolio | Nancy | Sales Representative
| Ms. | 1968-1
2-08 00:00:00 | 1992-05-01 00:00:00 | 507 - 20th Ave. E.
Apt. 2A | Seattle | WA | 98122 | USA | (206)
555-9857 | 5467 | EmpID
1.bmp | Education includes a BA in psychology from Colorado
State University. She also co
mpleted "The Art of the Cold Call." Nancy is a member of
Toastmasters International.| 2 |

| 2 | Fuller | Andrew | Vice President, Sales
| Dr. | 1952-0
2-19 00:00:00 | 1992-08-14 00:00:00 | 908 W. Capital Way
| Tacoma | WA |
98401 | USA | (206) 555-9482 | 3457 |
EmpID2.bmp | Andrew received his BTS
commercial and a Ph.D. in international marketing from the University
of Dallas. He is fluent in French and Italian and reads German.
He joined the company as a sales representative, was promoted to
sales manager and was then named vice president of sales.
Andrew is a member of the Sales Management Roundtable,
the Seattle Chamber of Commerce, and the Pacific Rim Importers
Association. | NULL |

| 3 | Buchanan | Steven | Sales Manager
| Mr. | 1955-0
3-04 00:00:00 | 1993-10-17 00:00:00 | 14 Garrett Hill
| London | NULL |
SW1 8JR | UK | (71) 555-4848 | 3453 |
EmpID5.bmp | Steven Buchanan graduate
d from St. Andrews University, Scotland, with a BSC degree.
Upon joining the company as a sales representative, he spent
6 months in an orientation program at the Seattle office a
nd then returned to his permanent post in London, where he was
promoted to sales manager. Mr. Buchanan has completed the
courses "Successful Telemarketing" and "International Sale
s Management." He is fluent in French. | 2 |

| 4 | Suyama | Michael | Sales Representative
| Mr. | 1963-0
7-02 00:00:00 | 1993-10-17 00:00:00 | Coventry House
Miner Rd. | London | NULL | EC2 7JR | UK |
(71) 555-7773 | 428 | E
mpID6.bmp | Michael is a graduate of Sussex University (MA,
economics) and the University of California at Los Angeles
(MBA, marketing). He has also taken the courses "Multi-Cultural
Selling" and "Time Management for the Sales Professional."
He is fluent in Japanese and can read and write French,
Portuguese, and Spanish.| 3 |

+------------+-----------+-----------+------------------------
--------------+---------------------+-------------------------
--------------------------------------------------------------
----------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql>

The output is not particularly pretty but you can see that the data has come in. The next figure shows the same query run in MySQL Workbench a graphic tool used in querying and modeling MySql databases.

Exporting data from MS Access 2003 to MySQL

Summary

The article described the procedure to create an ODBC DSN for connecting to a MySQL Database. The article also described the process of copying data in an MS Access database to MySQL.

If you have read this article you may be interested to view :

Choosing an Open Source CMS: Beginner's Guide Find the best CMS and start working with it to create web sites, blogs, communities, e-commerce sites, and intranets
Published: April 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

 

Books From Packt

Flex 3 with Java
Flex 3 with Java

WordPress 2.7 Complete
WordPress 2.7 Complete

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

Joomla! E-Commerce with VirtueMart
Joomla! E-Commerce with VirtueMart

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

jQuery UI 1.6: The User Interface Library for jQuery
jQuery UI 1.6: The User Interface Library for jQuery

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 Installation, Migration, and Configuration

Your rating: None Average: 4 (2 votes)
Excellento by
Superb. Exactly what I wanted.
mysql driver by
I love the way you don't link to the MySQL ODBC driver. For all the non-WAMP users: http://dev.mysql.com/downloads/connector/odbc/ WAMP? WTFIT? FUBAR!
exporting protein sequences from access spreasheet to MySQL db. by
I have been trying to export these sequences from MS Access to MySQL db for so long now so that I can run them in BLAST database. Thanks Chary for ur help!!
opening MS ACCESS Db file through MYSQL. by
I am new to MYSQL.Recently installed it on windows xp.I was trying to open MSAceess Db file through Mysql but cd not. I had gone through many MYSQL tutorials on the net but did not get a clear idea about the method.I felt very happy and relaxed going through the method you explained how to access a database file in MS Access. I am sure I can manipulate the database through MY SQL. Many Thanks, Chary M V N

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
N
C
r
i
9
Z
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software