Since open source database management systems like MySQL become more popular, many commercial and non-profit organizations and even government agenciesmigrate 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.Thosefeatures arethe 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
Documenting Microsoft Access database is quite easy to implement. For example, Access 2007 provides Database Documenter 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. Many database specialists prefer to use some special tools to automate this task. This whitepaper explores such software solution below.
- Migrate Data
Microsoft Access can export the data into MySQL usingthe 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 stepsto 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 withSQL dialectof 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 automateall 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 softwarevendor working in data migration and synchronization field since 2001. The company provides converters for all the most popular database management systemssuch as MySQL, PostgreSQL, MS Access, SQL Server, Oracle, SQLite, FoxPro and IBM DB2.
MS Access to MySQL converter is auser friendlytool having enough capabilities to migrate alldatabase objects to MySQL. Average performance of migration process is 10000 records per second on a modern hardware platform. The program supports Unicode, convertsall indexes and relationships between tables with related attributes, migrates MS Access queries to MySQL views. It supports command line to script and schedulethe database migration.
