FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:

Microsoft Access Developer Center

Table Design

Query Design

Form Design

Form Tips and Mistakes

Form Navigation Caption

Using a RecordsetClone

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppressing Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Adding Buttons to the Quick Access Toolbar

Collapsing the Office Ribbon for more space

VBA Programming

Using Nz() to Handle Nulls

Avoiding Exits in the Body of a Procedure

Shortcut Debugging Keys

Setting Module Options

Math Rounding Issues

Rename a File or Folder

Source Code Library

Microsoft Access Module VBA Library

Royalty Free VBA Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual Source Safe

Deployment

Runtime Downloads

Simulating Runtime

Prevent Close Box

Disable Design Changes

Broken References

Missing Package & Deployment Wizard

Terminal Services and RemoteApp Deployment

Avoid Program Files Folder

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Microsoft Access Help

MS Access Developer Programming

More Microsoft Access Tips

Technical Papers

Microsoft Access Tools

Connect with Us

Email NewsletterEmail Newsletter Signup

FMS Development Team BlogDeveloper Team Blog

Facebook PageFacebook

Twitter with FMSTwitter

FMS Support SiteSupport Forum

 

 

News Features

Total Access Statistics 2013 ships! Add advanced data and statistical analysis to Microsoft Access!

Microsoft Access Statistical Analysis


Total Access Emailer 2013 ships! Easily send personalized emails with Access data to everyone!

Microsoft Access Email Blaster


Get our Latest News

Email NewsletterLatest Newsletter (Sign up)

FMS Development Team BlogBlog with us and subscribe to our RSS feed.

FMS Facebook Page Like our Facebook Page

FMS Twitter Follow us on Twitter

Microsoft AccessTaking Over (Inheriting) Legacy Microsoft Access Database Applications

by Luke Chung, President of FMS, Inc.

PowerPoint Presentation on Taking Over Microsoft Access Databases


Introduction and Background

Since its revolutionary introduction in 1992, Microsoft Access has been customized by end users and professional developers to create a wide range of solutions. By becoming a part of Microsoft Office with the Access 97 version, Access established itself as the most popular desktop database in the world. It's now evolved with Microsoft Office through Access 2000, 2002, 2003, 2007, 2010 and 2013.

Most organizations are literally filled with Access databases. Of course, there are more Excel and Word files, but Access applications have a longer life span, and over time, some of these databases remain in use and continue to serve a critical need for its users.

Many of these legacy databases were designed and created years ago by people who are no longer with the organization or can no longer remember them. New people need to enhance the application and there's the challenge. Inheriting and taking over someone else's software is challenging in any environment. MS Access is particularly challenging because it combines user objects and code. Programming experts often finds this disconcerting because everything isn't done in VBA code alone, while non-programmers or power users are intimidated by all the VBA.

Certainly, someone who takes over another application needs to understand the fundamentals of how the platform works. This paper doesn't attempt to teach how to use Access. Instead, assuming you already understand Access, we explore tips and techniques we've found helpful for working with inherited Access applications.

There are many types of Access applications and enhancements requiring the assistance of an Access developer, so it would be foolhardy to claim a single solution to address all these situations. This paper attempts to cover some of the more common situations and how we've approached these challenges by combining our knowledge of Microsoft Access with the various tools we've built to address these issues efficiently and consistently.

Overview


Who Takes Over the Microsoft Access Application?

Since there are so many Access databases with various levels of sophistication, one cannot give a blanket statement. Certainly, if there's lots of code, the person who is expected to take over must be able to understand and write VBA code. However, if the enhancements are simply modifying or adding new reports, someone skilled with creating Access reports could do the work.

That said, Access users and programmers, like any other platform, grow and evolve their skills over time. They may not be able to handle the most difficult applications in the beginning, but over time, they will hopefully gain the necessary skills and avoid the past mistakes that give them headaches today.

Definition of a Microsoft Access Developer

For lack of a better term, I will call the person who is customizing a Microsoft Access database as the developer without requiring this person to be a "professional programmer". It is critical that an Access developer have a solid understanding of how to use the Access user interface to create objects. That means how to create tables, queries, forms, and reports. Experience creating tables and designing normalized database structures is also critical. After all, Access is a database first, then a programming platform.

