Even if the process of converting MySQL is tedious, it is considered to be highly beneficial than Microsoft Access for the fact that, it has higher performance, multiple user access, cross platforming and a better security system. On the other hand, when talking about complex or multi-user database system, migration from MS Access to MySQL is highly recommended. For a system with a single user, there is usually not too large data to handle, what MySQL offers is not much when considered to the MS Access database.
Microsoft Access database can be migrated to MySQL according to the following steps
- Extract and convert MS Access tables, indexes and constraints definitions into the target format. Load it to MySQL database.
- Next step is to export the source data into comma separate values files as intermediate storage, make necessary conversions and load it into the target database
- Microsoft Access queries must be extracted from database in form of “CREATE VIEW” statements; those statements must be converted to the target format and then loaded to MySQL database.
There are number of solutions to automate some of migration steps specified above or the overall process. Following the most straight forward way, the source data can be migrated to MySQL database via Microsoft Access itself combined with Connector/ODBC driver developed by MySQL vendor.
To export MS Access data using the product interface:
- On the left pane highlight table to be exported and select “Export” item from “File” menu
- In the appeared window “Export Object Type” select “ODBC Database”
- After a few easy steps the specified Microsoft Access data will be exported into MySQL format
Although this approach doesn’t cover converting queries and any other specific constraints, it is enough for the migration of a little database that does not have a lot of queries. In other case you will have to continue with the process manually in order to complete the phase that has to do with automation.
There are some special software tools that can automate the entire process of database migration from MS Access to MySQL. One of these migration tools is the MS Access to MySQL converter created by Intelligent Converters. The software company specializes in providing high quality solutions for migration, conversion and synchronization of data within popular database management systems including MySQL, MS Access, SQL Server, PostgreSQL, Oracle, SQLite, FoxPro and IBM DB2.
MS Access to MySQL converter is an easy-to-use application yet powerful enough to handle complex and very large databases with performance up to 10000 records per second on the modern platform. The results from this application also comes with high accuracy simply because the program takes care of the migration process in all aspects:
- converts indexes and relationships between tables with related attributes
- migrates MS Access queries into MySQL views
- supports Unicode
MS Access to MySQL converter can migrate the original data into MySQL script that is suitable for situations when there is no direct connection to MySQL server. The can merge data into an existing MySQL database and also synchronize data if both source and destination tables have primary key or unique index defined. In conclusion, Command line support can help with automation, scheduling and scripting of MS Access to MySQL database migration.
