The Importance of Auditing

In many cases, the Access databases we create a innocuous tools to make life easier by automating menial tasks or creating better reports from large data sets. But in cases where your application is going to be processing important information, and by important I mean anything that might impact your customers, revenue, or have a potential for litigation, I can't stress this enough: audit your data.

If you don't have the money to buy an off-the-shelf product that contains the right tools to ensure user activity and application processing is tracked and timestamped, take the effort to incorporate similar functionality into your own applications. A little story for your entertainment:

About eight years ago I took on a position at a local business, a temporary position until I went to grad school (well that never happened, but that's another story). My first full day was a training on how to process service order records that were sent on to telephone carriers on the Eastern seaboard. My primary role would be to spend eight hours a day performing this laborious twenty-five plus step process over and over again to take raw data, put it into Excel, then output it into other formats for processing. I spent some time with Microsoft Access and got that process down to a two click activity, relying on user input only where it was critical and couldn't be readily automated. These records, by the way, effected the way 9-1-1 calls were routed, and emergency services were dispatched. So you can be sure I had every single step audited: timestamp, user, machine, what was happening to the data, before/after snapshots, etc. To my knowledge, that little database is still processing about 10-20% of the 9-1-1 records on the Eastern seaboard. Scary, but I'm confident it's completely error free.

My point to you, the reader, is that whether it's life critical or business critical data, be sure you've covered your bases, and your behind, when you decide to create Access databases that will do more than just store addresses and names. I won't go into great depth on how to do it, but some general guidance:

1) Make sure you get the handy functions available from Dev Ashish's MVP site: API to retrieve a user's network username, API to retrieve the local machine name,API to retrieve the user's operating system name.

2) Create a function that you can call from forms and other VB functions that will handle the logging events for you. I typically write to either a local table, linked table, or file depending on whether it's a multi-user Access application or sitting on someone's PC. Alternatively, you can use a logging API like what comes in UI Builder for Access, instead of creating your own.

3) Insert the logging function wherever you need to track user behaviour, errors, or other system events. This could be as granular as when the user logs in, changes a record, etc. Or it can be just for errors.

No only is it a great way to protect yourself from potential liability, help debug your apps, it also ensures your solution has a greater level of legitimacy and value to your organization.