This is a widely recognized fact that MySQL database management system has essential advantages above the Microsoft Access including: cross platforming, better performance, multiple user access and enhanced security system. This clarifies the reason why businesses move their databases from MS Access to MySQL irrespective of the fact that it is extremely complex and requires a challenging approach.
The standard steps to move MS Access database to MySQL, MariaDB or Percona server are listed below:
- Extract MS Access table and constraint definitions, convert into MySQL format and load to the target database server;
- Export data from the source database to intermediate storage, for example csv files, make necessary conversions and load it into the target database;
- Extract MS Access queries in form of SQL statements, convert them into the MySQL format and import into the target database;
- Create set of PHP scripts to display and process all forms from the source MS Access database;
- Port VBA scripts and other applications used to manage the original database to the destination system. In most cases PHP script is the best choice for this purpose.
Despite the fact that steps 4 and 5 requires a lot of work for automation, you will discover several methods to automate other steps in the above list. For instance, Microsoft Access could be used to export MS Access data to MySQL database via Connector/ODBC driver created by MySQL vendor.
To export MS Access Data:
- Click on the table to be exported and select “Export” item from “File” menu
- In the dialog box “Export Object Type” select “ODBC Database”
- After a few more steps Microsoft Access data will be exported to MySQL
This process doesn’t transform queries and certain constraints, however it’s enough for anyone interested in migrating small-scale database without a lot of queries. Or else, you will be required to use a manual during operation and upon the conclusion of the automated part.
Selected dedicated software can also be employed for the Access to MySQL conversion, a few of which enables the automation of the complete process. An example of these tools is available by Intelligent Converters, the organization that specializes in database migration and synchronization since 2001. The software can move all MS Access objects except forms, reports and Visual Basic source code. Additional features of Access to MySQL converter:
- All versions of Microsoft Access are supported
- All versions of MySQL running on Linux/Unix/Windows are supported
- Option to save conversion settings into profile
- Option to filter data using SELECT-queries
- Option to merge MS Access data into an existing MySQL database
- Sync MySQL database with MS Access data
- Password protected .mdb and .accdb files are supported
- Option to export MS Access database into MySQL script file
- Command line support