Tags: database modernization, Database Upgrades, microsoft-access, modernization roadmap
A leading provider of engineering software has a popular application deployed at thousands of customer locations. They have a plan in place to rearchitect the solution in a couple of years to use a modern application architecture with a SQL Server backend. The problem they faced was that the application used the Microsoft Access 97 Jet database engine. New customers who tried to deploy the solution on newer versions of the operating system were blocked from being able to install the software because of the inherent security risks using the old database technology. So we had to find the way of removing the dependency on the legacy data access technologies and upgrade Access databases to the latest version. In this video, we demonstrate our first approach on how to upgrade Microsoft Access 97 Jet database engine applications to the latest version of the Jet engine that runs on the latest versions of Windows.
Existing programming suite
Our customer develops a programming suite with integrated Microsoft Windows-based applications. These applications provide data gathering, storage, analysis and reporting tools for the needs of the engineering industry. At first, we collected the data on customer’s current environment to assess the architecture of programming suite. In order to do so, we reviewed the source code of customer’s suite components to determine data access method.
These components are written in C++ and are built upon Microsoft Foundation Class (MFC) library. The MFC Library is a collection of classes for building desktop applications for Microsoft Windows. The components interact with a number of Access 97 databases through the special Data Access Object (DAO) classes. These classes provide data access functionality using database engine and corresponding data provider. DAOs act as an intermediary between the application and the database by moving data back and forth between objects and database records. A database engine is the underlying component of a database and a data provider is a component that provides an interface to a data source, such as a database. Microsoft used Joint Engine Technology (JET) as its underlying database engine for Access 97.
Customer’s problem
Customer’s suite components heavily rely on the use of multiple outdated Microsoft Access ‘97 databases which are based on the legacy .MDB file type. That presents a number of risks such as poor performance, scalability, manageability, and security. So, our customer decided to get back to Microsoft compliance. The challenge, how to upgrade Microsoft Access 97 database applications with minimal changes to the code and setup programs. This process involves updating old JET database engine with the new ACE engine which runs on newer versions of Windows. Direct upgrade to the latest Access version was not possible because the modern ACE engine does not support legacy .MDB file type.
At the same time, there was no straightforward way to convert the legacy file type to a modern .ACCDB format. File type conversion is not a foolproof process due to :
- Lack of support for some legacy Access features, programming models, etc.
- Failure may occur without indication
- Functional integrity of database may be compromised
So, we had to eliminate these risks in our Access 97 upgrade solution.
Official Microsoft solution
The existing programming suite is currently dependent on the JET database engine and corresponding DAO data provider as accessed via MFC DAO classes. Microsoft solution assumes replacing the code related to the JET engine (MFC DAO class references) with the code capable of providing data access to the modern ACE engine (MFC ODBC class references). As a result, the new system will be dependent on the ACE engine and corresponding ODBC data provider. Implementation of this Access 97 upgrade solution requires modification of the significant amount of code. This leads to high man-effort and therefore dramatically increases the upgrade costs. In addition, this method presents a high possibility of introducing a defect.
How to upgrade Microsoft Access 97 Jet database engine applications
Instead of replacing all the code responsible for interaction with the database engine, we offered to remediate the existing code the way that ACE engine will be loaded instead of the JET engine. According to this solution, we offered to modify the initialization ‘event’ of all code modules performing data access. This ‘event’ should call a method that would force the ACE engine to be loaded instead of the JET engine. To do so, this method would set the appropriate MFC DAO variable used to store the ‘handle’ of the DAO provider to refer to the loaded ACE engine.
During the development of this solution, we faced an issue with string variable declaration. ACE database engine supports UNICODE strings, whereas the JET database engine does not. So we have made addition code modifications to address this challenge.
The complexity of this approach is low because it is a reasonably straightforward to modify the initialization ‘event’ of all code modules. Since this was a proof-of-concept, there were some issues that we encountered in order to keep application changes to an absolute minimum.
Preview for part 2
In part two of this series, we will talk about how the MFC code can be modified to load the ACE engine instead of the Jet engine. Microsoft had the foresight to supply the source code for the MFC to address changes like the ones needed to upgrade Microsoft Access 97 Jet database applications to newer versions of the database.