Being able to automate steps through macros and/or some basic VBA module code would also be helpful but it's much more important to understand and use all the pieces of the Access user interface and menus. Through the user interface, they are able to create solutions using queries, forms, and reports, that require minimal coding. Because they understand the power of the Access objects, code is only written when the user interface is insufficient.

With this skill set, an Access developer can support many Access applications to properly maintain and enhance them.

Please note that these are the minimum requirements. Ideally, the skills of the developer taking over the project exceeds the skills of the original author. Our emphasis on the user interface and database skills is borne from experience seeing lots of code written to address problems that the user interface can solve more efficiently or for "free" through a query, simple command, or better database design.

Working Collaboratively with End Users

A big competitive advantage of Access developers is their ability to straddle the end user needs with the more sophisticated professional, back-office IT operations. Microsoft Access fills the gap between the flexibility and ease of use in Excel which is entirely controlled by the end user, to the more rigorous demands (and benefits) of database applications.

Great Access developers have an excellent understanding of their organization's requirements, what the data really means, its relation to other data and what it's used for. He/she is also comfortable working with end users in an iterative manner to reach the final solution.

Demanding an upfront, detailed design document is nice, but that rarely works because most end users simply cannot provide it perfectly -- because they simply don't know. But as soon they see a result, they can make good suggestions that they could not perceive before. Access provides an environment where rapid application development (some call it prototyping) can be performed quickly and for minimal cost compared to alternative platforms.


Taking Over the Entire Microsoft Access Application

If you are simply tasked to create a new report or modify some fields on a form with no other support needs in the future, then that may be all you need to do. Enhance the database and move on, you'll never have to think about it again.

What we're considering here is the larger situation when you inherit the entire MS Access application and assume total responsibility. You'll need to maintain it over the foreseeable future, and establishing Best Practices to minimize the costs and headaches, while maximizing its functionality and potential, makes sense. Areas to consider:

Triage

Stop the bleeding and stabilize the application. Do what's necessary quickly while establishing base line support so a worse disaster can't occur.

  • Fixing Immediate Problems
  • Backup and Disaster Recovery Plan
  • Basic System Administration Needs

Documenting what You Have, What's Wrong, and What Should be Fixed

Once stabilized and the immediate emergency is over, it's important to spend some time to assess the situation and go into detail to see what's working, what's not working (but hasn't been reported yet), and what can potentially cause problems in the future. You may also discover opportunities to make simple changes that have a very positive impact:

  • Applying Best Practices such as:
    • Finding tables without primary keys or odd primary keys
    • Detecting inconsistencies with the same field name having different types across tables
    • Having good startup routines when the application launches
    • Having centralized code for printing and previewing reports
  • Cleaning Up Existing Code
    • Making sure Option Explicit is used in every module and the code compiles
    • Establishing a global error handling system and error handling to every procedure that lack it
    • Format the code so it's more readable and maintainable
    • Apply variable naming conventions based on data type and scope

Strategy for Updating the User's Database Efficiently

It's important to also lay the foundation to make it easy to deploy your updates for the application. Nothing worse than making the changes and spending even more time deploying it. Ideally, you'd like to do that as easily and transparently as possible for your users. We've established processes where this is centrally controlled and users automatically receive updates the next time they launch the program.

  • Splitting the Database
  • Multi-user Support
  • Simplifying Deployment
  • Establishing a Quality Assurance/Testing Process

Adding New Features in Access and 3rd Party Products

There are many ways to add new features to your application. Some just require coding the specific features requested by the users. The other takes the application beyond what users want, and provides them with functionality they didn't even know they could ask for. This is a combination of applying the features in the latest version of Access and using 3rd party products such as those from FMS:

System Administrative Needs

  • Application Development Processes
  • Using Microsoft Visual SourceSafe (VSS)
  • Simplify deployment of application updates (Total Access Startup)

Of course, every situation is unique and the approach necessary may differ. We'll basically outline what we generally do when taking over an existing application.


Triage: Stop the Bleeding

When an emergency exists, we simply need to fix what's critical and worry about the other issues later. Obviously, how to fix specific problems is beyond the scope of this paper.

As a resource, the 100,000+ lines of code in our Total Visual SourceBook may help address specific issues and let you address those needs without having to write your own code, but when a fire is burning, you do what you need to do to stop it.


Backup and Disaster Recovery Plan

If you're now responsible for the application, avoid any chance of being blamed if a hardware failure, user mistake, or database corruption wipes out data or copies of the application. You should have backups of the application kept on another machine and also off-site. Off-site can be physically on a CD in another location or online.

Use Off-Site Backup Storage (free sites available)

Lots of organizations provide online storage. If the database isn't too big (and security is not an issue), you can zip it up and email it to a Hotmail, Google or Yahoo account. You can also create a Microsoft hosted SkyDrive account where you can store up to 7GB for free (25GB if you upgraded your legacy account) with more space for purchase.

Document the Plan

Establish how often the application needs to be backed up. This determines how much data (time) the organization is willing to risk losing between backups. Then document where the backups go, and how they can be recovered. In addition to the Access application you need to make sure related programs such as Office/Access, referenced libraries, ActiveX controls, and other dependencies are documented with instructions on how to reinstall them.

Test the Plan

There are many disaster recovery plans that were never actually tested, so when disaster struck, an unrecoverable failure occurred. You may not be able to test the complete plan during the triage phase, but at the very least, verify the backup copies really exist.

Microsoft Access compact and backup databases on a scheduleFor more details, read our paper on Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications

Make Sure Basic System Administration Needs are Met

Our Total Visual Agent program provides the repetitive database chores that are necessary to keep your Access applications healthy. Access Jet databases (MDB and ACCDB) files need to be periodically compacted for optimal performance. Total Visual Agent lets you schedule these in the middle of the night so you don't have to worry about it. It also lets you zip and create backups of the file, run data extracts if users are still in it, collect database statistics, and launch any macro on a schedule you specify.  Events can be scheduled hourly, daily, weekly, monthly, or at a time you specify. All the actions are kept in an audit log and if something goes wrong, you're notified via email. A fully functional demo of Total Visual Agent is available.


Microsoft Visual SourceSafeUse Microsoft Visual Source Safe (VSS)

For our development environment, we use Microsoft Visual SourceSafe (VSS) to manage our work. VSS is included with MSDN and provides source code control for Access development (and other platforms). It's critical if multiple developers are involved so you don't overwrite each other's work, but also useful if you're the only developer.

It lets you check-out and check-in individual objects (queries, forms, reports, macros, and modules), and keeps copies of your checked-in versions so you can see what changed over time and rollback if necessary. One shortcoming is that all the tables are treated as one object so only one person can change table structures at a time (understandable since table relationship go across tables).

VSS integrates with Microsoft Access directly from the ribbon when you install the Access Developer Extensions. Here's a tip for Speeding Up Microsoft Access and Visual SourceSafe (VSS)


Understand the Database Architecture

The first step is to understand the overall design of your database and its dependencies on other objects and Windows:

  • Is the application single user or multi-user?
  • How the data goes in and out of the application?
    • Is it all from data entry?
    • Is data shared by other applications?
      • If so, what do those applications do to the data?
  • Is it all in Access (Jet)?
    • If so, is it broken up into a front-end and backend database?
  • Does it use data from other sources?
    • Linked tables (Jet, SQL Server, etc.)
    • Imports from download files, Excel, etc.
  • Does it export data to other sources?
    • Exports to Excel or Word
    • Exports for other applications
  • Is there workgroup or password security?
  • Do files violate Windows security (User Access Control)?
    • Microsoft Access files which require editing, cannot be installed in C:\Program Files
    • Use the User’s Application Data folder for temporary files

Split Database Architecture

If the application is a single database containing both the data and programming around it, it needs to be split so that it's more easily maintainable. It's the proper architecture for supporting multiple users and application enhancements

  • Back-End database resides on the network and has all the data that’s shared among users
  • Front-End database includes:
    • Links to the back-end database tables
    • Application objects (queries, forms, reports, code)
    • Temporary/intermediate tables that are part of processing output to avoid multi-user collisions
  • Front-End database can be easily replaced without impacting the data
  • Avoids getting the users’ latest data
  • Is local to each user (performance gain)
  • The front-end database should know its version, and check a table in the back-end database that contains the latest version number so it knows if it's out-of-date

For more information, read our paper: Microsoft Access Split Database Design to Improve Performance and Maintainability (front end and back end databases)


Simplifying Application Deployment

Making it easy to deploy your application provides a more professional experience for your users and simplifies deploying updates of your database. For optimal performance, each user should have their own copy of Access deployed to their local machine, but you want to update that when you create a new version. Here are some things to implement:

  • Microsoft Access Database Startup Version LauncherUsers should click on a shortcut and start the application
    • Avoid having them know your database name. They shouldn't have to run Access, then open your database. The shortcut should hide that from them.
  • Make sure the right version of Access is launched with the database – do not trust Windows to just launch an MDB file
  • Recognize that what you build will have bugs and updates will be necessary
  • Simplifying the deployment process reduces the pain of each update and makes you look better
  • Make it transparent to users

To address many of these issues, we created our Total Access Startup program to let you centrally manage all your Access applications and automatically update the database on each user's desktop when you release an update. Eliminate the need to manually go to each user's machine.


Set Database Startup Settings

Make sure that your application launches with settings to minimize problems your users may encounter:


Making Sure the VBA Module Code Compiles

One of the first steps for examining a database is to make sure its VBA code compiles. Code that compiles may still have lots of problems, but code that doesn't compile is guaranteed to have problems now. Open any module and from the VBA menu, select Debug, Compile...

Module VBA Compiles

Fix or delete any of the code the compiler flags. Unfortunately, it may not be so easy because while the compiler pinpoints syntax problems, you may actually need to fix the broken code to make it functional. Of course, if you can identify unused code, deleting the offending code is the easiest if you are confident you'll never need to use it.

Note that if the module doesn't have Option Explicit set, the compile feature will not catch syntax errors in that module. We identify these in our Total Access Analyzer and even fix it in our Total Visual CodeTools products described later.

Decompiling Corrupt Code

There are situations where an Access application gets into a state where its code and the built-in VBA functions fail. Compacting the database does nothing and people mistakenly believe the database is corrupt. It is corrupt, but only the code. This is fixed by:

  • Decompiling the database
  • From a module in design mode, selecting the Debug, Compile.. menu
  • Compacting the database

To decompile a database, use this Windows Run command line:

C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE /decompile

where the first part is the entire path where your copy of Access is installed. This loads Access, then open your database and follow the steps above.

For simplicity and since you'll likely need it over time, you should create a Windows shortcut with this command so you can open Access in this mode when you need it.

Access doesn't behave any differently with the decompile option other than decompiling the code in the database you open. Once you resolve that, Access runs behaves normally.

For more information, read our paper: Decompile Your Microsoft Access Database to Improve Performance, Fix Corruption, and Avoid Strange VBA Errors

Verify Library References and ActiveX Controls

A code area that could have problems is library references. These can be seen from the VBA menu: Tools, References.

Libraries contain code that the application requires. Some are built-in Microsoft libraries such as VBA, Access, Office, DAO, ADOX, Outlook, etc. But others may be part of your application.

Make sure there are no broken or missing library references. For instance, the application may use commercially available ActiveX controls or code libraries. If so, you'll need to make sure your development environment has these controls and/or libraries.  Without the libraries your code may not compile, and without the ActiveX controls (including their license to modify), you may break the forms or reports using those controls.


Take Control, Identify Problems, and Position Yourself for Success

Assuming you have a bit more breathing space, we created and use a few of our products to help us take control quickly, detect and fix problems that we can identify before users do, and make the code easier to read and maintain.

Microsoft Access Database Documenter and Error DetectorTotal Access Analyzer Generates Documentation and Finds Errors and Suggestions for Design Improvements

The first task we perform is running Total Access Analyzer on the Access database to understand what's going on. Total Access Analyzer examines each object in the database to determine what's in it and how it connects to the other objects. It generates a wide range of useful reports to help us understand the existing work. Here are some of the highlights we use immediately:

If Total Access Analyzer finds really serious errors, like broken table or field references and Option Explicit not in your modules, you may need to fix these immediately. By adding Option Explicit, you may find all sorts of other errors that the compiler previously skipped. For its other findings, we use it to diagnose and prioritize what needs to be done later.

Cleanup VBA Code with Total Visual CodeToolsCode Cleanup Feature in Total Visual CodeTools Makes the VBA Code More Readable and Maintainable

There's nothing worse than reading someone else's code with non-standard indentations, variable naming conventions, etc. I often find myself trying to reformat the code before being able to read and diagnose it. To avoid this busywork, our Total Visual CodeTools product has a VBA Code Cleanup feature. It can do a lot of things but at this stage, we would use it to:

  • Add Option Explicit to any module that lacks it.
    This lets the compiler catch blatant coding errors.
  • Standardize indentations so loops are properly indented
  • Apply variable naming conventions (if you're used to having variable names identified by their data type and scope which makes it easier to understand the code)

With these changes, the application doesn't behave differently. The code is just easier to read, understand, and maintain.


Add Robust Error Handling

There are lots of ways to improve the Microsoft Access application. The first step is creating the error handling system to know what's broken without requiring every crash to be manually reported to you. This can be done with a professional VBA error handling system with error handling code in each procedure and a global error handler. Read this paper Microsoft Access, VBA, and Visual Basic Debugging Tips and Techniques if you're not familiar with error handling.

The error handling system can automatically:

  • Present a friendly screen to let the user know a crash occurred through no fault of their own and who to contact
  • Create a text file or entry into a database to record the crash:
    • The error number, error description and line number
    • Procedure call chain so you know how they got to that point
    • Current state of the system (where it's installed, Access version, particular objects or IDs, DLLs, etc.)

With a robust error handler that's triggered when unhandled errors are encountered, developers:

  • Acknowledge that a bug really occurred rather than blaming the user
  • May identify the bug without needing to consult the user (by knowing the error and exact line of code, the solution may be obvious)
  • Rely less on the user's recollection (which may not be reliable) for reproducing the crash
  • Simplify the creation of repro steps when necessary

The result is an efficient system to trap, document, diagnose and fix bugs. With this system in place, people don't need to manually report bugs because they'll be automatically recorded. We've found that many users don't report their crashes because they often feel they are at fault, or worse yet, they feel the application is useless and of poor quality. By knowing what's wrong and fixing them proactively, the quality of the application constantly improves.

Microsoft Access source code library for error handlingAdding Error Handling to Your Application

If you don't already have a global error handler, consider getting the source code from Total Visual SourceBook. Among its huge code library, there are two of particular interest here:

This code is what you'll call when you start your application and in every procedure.

Applying Error Handling to Your Application

Once you have the code you'd like to use when an error occurs, you need to add it to every procedure. This can be a monumental task and prone to error if you do it manually. To document the call stack of every procedure and how it calls each other, we also want to have code at the beginning and end of each procedure. Fortunately, a feature in Total Visual CodeTool's Code Cleanup routines adds your custom error handler to every procedure that lacks it.

Examples of the VBA Code Cleanup feature were described earlier, but now we're actually changing the behavior of the application.

When we're ready to ship the Access application, we'll want to add line numbers to every line of code (so we can Pinpoint the Exact Line Where A Crash Occurs. The Code Delivery feature does this along with many other features if you want to obfuscate the code.


Conclusions

Taking over someone else's work is challenging regardless of the technology. We hope this paper helps you anticipate and address some of the issues so you can position yourself for success. At FMS, we've taken over hundreds of databases created by others and automated many of the steps to implement enhancements and ensure consistency. In fact, we created most of our Microsoft Access products to address the needs we encountered during application development, taking over someone else's work, and deploying them. If you're working on critical projects, our Total Access Ultimate Suite will simplify your work and pay for itself very quickly.

If you need direct help, whether it's triage, making enhancements in Access, upsizing to SQL Server, or migrating to a new platform, consider our Professional Solutions Group and our custom Microsoft Access Programming Services. Contact us for more information.

Good luck!

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright © FMS, Inc., Vienna, Virginia
Celebrating our 27th Year of Software Excellence