Migrating From MS Access To MySQL

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:

1. Rebuild the schema: create a new schema in MySQL that reflects SQL best practices rather than trying to simply recreate the Access schema in MySQL
2. Clean the data: extract the data from the Access database, cleanse the data, then import the data into the new MySQL schema
3. Rewrite the application: rebuild the Access application using web development tools like php or ActiveGrid

Motivation for MS Access to MySQL Migration

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:

• Low data quality: Access applications often have out-of-date corporate data or corrupt data based on poorly defined schemas
• Poor security: Access applications do not integrate with corporate security and do not allow advanced security such as role-based access controls
• Limited manageability: Access applications can not be centrally managed by IT
• No web-based distribution: Access applications cannot be accessed over the web
• SOX compliance: Access applications are often identified in corporate audits as a significant source of risk.

Addressing Data Migration Issues

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.

Addressing Application Migration Issues

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:

• Quality issues: “given that the original MS Access app was written by a non-programmer, I don’t even want the logic moved – I don’t trust it.”
• Desire to make application web-based: most MySQL users would prefer to migrate “legacy” MS Access applications to more robust web architectures
• Security requirements: MySQL users often want to add “enterprise” security features like Siteminder/LDAP authentication and role-based access controls

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.

Rapid Application Development For MySQL - 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:

1. Define the model. The model defines the data used in the application, including database tables and relationships between tables. The developer specifies this information by importing an existing database schema or using a visual data editor.
2. Create the views. The view describes the web pages displayed by the application. ActiveGrid can create default Ajax web pages based on the database schema, or the developer can create new web pages using a visual screen builder.
3. Build the controller(s). The controllers manage actions within the application, including navigation between pages and invoking data, security and web services. The developer can define new actions using a visual action editor that call web services or custom code modules written in Java or Python.

A Process for MS Access Migrating

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:

1. Rebuild the schema: create a new schema in MySQL that reflects SQL best practices rather than trying to simply recreate the MS Access schema in MySQL. Ensure proper definitions for the following elements
   1. Primary keys
   2. Indexes for common search and join columns
   3. Foreign keys for all relationships, along with cardinality constraints and delete propagation constraints
   4. Default values for columns
   5. Null-allowed columns
   6. Views
2. Clean the data: extract the data from the MS Access database, using the MySQL Migration tool or a simple .CSV export. Before importing the data, perform data cleansing:
   1. Ensure primary key uniqueness
   2. Ensure referential integrity: check that primary key exists for all foreign keys, ensure foreign key uniqueness for 1..1 relationships
   3. Ensure that non-null columns have a value
   4. Ensure that data types agree, particularly for date, integer, decimal data types
   5. Import cleansed data into new MySQL schema
3. Rewrite the application: review the forms, reports and queries of the Access application and re-design them rebuild the application forms and reports using web tools rather than trying to convert the existing application and scripts.
   1. Import MySQL data schema into visual builder tool such as ActiveGrid
   2. Create new web pages that provide graphical interface for application using the ActiveGrid page editor
   3. Define actions that provide needed functionality for application using the ActiveGrid action editor, custom Java or Python code, or web services.

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.

Other resources

There are a number of additional resources on the web describing MS Access to MySQL migrations:

• MySQL Forum for MS Access migration
• MySQL Migration Toolkit
• ActiveGrid developer forum
• Automated Access to PHP conversion
• Automated Access to Java conversion
• MS Access to MySQL migration