Microsoft Access: Applications and Utilities

provided by Allen Browne, allenbrowne.com. Updated May 2007.

Creating an Audit Log

Access cannot log changes to your data at the record level. This article describes one solution for logging inserts, deletions, and edits. The typical multi-user situation is supported, and the code can be called from different forms open at the same time. Limitations of this solution include:

• each table to be audited must have an AutoNumber primary key;
• your interface must limit users to manipulating data only through forms;
• any cascading updates or deletes will not be logged;
• replica databases are not supported;
• the Confirmation options must be turned on for this to work. (Tools | Options | Edit/Find | Confirm in Access 95 - 2003; in Access 2007, Office Button | Access Options | Advanced | Confirm.)
• Does not work with Multi-Valued Fields (Access 2007.)

This approach uses a temporary table to log the proposed changes in the form's Delete and BeforeUpdate events. It then moves the proposed change tothe true audit table when the operation is assured in AfterDelConfirm/AfterUpdate. The code consists of four functions, called by the four form events:

• AuditDelBegin(), called by the Delete event;
• AuditDelEnd(), called by the AfterDelConfirm event;
• AuditEditBegin(), called by the BeforeUpdate event;
• AuditEditEnd(), called by the AfterUpdate event.

The end result is a copy of the record in the audit log, stamped with username, date and time, and type of operation (deletion, insertion, or edit). For deletions, the copy represents the data at the point it was deleted. For new inserts, the log contains the new entry. For edits, two copies are written: one marked "EditFrom" represents the record as it was before the update, and the other marked "EditTo" as is was after. In this way the log always contains redundant data. Combined with the fact that the audit log's AutoNumber should always be sequential, this makes any human tampering with the log more obvious.

Although the code does nothing more than execute a few action queries, you need a good grasp of the Access form events to follow how it works.

Debugging hint: Until you have the code working, remove the single quote at the start of the 2nd line in each procedure. When it fails, you will be able to see which line has not completed. For example, if you have not designed the tables exactly right, the SQL statement will fail.

Pitfalls in the Process

Logging Deletions

It is possible for a user to select multiple records for deletion in Continuous or Datasheet view. Access begins a transaction, removes the records, asks the user for confirmation, then commits or rolls back the transaction. The form's Delete event occurs for each record being deleted. At this point you have access to the data, but no idea whether the deletion will be committed. When the form's BeforeDelConfirm event occurs, you no longer have access to the data. The form's AfterDelConfirm event occurs even if the user cancels, with the Status argument indicating whether the deletion was committed or cancelled.

In the form's Delete event, the code below writes a copy of the record to the temp table. In the form's AfterDelConfirm event, these records are copied into the true audit table only if the Status argument provided by Access indicates that the deletion proceeded. The copies in the temp table are then removed.

Logging Edits

We use the form's BeforeUpdate and AfterUpdate events for logging edits. There is no guarantee the update will be committed when BeforeUpdate occurs, but the old values are no longer available in AfterUpdate. The code therefore uses BeforeUpdate to record the old values in the temp table, marked "EditFrom". AfterUpdate copies that record to the audit log along with the new values marked "EditTo", and then clears the temp table.

However, if the update fails after the BeforeUpdate code has executed, the AfterUpdate event does not occur. It is therefore possible for the temp table to contain spurious records representing failed edits. Fortunately, a user can edit only a single record in any one form at one time. The code therefore deletes any records in the temp table prior to writing the "EditFrom" copy in BeforeUpdate. If the database is distributed (front-end interface, back-end data), the temp table must be local to the workstation, so the user does not delete other workstations' valid entries in the temp table. Realistically, the time delay between BeforeUpdate and AfterUpdate is minimal, but the local temp table is the safe choice.

If you have other validation code in the form's BeforeUpdate, it makes sense to run that code first, and call AuditEditBegin() only if you do not cancel the update.

Logging Inserts

