Here is a method to track edits in MS-Access. The goal is to record the "who what when where" of edits made in an Access database. Since Access does not have triggers like SQL Server, we will be working at the Form level.
NOTE - UPDATED 11/11/2007
added table name to tracker
To keep things readable within the blogger format, you'll need to visit three links that will display details of what is described here.
First, you will add three objects to your database:
1. A VBA module with Windows API calls to identify the user and PC name. This code has been tested with Windows 2000, XP and Vista 32-bit editions.
2. A VBA module to examine the current record & determine what has changed to the record
3. A table to store the results. For text fields, I've indicated the length in the Description. Here is a VBA module that will create the table for you.
After adding these objects, modify each form as follows:
1. Set the Tag property for the form = the name of the underlying table.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.
3. Add the Form_BeforeUpdate event and invoke the tracking code using:
4. If you are using subforms, you'll need to perform these three steps for each subform as well.
UPDATED 09/24/2008 - IMPORTANT NOTE
You can run a Sub in Access by using either
but you cannot use
that will result in a compile error.
NOTE - UPDATED 05/23/2010
tracking blank or null fields
There was an important note left out of the original post. In order to properly track when a field is blanked-out, the fields in the tracking table Field_OldValue and Field_NewValue should be set for AllowZeroLength = True. The updated "create table" code is here:
if you create the table manually you will need to set that property in the design view.