If you are looking for another well-known database management system, Microsoft Access is perfect for handling small databases on desktop machine as well as local network. Though it has a user-friendly interface and also simple to learn, MS Access has crucial restrictions which include:
- Low scalability
- Cannot deploy in the Web
- Poor multi-user support
- It does not support many useful options that come with mature DBMS (stored procedures, transaction logging)
This approach describes the reason why businesses aspire to move their MS Access databases into stronger database management systems though it may require some quite complex process to complete.
How to decide on a destination DBMS? Of course, the primary requirements to help you choose from is the wide variety of advantages of the new system when compared with the present one. Considering the fact that database migration is the good point to make sure you reduce total price of ownership (TCO), numerous businesses think about using free open source database management systems. An example of such DBMS is MySQL and its forks MariaDB or Percona.
As a matter of fact, MySQL has essential advantages over Microsoft Access which include: cross platforming, tight integration into Web, better performance, wide support of ANSI SQL standard, improved security system and multiple user access.
The entire process of database migration from MS Access to MySQL, MariaDB or Percona is made up of all of the following steps:
1) Extract table structures, indexes and constraint definitions from Microsoft Access database
2) Convert it into MySQL format and load to the target database server
3) Export MS Access data into intermediate storage, such as csv files
4) Transform the data (if it’s necessary) and load it into the target database
5) Export MS Access queries into SQL SELECT-statements
6) Transform those SQL statements according to the MySQL syntax and import into the target database
7) Compose PHP scripts to display and process all MS Access forms
8) Port VBA scripts and other applications processing MS Access database into PHP that is the best choice for programming MySQL
Even when steps 7 and 8 can’t be automated, there are numerous approaches to automate other steps of Accessibility to MySQL database migration. First option would be to work with Microsoft Access for transferring data into MySQL via Connector/ODBC driver created by MySQL vendor as follows:
- Click on the table to export and select item “Export” of menu “File”
- In “Export Object Type” dialog box select “ODBC Database”
- After a couple of actions data of MS Access table will be exported to MySQL
Certainly, this process is enough for the migration of a simple database however it cannot convert queries and certain limitations. Complex MS Access databases still need manual procedures upon the conclusion of this automated part.
Second option would be to work with special software for automating the whole process of database migration from MS Access to MySQL. An example of such resources is MS Access to MySQL converter created by Intelligent Converters.
The converter has easy to use intuitive interface, it can transform complicated and huge MS Access database with average capabilities of Two thousand records per second on the modern system. This software enables you to indicate MySQL engine (table type) and charset, convert indexes and relationships between tables along with necessary attributes, translate MS Access queries into MySQL views.
A chance to access MySQL converter can help you move data into MySQL script even with no direct link to a MySQL server. This software offers method to merge data into a pre-existing MySQL server as well as to synchronize the target database with MS Access data. It can handle command line that enables automation, scheduling and scripting of the database migration.
