Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Import MS Access Data to MySQL 5
Ken Lin
Introduction
In an era where data becomes volumetric in every business, much attention has been given to proper database management and the ease of data access. Businesses that use MS Access begin to look for an alternative which allows better database performance, higher reliability, higher flexibility, yet inexpensive.
With the numerous choices available in the market, considering you don't want to put too much investment on it, only one, MySQL, stands out from the crowd. MySQL's flexibility allows you to deploy it cross various platforms, it also allows multiple user access concurrently. If you wish, you can still continue to do your data administration through Access as a front end. However, despite the various MySQL's features overtaking Access, whether or not to migrate your data from Access to MySQL still need an in-depth consideration.
In this article, I will discuss whether or not to migrate your MS Access data to MySQL. Then, I will cover about the considerations, planning, and preparations which should made before migrating your data. After all the planning and preparation stages has been finished, I will illustrate to you how the migration can be done with the help of Navicat, a MySQL database administration GUI.
MySQL versus Access
When you start to think of whether to use Access or MySQL, a very first point to think about is that what you need. The features of MS Access and MySQL are almost in two different directions: MS Access can only be deployed in Microsoft Windows while MySQL is cross platform; MS Access is a single-user application while MySQL is a multi-user application. To help you to decide whether or not to keep using MS Access or migrate your data from MS Access to MySQL, we will illustrate some scenarios that you should migrate or should not.
Migrate your MS Access data to MySQL when:
  1. You want your data to be deployed with more flexibility. Data in MySQL can be accessible to more users through the web. With MySQL, people can use client programs or other administrative tools to get access to your database by authentication. MySQL can also be integrated with Web Server by web programming languages. This provides a more flexible choice other than MS Access alone. You can get your data from MySQL remotely from anywhere you want disregarding the platform, provided that the database is connected to the Internet and you have to login names for it.
  2. You are not the only person who controls the data. MS Access is a single user program for local use, although it has some sharing features, your data cannot be accessed concurrently in MS Access. MySQL is a multiple-user program, it's suited to the situation where you are not the only person who is controlling the data. MySQL is designed to work well in a networked environment and is capable to serve a number of clients.
  3. You want your data secured and only accessed by authorized people. MS Access data is stored in a local machine, whenever the machine is left unattended, any foreigner may steal your data by copying it to a disk. Although MS Access do allow users to set passwords to a database, it is not a necessity and many people neglect the process. MySQL requires authentication before opening connection to a database, this enhances the security issue. It also allows user privilege settings, this can help database administrator easily manage the actions for each particular user can perform.
  4. Your database is large.
  5. You are not going to use Microsoft Windows anymore. Since MySQL is cross-platform, you can install your MySQL on more than 20 platforms including Linux distributions, Mac OS X, UNIX, and Microsoft Windows.
  6. You want an open source database. MySQL can be obtained for free while MS Access cannot. MySQL is now under the GPL license. You can obtain a free copy of MySQL if you are not using it for commercial purpose. For commercial organization, the license is inexpensive compared to other databases in the market.
Do not migrate your MS Access data to MySQL when:
  1. you want your data be easily portable. Since MS Access is a local based program, you can take your data with your wherever you like by just copying the file onto a disk. The file can be opened with an MS Access program installed in another computer;
  2. you only need single user access to database;
  3. you prioritize the use of MS Access reporting feature more than the database features of MySQL;
  4. you feel very comfortable with the performance of MS Access.
After thorough understanding of the pros and cons of MS Access and MySQL, you should now able to decide whether or not to move your data to MySQL. If you have now confirmed your interest to migrate your data, the following sections will teach you how to do so.
Before Migration
To start using MySQL, a good planning is important as there is some incompatibility between MS Access and MySQL. To transfer the data successfully, there a few points we have consider. One of the important data incompatibilities we must consider is the column type and data format. Although most of the column types in MS Access and MySQL are compatible with each other, the capacity of specific column type may differ. For example, the type varchar in MS Access is not the same with the type varchar in MySQL. MS Access' varchar holds more characters then that in MySQL. In this case, we have to use text in MySQL to hold up characters in MS Access' varchar instead.
A typical example is the Date Format. In MS Access, date is stored as MM-DD-YYYY, whereas in MySQL, the date is stored as YYYY-MM-DD. Care needs to be given to those fields during conversion.
Other points we have to consider are the differences in the built-in functions, user defined functions, and maintenance.
It seems that there is a lot which we must consider for the migration. However, the real story can be simplified with the help of some database administrative tools.
Currently, there are a wide variety of tools available in the market to suit our needs. Here I will look into Navicat which I personally thinks stands out from the crowd.
Transferring your MS Access data to MySQL through Navicat
Navicat (http://www.navicat.com/download.html) supports imports from various file formats such as text, csv, XML, Excel, MS Access, HTML and some others.
Navicat has a function Import Wizard. In the table view, click the icon Import Wizard.
Animated graphical demonstration: http://support.navicat.com/animations/import_access.gif
In step 1 of the Import Wizard, specify the file type you would like to import. In this case, we choose MS Access database .mdb file. Click Next to proceed.
In step 2, choose the location of the access file. Once you have chosen the location of the file, all your tables in your access files will be shown in the table name box. Choose the tables which you would like to import. In this case, there is only one table present.
The import wizard will skip steps 3-5 and jump to Step 6. In this step, you can manually change the data type of the field, the value shown is the automatically generated type detected by the system.
The import wizard will skip Step 7-9 and jumped to Step 10. Choose Append: add records to the destination table and click Execute to start the converting process.
With the help of Navicat, the conversion process becomes unexpectedly easy. You can finish all the configuring process within one minute.
Navicat not only helps import MS Access data onto MySQL but also contains functions which MS Access has and even more.
Conclusion
This article has discussed the advantages and disadvantages of switching MS Access to MySQL. Whether or not to migrate your data will, of course, depend on your own needs.
  • Free trial version - http://www.navicat.com/download.html
  • Navicat Animated Demo - http://support.navicat.com/visual_tutorial.php