databasedev.co.uk - database solutions and downloads for microsoft access

Audit Trails in Microsoft Access

What is an audit trail? Well...in simple terms, it is a tracking system. Audit trails are extremely useful for administrators because they can view (remote) changes to the system that they control. In Access, implementing a tracking system, or even a small procedure to record data changes is very simple.

Depending on the need of the business and the environment in which the business operates, the tracking may be very different. For years now, Microsoft has had an article published about this sort of technique. The article can be found at: http://support.microsoft.com/kb/197592

The first thing to understand about tracking changes in an Access database is that the program continuously updates data definitions in the file as they happen. For instance, updating a form field will yield immediate results in the table, thus saving the form is not necessary. This is very different than Word or Excel, where the files must be saved to record data changes.

The difference lies with "data binding", which is a concept that basically says that interfacing controls like listboxes and textboxes are just upscale representations of the raw data in the table. Audit trails can only be made possible by this concept. Bound controls on forms are the key factor in creating a successful tracking system, and they can only exist on bound forms. So let's look at a bound form. How do you know it's bound to a table? By its Recordsource property:

Once the form has a source, controls can be bound to fields in that table or query. In the same form as the one pictured above, the textbox called "First Name" is now bound to the table field of the same name:

At this point, the BeforeUpdate and OldValue properties become important. The "BeforeUpdate" property of any given form can be triggered an infinite number of times. But the most common trigger is moving from one record to another. The only condition for the event is that one or more bound controls must have been modified on the form. Generally speaking, this event will occur if this condition is met, and a user does any one of the following:

• Moves to a new record.
• Saves the record, either by mouse or keyboard.
• Closes the form.

OldValue

This property only applies to form controls, and only those that can hold values, such as text boxes and combo boxes. This is the property used in an audit trail record.

So how do you put it all together? The simple answer is that it's up to you! The Microsoft Article mentioned above gives a good outline of the general principles that you can use. Some administrators track everything, and some don't track anything. Some record changes in every table, when that table is modified specifically. Others write records to a master table that can be easily exported and read in other programs. More often than not, the reason for the implementation of an audit system is either because internal policy requires it, or auditing firms request the information. Below is a small example of how to track changes to the Northwind database:

Consider the Customer List Form from Northwind:

The right-side menu lets us access all kinds of forms. Inventory, Orders, Customers, etc. The most effecient way to audit your database is to write a global function that can be called by any form. Using this method, we can use a combination of Microsoft's code and our own to record data changes on any form to an AuditTrail table:

Function WriteChanges()

Dim f As Form
Dim c As Control
Dim frm As String
Dim user As String
Dim sql As String
Dim changes As String
Dim db As DAO.Database

Set f = Screen.ActiveForm
Set db = CurrentDb

frm = Screen.ActiveForm.Name
user = Application.CurrentUser
changes = ""

   sql = "INSERT INTO AuditTrail " & _
         "([FormName], [User], [ChangesMade]) " & _
         "VALUES ('" & frm & "', '" & user & "', "

    For Each c In f.Controls

      Select Case c.ControlType
          Case acTextBox, acComboBox, acListBox, acOptionGroup
               If IsNull(c.OldValue) And Not IsNull(c.Value) Then
                  changes = changes & _
                  c.Name & "--" & "BLANK" & "--" & c.Value & _
                  vbCrLf
               ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & "BLANK" & _
                  vbCrLf
               ElseIf c.Value <> c.OldValue Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & c.Value & _
                  vbCrLf
               End If
      End Select

    Next c

   sql = sql & "'" & changes & "');"

db.Execute sql, dbFailOnError

Set f = Nothing
Set db = Nothing

End Function

On our customer's form in the "BeforeUpdate" event, we can write:

=WriteChanges()

Now, everytime we update a record the administrator will have one more thing to look at. If we go in and add some email addresses to our customers' records, we see the result in the audit table immediately: