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, 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 paper, I will firstly discuss about whether or not to migrate your Access data to MySQL. Then, I will cover about the considerations, planning and preparations which you 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 is that “What You Need?”. The features of Access and MySQL are almost in two different directions: Access and 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 think of whether or not to keep using Access or migrating your data from Access to MySQL, we will illustrate some scenarios that you should migrate or should not.

Migrate your 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 program 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 Access alone. You can get your data from MySQL remotely from everywhere 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 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 to the situation that 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.

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 Access do allow user to set password to a database, it is not a necessity and many people neglected the process. MySQL requires authentication before opening connection to a database, this enhance 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.

MySQL is capable to manage xxxxxxxxxx data and even more.

5. You are not going to use Microsoft Windows anymore

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 Access cannot. MySQL is now under the GPL license. You can obtain 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 Access data to MySQL when…

1.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.

2. You only need single user access to database

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

4. You feel very comfortable for the performance of Access

After 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 are 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 well planning is important as there is some incompatibility between Access and MySQL. To transfer the data successfully, some points we have to get rid of. One of the two important data incompatibilities we have to get rid of are the column type and data format. Although most of the column types in Access and MySQL do compatible with each other, however, 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’ varchar holds more characters then that in MySQL. In this case, we have to use text in MySQL to hold up characters in Access’ varchar instead.

A typical example on the need to modify data during migration from Access to MySQL is the Date Format. In Access, date is stored as MM-DD-YYYY, whereas 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 care are the differences in the built-in functions, user defined functions and maintenance, etc.

After a simple introduction on what to care for before migration, it seems that there a lot which we have to put up with 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 it stands out from the crowd.

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 a function Import Wizard. In the table view, click the icon  Import Wizard.

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

Conclusion

This articles have discussed the advantages and disadvantages of switching Access to MySQL. Whether or not to migrate your data solely depends on your own needs.

Reference

[1] MS Access - http://www.microsoft.com/access/

[2] MySQL – http://www.mysql.com

[3] Navicat – http://www.navicat.com, also available for PostgreSQL (http://pgsql.navicat.com)