MS Access to MySQL Database Migration
Since open source database management systems like MySQL become more popular, many commercial and non-profit organizations and even government agencies migrate their data away from expensive large-scale proprietary systems to more cost-effective solutions. Before planning migration every organization should acknowledge benefits, true effort and costs of moving to an alternative DBMS since database migration is quite complicated procedure.
For example, many Microsoft Access users are migrating to MySQL because of such advantages as high performance, cost-savings and platform freedom. Those features are the valuable reason to unload MS Access applications to the MySQL environment. In fact, they often split their databases in front-end and back-end parts, keep MS Access as a front-end and migrate data and logic to MySQL providing native multi-user environment with more stability, application performance and low total cost of ownership (TCO). As an alternative, the source database can be completely migrated to MySQL running on the same machine, since this DBMS can be easily set up and deployed on any Windows platform.
So, let’s work through all steps of migrating MS Access database to MySQL.
- Document the Data Source
It is quite easy to document Microsoft Access database compared to another DBMS. For example, Access 2007 provides Database Document er feature that creates a well formatted report specifying all database objects, metadata, queries and forms, then exports the set of documentation into one of multiple supported formats (plain text file, MS Word document, etc).
- Design MySQL Database
After all MS Access metadata has been documented, it is time to create the target MySQL database. The goal of this step is to create MySQL equivalents for all entries of the source database including types mapping, correct processing of related attributes, etc. This task requires much attention as it enclose a lot of opportunities for errors. This is the reason why many database experts use special software to automate this task. This whitepaper explores such approach and related solutions.
- Migrate Data
Microsoft Access can export the data into MySQL using the appropriate Connector/ODBC driver as follows:
- On the left pane of MS Access highlight the table to export and select “File” > “Export” menu
- In the appeared dialog called “Export Object Type” select option “ODBC Database”
- After a few further steps to set up migration options, the data will be exported into MySQL
- Convert Queries
Microsoft Access queries are exported into SELECT-statements, those statements are converted to comply with SQL dialect of MySQL and then imported into the destination database. This step requires deep knowledge of SQL language and can cause some errors when implementing it manually.
There are special software tools that can automate all steps of MS Access to MySQL migration listed above, even for huge, sophisticated databases with a lot of tables, constraints and queries. MS Access to MySQL is one of those tools provided by Intelligent Converters, a software vendor working in data migration and synchronization field since 2001. The company provides converters for all the most popular database management systems such as MySQL, PostgreSQL, MS Access, SQL Server, Oracle, SQLite, FoxPro and IBM DB2.
MS Access to MySQL converter is a user friendly tool having enough capabilities to migrate all database objects to MySQL. Average performance of migration process is 10000 records per second on a modern hardware platform. The converter supports Unicode,processes schemas, indexes and relationships between tables with all necessary attributes. MS Access queries are migrated into MySQL views.Command line support allows to script and schedule the database migration.