The question I’m often asked is What is database auditing and how do I create an audit trail in Microsoft Access?

The second part to that question means the inquisitor has realised there are no standard MS Access tools available (out-of-the-box) for you to utilise and developers are therefore left to their own skills and time to creating a tool instead.

In this quick article, I want to highlight the key components to designing your own auditing tool and keep track of records which refers to the first part of the question about database auditing meaning the monitoring of data changes in an Access database.

What Is Database Auditing – Creating Your Own Solution!

If one user changes values to a record and then another decides to modify or even delete the same value then where is the history of all the changes? Simple answer, there is no history!

Creating an audit log of some kind can cover many different aspects but here the essential element for me is keeping a data log of record changes so that when the question of what is database auditing is raised, it’s this aspect that generally users want to know more about.

To create your own auditing tool requires the following items and skills in an Access database:

• A plan of what it is you wish to track
• How flexible will this tool be applied to – generic enough?
• Which tables (and perhaps queries) will be affected
• Building user-friendly forms to manage auditing
• Knowing Access VBA code to automate and track changes
• Multiple user and records instances need some sort of user login tool too!

Deciding on a plan is the first step as you may only want to track a collection of tables and their records. Additionally, there are some queries that may also fall into this area of your auditing requirements but be aware that not all queries are updatable and therefore serve no benefit in being included in this collection.

The flexibility of your designs need to be considered too if you are still generally developing your database as new tables and supporting forms may need to be added to the auditing environment and manually coding any new object can be an overhead one could simply avoid by working on the plan first.

I personally recommend you have a set of Access forms to control and manage users, forms and some of their properties to assign or un-assign objects to the auditing system as to avoid any user (albeit a developer, power user or end-user) gaining access to the back-door of the database design area where they can cause more harm than good.

Using generic forms to control other forms and the bound tables means knowing some VBA code too (Access Macros will not be flexible enough). The code would need to be scoped, trap for errors and be modular based so that future development will be a breeze but this can be a challenge for general Access developers/power users.

Here’s a snippet of VBA code that I created to save form objects as members of the auditing trail so that you can get an idea of the level of VBA code you may need to master:

This is not to impress you but to highlight the careful planning of code too and that it needs to handle more than just the task it is intended for.

You really want to use Access forms to allow users to make the data changes which are bound tables and not work directly in a table which will allow for better control and security of how your data should be captured and monitored.

In my example, I created a set of user forms to view and drill-down to see more detail for a record’s value and their changes keeping a history and again having extra functionality (by way for forms) will be required to make this overall very flexible – see example screen below

In order to keep track of users, your will need to have a mechanism to ask the user to capture their name perhaps as a valid login form otherwise it may well diminish the reason for having an audit trail in the first place.

There you have it, what is database auditing can be in many forms but when handling records in your database, we really want to keep an audit trail of data and therefore can be no argument as to who, what and when data had been changed!

To make life really easy for yourself and is a great offer too, I suggest you consider my new auditing tool utility that is released today It comes with the usual quality and guarantee assurance – check it out!

Tags: access auditing, how to create an access database, MS Access, tracking records in your database, using access vba, what is database auditing