BeforeUpdate and AfterUpdate still occur when the user saves a new entry. In this case, we suppress generation of an "EditFrom" entry (since there are no old values), and mark the log entry as "Insert" instead of "EditTo" in the AfterUpdate event. To do this, the form must pass an argument to our code indicating whether this was a new record. That's easy in BeforeUpdate, but by the time AfterUpdate fires, the form's NewRecord property will always be false. The form needs a module level variable set to the value of Me.NewRecord in BeforeUpdate, so as to provide that value again in AfterUpdate.

Create the Four Functions

To create a general module with its functions:

1. Select the Modules tab in the Database Window/Nav Pane. Click New. Access opens a new code window.
2. Copy the code in this link, and paste it into the module.
3. Either remove the four references to function LogError(), or set up error logging as described in Error Handling.
4. Choose Compile from the Debug menu. Fix any errors Access highlights.
5. Save the module with a name such as ajbAudit.

Create the Temporary Table and Audit Log Table

For each table you wish to audit, you will create two more tables, as follows. If your database is split into two mdb files, the table created at step 2 must be in the code mdb (workstation file), and the table at step 5 in the data mdb (server file.)

1. In the Database window/Nav Pane, select the table to be audited.
2. Copy (Ctrl+C) and paste (Ctrl+V). Choose the "Structure Only" option so the data is not copied. Supply a name for the temp table: for example, if the table to be audited it named tblInvoice, use the name audTmpInvoice.
3. Open the temp table in Design view. Remove all indexes (including the Primary Key), but do not delete the fields. Change the AutoNumber field type to a Number, Long Integer. Remove any Validation rules from the table and all its fields. Make sure the Required property is set to No for all fields. Save. (Note: the temp table has no primary key.)
4. Still in Design view of the temp table, insert these three new fields at the top of the table (i.e. before other fields):
   

   Field Name	Field Type	Size
   audType	Text	8
   audDate	Date/Time
   audUser	Text	40

5. After saving the changes to the temp table and closing it, select it in the Database window/Nav Pane. Copy (Ctrl+C) and paste (Ctrl+V), supplying a name for the true audit log table. For example, if the table to be audited is named tblInvoice, use the name audInvoice.
6. Open the audit table in design view. Insert a new AutoNumber field, and mark it as Primary Key. The field name audID is suggested.
7. Repeat steps 1 to 6 for any other tables you wish to audit.

Enter the Form Events

You are now ready to enter 6 lines of code for your form. Replace the names in the example with the names of the tables and fields of your database, like this:

Where you see	Use
tblInvoice	the name of the table to be audited
audTmpInvoice	the name of your temp table
audInvoice	the name of your audit log table
InvoiceID	the primary key of the table being audited

Steps:

1. In the Database window/Nav Pane, select the form used to enter/edit/delete data. Open the code window (View | Code in Access 95 - 2003; Design | Tools | Code in Access 2007.) Access opens the form in Design View, and displays the code window. On a fresh line in the General Declarations section, add the line:
      Dim bWasNewRecord As Boolean
2. Select the form. In the form's properties, locate OnDelete under the Event tab. Choose [Event Procedure], and click the build button (...). Access opens the code window. Beneath any validation code you have there, enter a line like this:
      Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID,0))
3. In the form's properties, locate AfterDelConfirm. Choose [Event Procedure], and click the build button. In the code window enter a line like this:
      Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
4. Locate the form's BeforeUpdate property. Choose [Event Procedure]. Click the Build button. Enter these two lines:
      bWasNewRecord = Me.NewRecord
      Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID, 0), bWasNewRecord)
5. Locate the form's AfterUpdate property. Choose [Event Procedure]. Click the Build button. Enter this line:
       Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Nz(Me!InvoiceID, 0), bWasNewRecord)
6. Save the form.
7. Repeat steps 1 to 6 for other forms as needed.
8. Once you have it all working, activate the error handling by removing the single quote from the startof line 2 of each of the five functions, i.e.:
       'On Error GoTo ...

Each edit, insert, or delete will now be logged.