The world's most popular open source database
Christopher Keene is the CEO of ActiveGrid Software, a San Francisco-based company focused on Web 2.0 enterprise development tools. In 2006, he was an adjunct professor at INSEAD, living in Paris. Previously, he was the founder and CEO of NASDAQ-listed Persistence Software, which was sold to Progress Software. Chris has consistently been recognized as a visionary software executive, securing him a place in Intelligent Enterprise's Top Ten Technology Leaders.
According to a recent survey, over 20% of MySQL users plan to migrate a Microsoft Access applications to MySQL over the next 12 months. However there are few documents available that describe best practices for performing such a migration.
This document summarizes discussion from the “MS Access Migration” session at the 2007 MySQL User Group meeting in California. That session brought together a number of MySQL users with a goal of identifying key success factors for moving MS Access applications to MySQL.
Because MS Access applications were often created in an ad hoc fashion, migration can pose particular challenges. MySQL Users reported two common migration problems: Data migration issues: MS Access data conversion is often complicated by poor schema design and even low data quality. Application migration issues: MS Access applications often contain logic or design errors in their forms and reports, making them impossible to convert automatically.
The group consensus was that a successful migration path has three fundamental tasks:
Access is the default choice of departmental developers with moderate technical skills. Often, Access applications are built by downloading corporate data to Excel, converting the spreadsheet to an Access database, then adding ad hoc forms and reports. Because they grow organically, these applications usually lack formal requirements.
MySQL users cited increasing pressures for companies to migrate Access applications:
MySQL provides a data migration tool, the MySQL Migration Tool. However, this tool is only as good as the underlying schema and data of the database to be converted. Because schema and data quality issues are so pervasive with Access, MySQL users often find it easier to rebuild the data schema in MySQL from scratch.
The two most common data quality issues with Access migration are:
Access data schema is not SQL-ready: Access developers are typically not familiar with the basics of SQL schema design. MySQL DBAs report that Access schemas often resemble an Excel spreadsheet more than a classic SQL schema. For example, the schema may lack primary, foreign key, and referential integrity constraints.
Access data is not clean: in part because the tables were not defined rigorously, the data in Access databases is often corrupt. One MySQL user reported finding text strings in fields which were meant to be date fields for geotechnical data.
Porting the data from Access to MySQL only addresses part of the problem. There is still the issue of what to do with the forms and reports associated with the Access application.
In addition, it is often possible to consolidate multiple Access applications into a single web application. Similarly, it is often possible to consolidate several Access forms into a single, well-designed web page.
While is possible to use ODBC to access MySQL data from Access, most MySQL users choose to rewrite the application. The reasons for rewriting Access applications include:
Although there are tools available that automate the conversion of a MS Access application to Java, MySQL users reported little success with automated conversion. Instead, the preferred approach is to port MS Access applications using a coding language like PHP or a web 2.0 visual builder like ActiveGrid.
MySQL users recognize that the process of migrating MS Access applications to MySQL often requires rebuilding the application. They are very interested in any tools that can accelerate the application development process.
ActiveGrid is a web 2.0 visual builder for MySQL that greatly simplifies the task of migrating an MS Access application. Many MS Access application developers (and MySQL DBAs for that matter!) prefer a visual approach to building applications and have little interest in complex Java frameworks. ActiveGrid is ideal for these developers.
The following table shows the similarities between the MS Access and ActiveGrid visual development tools:
Building an ActiveGrid application is as simple as following a three step process based on the Model-View-Controller (MVC) design pattern:
The consensus of MySQL users is that automated conversion tools for MS Access do not work. For example, tools that translate existing Access applications to Java often result in 80% complete solutions where finishing the last 20% of the work takes longer than starting from scratch.
Instead, the best practice for Access migration is to rebuild the schema, cleanse the data and then rewrite the application. Although this is time intensive, it is the only way to ensure that the resulting application is of sufficient quality to be maintainable.
This lays out a step-by-step process to migrate an MS Access application to MySQL:
In summary, best practices for Access to MySQL migration require careful migration of data to a new schema along with a requirements-driven rebuilding of the application forms and reports using web-based development tools. MySQL is an increasingly attractive database solution for companies trying to improve the security and data quality of their departmental applications. However a successful migration from Access requires pairing MySQL with a web development tool.
There are a number of additional resources on the web describing MS Access to MySQL migrations:
Read and post comments on this article in the MySQL Forums. There are currently 7 comments.