Enterprises are increasingly moving from proprietary platforms like Microsoft Access to open Java platforms. Yet migrating Access applications and re-training Microsoft Access developers to use standard Java tools can be time consuming and costly.
There is a very steep learning curve for MS Access developers to use complex coding frameworks like Java/J2EE. A visual development tool like WaveMaker can flatten this learning curve - minimizing hand coding while generating standard Java applications.
Industry best practices for modernizing MS Access Applications include:
- Rebuild the schema: create a new relational schema that reflects SQL best practices rather than trying to simply recreate the Access schema in a relational database.
- Clean the data: extract the data from the Access database, cleanse the data, then import the data into a new relational database
- Rewrite the application: rebuild the Access application using open and standards-based development tools like WaveMaker.
Open Alternative to MS Access - WaveMaker
WaveMaker is an open source, visual development environment that provides an easy way to move Microsoft Access developers and applications to open Java standards. The following screenshot shows the WaveMaker studio:
WaveMaker's visual, drag and drop development tools are easy for Microsoft Access developers to use, while generating standard Java applications. WaveMaker's WYSIWYG studio is ideal for developers who want to focus on creating enterprise applications, not coding.
Over 15,000 developers depend on WaveMaker today, including Fortune 500 customers like Macy's, National City Bank and Pioneer Energy. WaveMaker is available as a free, open source Community Edition or in a Enterprise Edition that adds commercial security, scalability and support.
The following table compares MS Access and WaveMaker:
Features | MS Access | WaveMaker |
How does it work? | Visual builder for desktop apps | Visual builder for web 2.0 apps |
Who should use? | Departmental developer | Departmental or enterprise developer |
When to use? | Dept apps with few users | Enterprise apps, up to 1000s of users |
Model definition? | Visual definition of tables | Visual definition or database import |
View creation? | WYSIWYG definition of forms | WYSIWYG definition of forms |
Custom logic? | Custom logic in Visual Basic | Custom logic in JavaScript, Java |
Building a WaveMaker application is as simple as following an iterative, three step process based on the Model-View-Controller (MVC) design pattern:
- Define application data (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.
- Create web pages (views). WaveMaker can create default web pages to create, read, update and delete data directly from database schema, or the developer can create new web pages using a drag and drop screen builder.
- Connect data to web page elements (controllers). WaveMaker provides visual tools to connect data and events with visual elements in the application such as buttons and datagrids. The developer can define new actions using a visual action editor that call web services or custom code modules written in Java or JavaScript.
Motivations and Issues in Modernizing MS Access Applications
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.
Many developers are finding increasing pressures to migrate Access applications to more open and manageable platforms:
- 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.
Because MS Access applications were often created in an ad hoc fashion, migration can pose particular challenges. These include:
- 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.
Addressing Data Migration Issues
Many databases provide automated conversion utilities. For example, 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, developers sometimes find it easier to rebuild the data schema 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. Access schemas often resemble an Excel spreadsheet. 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.
Addressing Application Migration Issues
Porting the data from Access to a relational database 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.
Although there are tools available that automate the conversion of a MS Access application to Java, developers often report little success with automated conversion. Instead, the preferred approach is to port MS Access applications to standards-based Java using a visual development platform like WaveMaker.
While is possible to use ODBC to access relational data from Access, most developers choose to rewrite the application. The reasons for rewriting Access applications include:
- Quality issues: many developers feel that the logic for MS Access applications is suspect and would rather recreate the logic on an open platform like Java.
- Desire to make application web-based: most developers would prefer to migrate “legacy” MS Access applications to more robust web architectures.
- Security requirements: developers often want to add “enterprise” security features like Siteminder/LDAP authentication and role-based access controls.
A Process for Modernizing MS Access Applications Using WaveMaker
The consensus of most developers is that automated conversion tools for MS Access do not work. Instead, the best practice for Access migration is to rebuild the schema, cleanse the data and then rewrite the application. This 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 open Java using WaveMaker:
- Rebuild the schema: create a new schema in a relational database like MySQL that reflects SQL best practices rather than trying to simply recreate the MS Access schema in a relational database. Ensure proper definitions for the following elements
- Primary keys
- Indexes for common search and join columns
- Foreign keys for all relationships, along with cardinality constraints and delete propagation constraints
- Default values for columns and Null-allowed columns
- Views
- Clean the data: extract the data from the MS Access database, for example by using a data migration tool or a simple .CSV export. Before importing the data, perform data cleansing:
- Ensure primary key uniqueness
- Ensure referential integrity: check that primary key exists for all foreign keys, ensure foreign key uniqueness for 1..1 relationships
- Ensure that non-null columns have a value
- Ensure that data types agree, particularly for date, integer, decimal data types
- Import cleansed data into new relational database schema
- Rewrite the application: review the forms, reports and queries of the Access application and re-design them to follow Web 2.0 best practices. Rebuild the application forms and reports using web tools rather than trying to convert the existing application and scripts.
- Import relational data schema into the WaveMaker visual builder
- Create new web pages that provide graphical interface for application using the WaveMaker page designer
- Define actions that provide needed functionality for application using the WaveMaker action editor, custom Java or JavaScript code, or web services.
In summary, best practices for Access 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 like WaveMaker.
Other resources
There are a number of additional resources on the web describing MS Access modernization:
Rich Bianco over at the Displaced Guy blog just wrote a post entitled WaveMaker delivers for the cloud like PowerBuilder did for client-server. In it, he talks about using WaveMaker to migrate PowerBuilder applications to Java.
