Reverse engineering a MS Access database before we converting it to a web-based application

We are often approached by Clients to, either improve their existing MS Access application or, to convert Access to Web. In many cases the original MS Access Developer is not available anymore. Which means we have to reverse engineer the MS Access application to establish what it does before we can improve or covert it to Web based application.

This is normally a daunting task since not all Developers make sufficient notes in their code and processes. We would then have to depend on the Clients or Users of the application, to give us some insight. It does not help much with regards to the back end processes.

Here are some of the steps that can be followed to make sense of a MS Access application:

1. Look at the Relationship layout of the database tables (if it is a split database, look at both layouts). Note that this is not always reliable since relationships could be set up in individual query as well.
2. Make sure Hidden Objects are displayed (in Access Options) to show any hidden tables etc.
3. Look at the start-up sequence. Is there an AutoExec Macro loading VBA code or a start-up Form, or is the start-up Form selected in the Access Options.
4. Begin with the start-up Form and look at the code behind the events in the following order:
   
   Open ==> Load ==> Resize ==> Activate ==> Current
   
   Note: If there are no active controls on the form, the GotFocus event occurs for the Form after the Activate event but before the Current event.
5. Next investigate the code behind each command button and Combo, or List Box before looking at the rest of the code for the start-up Form.
6. From there try and follow the natural flow of the application by looking at the above mentioned events and also at any Menu items.
7. The MS Access Developer would normally have disabled warnings for any queries called in code.
   You can Find & Replace all “DoCmd.SetWarning False” commands with “DoCmd.SetWarning True” for all code in the Project if you think it will help you get an idea of when queries are called.
   (Needless to say it is best to work off a copy of the original database application.)
8. Look at the Source (table or Query) of the start-up Form, as well as each Form you are investigating bearing in mind that the source could be set in the code itself.
9. Keep notes on which Forms and Queries update which Tables.
10. Work and test with real data instead of your own dummy data.
11. Have multiple meetings with the Client and/or Users where they explain each part of the application. Some features might not be used anymore, and others might have to be added or changed. Remember to make screen-capture videos so you can compile notes afterwards. The idea would be to understand the business rules that govern the system, as well as the design, so both aspects can be improved upon.
12. There might be processes like Inventory systems, or complex Pricing models that need to be unravelled and improved upon.
13. Our job is either to improve the existing MS Access application or convert it to a Web-based application. So we will start off with reverse engineering the MS Access database, while creating notes and thinking of the new design.
14. Keep in mind that any changes made, could have an effect on other parts of the application, so a clear understanding of the application is required before making changes.

The above steps are only covering the tip of the iceberg and might be different depending on the type of MS Access application and if the goal is to improve, or convert Access to Web.

In our Company we have a Team of MS Access Consultants, MS SQL or MySQL DBAs and other Team Players, who work together on the Custom Software development due to the complexity of a project.

Additional Services that we Provide:

• SQL Consulting.
• Remote DBA services for MS SQL or MySQL.
• Database design for MySQL or MS SQL.
• Database optimization.
• Convert your Desktop Application into a web-based solution.
• Custom Web scraping development while using dot net.
• Optimize your Stored Procedures.
• Dot Net development.
• Alpha AnyWhere Developers, also known as: Alpha Five Development.
• Mobile application development Solutions.
• Convert Visual Pro Apps to Web based solutions.
• Maintain Visual FoxPro Apps.
• Convert MS Access To Web.

To learn more about our services please contact us or call via this phone: 732-536-4765.