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:
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:
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: