Checklist: Is Your Access App GOOD?

Mike Wolfe -

What makes a good Access application?  This first checklist should be considered the minimum requirements for sharing an Access application with other people.

If you aren't meeting at least these standards, then your Access program is inadequate.  You don't have an application; you have a ticking time bomb.  Keep that sucker to yourself until it's able to clear this very low bar.

  • Split the front-end user interface from the back-end data
  • Ensure the application is usable within a runtime environment
  • Avoid duplicating data in tables
  • Back up data regularly (even if just manually)

Split the front-end UI from the back-end data

There are a few reasons to do this:

  1. Form and report objects are the biggest source of corruption in Access files.  Keeping those objects separate from your most important asset--your data--is a good insurance policy.
  2. If the user interface and the data are in the same file, there is no way to update the user interface while others are updating the data.
  3. The user interface and data storage are fundamentally different functions of an application.  That Access files can perform both roles does not mean they should do so in a single file.

Ensure runtime usability

The runtime version of Access lacks the features required to create or edit forms or reports; modify macros or VBA code; or use the Navigation Pane to open tables or queries directly.  Rather than rely on that built-in functionality, runtime users rely on pre-built forms and reports to view and edit data.  This is much safer than allowing users to edit data directly.

In fact, you can run a full version of Access in runtime mode by using the /runtime command line switch.  This best practice prevent users with a full version of Access from accidentally changing things they shouldn't.  You can also compile your front-end file into an .mde or .accde to achieve the same effect.

Avoid data duplication

Many Microsoft Access applications begin their lives as Excel workbooks.  Excel is "flat-file" storage.  There are typically no formal relationships among the worksheet data in an Excel workbook.  As users grow frustrated trying to manage large amounts of data in Excel, they may read that the solution to their problem is to manage that data in Access instead.

The problem, though, is that many users simply move the data from Excel into Access without restructuring it.  This is understandable.  To a new user, an Access table looks an awful lot like an Excel worksheet.  It seems logical, then, to treat it as such.  New users won't notice anything is amiss until they go looking for the "Remove Duplicates" button and realize no such thing exists in Access.

The proper way to "remove duplicate data" in Access is to normalize your tables.  Data normalization is its own topic.  For now, a good starting point would be to walk through the Table Analyzer wizard built in to Access.

Back up data regularly

The only data you have to back up is the data you can't afford to lose.  You're probably thinking, "I can't afford to lose any of my data!" OK, then.  Act like it.

As we get into higher tiers of Access applications, we will discuss automating backups.  For now, just make sure you provide some simple way for your users to back up their data, even if it has to be done manually.

Image by PublicDomainPictures from Pixabay