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 Access begin to look for an alternative which allows better database performance, higher reliability, higher flexibility and yet is inexpensive.

With numerous choices available on the market, considering you don't want to put too much investment on it, only MySQL stands out from the crowd. MySQL's flexibility allows you to deploy it across various platforms and also allows multiple concurrent users. If you wish, you can still continue to do your data administration through Access as a front end.

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.

I will first discuss whether or not to migrate your Access data to MySQL. Then, I will cover the considerations, planning and preparations which you should make 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.

Source: http://codewalkers.com/tutorials/79/1.html

MySQL versus Access
When you start to think of whether to use Access or MySQL, the very first point to consider is "what do you need?" The features of Access and MySQL are almost in two different directions: Access can only be deployed in Microsoft Windows while MySQL is cross platform; Access is a Single-User Application while MySQL is a Multi-User Application and many more. To help you to decide whether to keep using Access or to migrate your data from Access to MySQL, I will illustrate some scenarios.

Migrate your Access data to MySQL when...

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 program or other administrative tools to get access to your database by authentication. MySQL can also be integrated with a web server by web programming languages which provides a more flexible choice than 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 login names for it.


You are not the only person who control the data. Access is a single user program for local use. Although it has some sharing features, your data cannot be accessed concurrently in Access. MySQL is a multiple user program. It suits the situation in which you are not the only person who is controlling the data. MySQL is designed to work well in a networked environment and is capable of serving a number of clients.


You want your data secured and only accessed by authorized people. Access data is stored in a local machine and whenever the machine is left unattended, any foreigner may steal your data by copying it to a disk. Although Access does allow users to set a password to a database, it is not a necessity and many people neglect the process. MySQL requires authentication before opening a connection to a database. This enhances the security issue. It also allows user privilege settings which this can help database administrators easily manage the actions each particular user can perform.


Your database is large. MySQL is capable to manage much more data.


You don't want to be tied to Microsoft Windows anymore. MySQL is cross-platform. You can install MySQL on more than 20 platforms including Linux distributions, Mac OS X, UNIX and Microsoft Windows.


You want an open source database. MySQL can be obtained for free while Access cannot. MySQL is now licenced under the GPL license. You can obtain a free copy of MySQL if you are not using it for commercial purposes. For commercial organizations, the license is inexpensive compared to other databases on the market.
Do not migrate your Access data to MySQL when...

You want your data be easily portable.
Since Access is a local based program, you can take your data with your wherever you like by just copying the file into a disk. The file can be opened with an Access program installed in another computer.


You only need single user access to database


You prioritize the use of the reporting features of Access more than the database features of MySQL.


You feel very comfortable with the performance of Access.
After a thorough understanding of the pros and cons of Access and MySQL, you should now able to decide whether or not to move your data to MySQL. If you have decided to migrate your data, the following sections will teach you how to do so.

Source: http://codewalkers.com/tutorials/79/2.html

Before Migration
To start using MySQL, good planning is important as there are some incompatibilities between Access and MySQL. One of the two important data incompatibilities we have to get rid of is the column type and data format. Although most of the column types in Access and MySQL are compatible with each other, the capacity of specific column type may differ. For example, the type varchar in Access is not the same with the type varchar in MySQL. Access's varchar holds more characters then that of MySQL. In this case, we have to use text in MySQL to hold Access's varchar instead.

A typical example of the need to modify data during migration from Access to MySQL is the Date Format. In Access, date is stored as MM-DD-YYYY. In MySQL, the date is stored as YYYY-MM-DD. Care has to be given to those fields during conversion.

Other points we have to take into consideration are the differences in the built-in functions, user defined functions and maintenance, etc.

After a simple introduction on what needs to be addressed before migration, it seems that there a lot which we have to do for the migration. However, the real story can be much simplified with the help on 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 think stands out from the crowd.

Source:http://codewalkers.com/tutorials/79/3.html

Transferring your Access data to MySQL through Navicat
Navicat supports imports from various file formats such as text file, csv file, xml file, excel file, access file, html file and some more.

To import Access data to MySQL, get a 30-day free trial of Navicat from http://www.navicat.com/download.php3.

Inside Navicat, there is an Import Wizard. In the table view, click the icon Import Wizard.

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 of the Import Wizard, 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 only one table is present, but in reality you may have more than one table to import.



The import wizard will skip steps 3-5 and jumped 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 jump to Step 10. Choose Append: add records to the destination table and click Execute to start the conversion process. With the help of Navicat, the conversion process becomes unexpectedly easy. You can finish all of the process within one minute!



Navicat not only helps you importing the Access data into MySQL, but also contains functions which Access has and more. Using MySQL with Navicat works like what you usually do with Access, but making your database more powerful than ever.

Source: http://codewalkers.com/tutorials/79/4.html