Microsoft Access is a well-known database management system that is suitable for processing small databases on desktop machine or in local network. Although it has very user-friendly interface and is easy to learn, MS Access has important limitations such as:
- Low scalability
- Cannot deploy in the Web
- Poor multi-user support
- Does not support many useful features of mature DBMS (stored procedures, transaction logging)
This fact explains why many organizations want to migrate their MS Access databases into more powerful database management systems even though it is quite complicated process.
How to choose the destination DBMS? Obviously, the main criteria of choice is number of benefits of new system compared with the current one. Since database migration is the good point to reduce total cost of ownership (TCO), some companies consider using free open source database management systems. One of such DBMS is MySQL and its forks MariaDB or Percona.
In fact, MySQL has important benefits over Microsoft Access such as: cross platforming, tight integration into Web, better performance, wide support of ANSI SQL standard, multiple user access and improved security system.
The process of database migration from MS Access to MySQL, MariaDB or Percona consists of the following steps:
- Extract table structures, indexes and constraint definitions from Microsoft Access database
- Convert it into MySQL format and load to the target database server
- Export MS Access data into intermediate storage, such as csv files
- Transform the data (if it’s necessary) and load it into the target database
- Export MS Access queries into SQL SELECT-statements
- Transform those SQL statements according to the MySQL syntax and import into the target database
- Compose PHP scripts to display and process all MS Access forms
- Port VBA scripts and other applications processing MS Access database into PHP that is the best choice for programming MySQL
While steps 7 and 8 can hardly be automated, there are several solutions to automate other steps of Access to MySQL database migration. First solution is to use Microsoft Access for exporting data into MySQL via Connector/ODBC driver developed by MySQL vendor as follows:
- Click on the table to export and select item “Export” of menu “File”
- After a few easy steps data of MS Access table will be exported to MySQL
Obviously, this method is sufficient for the migration of a simple database but it cannot convert queries and certain constraints. Complicated MS Access databases still require manual operations upon the completion of that automated part.
Second solution is to use special software for automating the entire process of database migration from MS Access to MySQL. One of such tools is MS Access to MySQL converter developed by Intelligent Converters.
The converter has user friendly intuitive interface, it can convert complex huge MS Access database with average performance of 2000 records per second on the modern platform. The program allows to specify MySQL engine (table type) and charset, convert indexes and relationships between tables with all necessary attributes, translate MS Access queries into MySQL views.
Access to MySQL converter can export data into MySQL script when there is no direct connection to a MySQL server. The program provides option to merge data into an existing MySQL server or to synchronize the target database with MS Access data. It supports command line that allows automation, scheduling and scripting of the database migration.
