Checklist: Better Access Applications

Yesterday, I wrote about what makes a good Access application.  By good, I meant the bare minimum required to share your application with other users.  That post was largely targeted at new users to Access, such as Excel power users who realize their data has outgrown Excel and requires something more.

The techniques in this post will take you from an adequate Access application to the kind that you could sell to somebody.  If you want to become an Access professional--someone who companies or individuals would pay for your services--then you should be able to answer yes to all the items on this list.

• Referential integrity is enforced
• Every user has their own copy of the front-end
• A system is in place to deploy front-end updates
• Data is backed up automatically

Referential integrity

To avoid duplicated data in a single table, you need to split the table into two or more separate tables.  This is called "normalizing" your data, and it is one of my requirements for a GOOD Access application.

When you split that data apart, you still need some way to bring it back together.  You join two tables together using "foreign keys."  

For example, consider an Orders table with Customer information.  Such a table in Excel would likely have the order number and customer address all in a single row.  That's a problem because updating the customer's address becomes dicey; we have to make sure we update every applicable row in the table.

To improve the situation, we create a separate Customer table.  Now instead of our Order table containing the customer's address, we have a single field named CustomerID.  This field joins to the Customer table to retrieve the customer's address.

This works great until one day John Smith, CustomerID 42, is deleted from the database.  All of a sudden we have a bunch of Orders for CustomerID 42 but there is no Customer record with that ID anymore.  

To avoid such "orphaned records" you need to enforce referential integrity.  Note that this is done in your back-end data store, which may or may not be Microsoft Access.

Individual front-end copies

Many users build their first Access application for their own personal use.  Then a few colleagues catch wind of it and all of a sudden everyone wants to use it.  The first instinct for many beginning Access developers is to throw a copy of the front-end file onto a shared network folder somewhere and send everybody a link to it.

That is not a good solution.  Microsoft Access front-end files are prone to occasional corruption.  There are many causes, but only one simple and reliable solution: give each user a copy of the front-end file and provide some easy way for them to get a fresh copy whenever needed.  

Deploy front-end updates

Distributing copies of the front-end file may seem more complicated than throwing it out on a shared drive and having users work from a single master copy, but there are many ways to make it work.

There are commercial solutions, like Tony Toews's Auto Front End Updater.  In a network environment, you can use centralized deployment software like PDQ Deploy. You can use any number of scripting languages, like Powershell, VBScript, AutoHotkey, or even plain old Windows batch files.

After all, if you want people to pay for your software, you need to have some way to deliver it, right?

Automatic data backups

In our checklist for GOOD Access applications, I wrote that you needed some mechanism to support data backups, even if that system is manual.  Well, the problem with manual systems is that they don't get used.  If you want reliable backups, you need to automate the process.

The good news is that there are many options for doing this.  In a corporate environment, the answer may be as simple as keeping the back-end database file on a network folder that's already getting backed up on a daily basis.  If the data is stored in SQL Server (or SQL Server Express), you'll need to back up the data into a dedicated backup file first.  I highly recommend starting with Ola Hallengren's database backup (and maintenance) scripts.

Recap

If you're enforcing referential integrity, providing a copy of the front-end for every user--along with a simple way to update it--and backing up the data automatically, then you have a BETTER Access application than many others.  Combine that with some domain-specific knowledge, and you have the makings of an Access consulting company.

As you grow that company, you can start putting in place best practices from the broader software world, so that you can create the BEST Access applications money can buy.  Stay tuned for that checklist tomorrow!

Image by Thomas Skirde from Pixabay