Audit Trail in Access

I'll go with Jim's response.  Access does not provide for field-level auditing in ANY sense of the concept, and trying to build a system for it will be time-consuming at best, homicidally frustrating at worst.  The best system I've seen goes something like this:

1) User opens a form to a specific record.  Access records that action in the audit table.
2) As the user changes field data, the form is populating a manually constructed recordset with each change.  The recordset includes fields for table name, field name, record id, old value, new value, timestamp, and user id.
3) If the user saves the record, the manual recordset is then appended to the audit table.  Otherwise, the audit table receives a record indicating the change was cancelled, and the manual recordset is discarded.

It was intensive to build, and required event code for EVERY CONTROL on every audited form...quite the project.  Unless this is going to be a permanent ongoing project, I would not even bother with it.  Even if it is a permanent ongoing project, I would still recommend switching to a db platform with the functionality to support what you need, like MSSQL.

<<I would still recommend switching to a db platform with the functionality to support what you need, like MSSQL.>>

OK then assuming we go the way you suggest and use Access as the front end and SQL back end what is your best suggestion for how to do this with that particular configuration?

Triggers!  MSSQL has that neat little functionality to support exactly what you're talking about.  A trigger is kind of like an event for an Access form, but it works on table changes instead.  You can identify triggers for all types of events in MSSQL, including changing data in a record.  Here's an excerpt from one of the "Overview" pages from the MSSQL Books On Line.  If you would like to download this, you can find it at:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

---------------------------------------------------------------------------------------------
Microsoft® SQL Server™ 2000 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. A trigger is invoked in response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

Triggers are useful in these ways:

Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.


Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.
Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.


Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.
---------------------------------------------------------------------------------------------

>>> Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.

I just wanted to specifically point out that particular sentence...

OK let me try this.  It seems I recall in the upsizing wizard it allows for uploading the table relationships to the SQL2000 server but these require unique indexes.  I'm thinking more like this first suggestion that whenever the user save or inserts a record may be the best place to simply copy the currently edited record plus a time and date stamp field.

The SQL upsize wizard actually asks about adding a datetime stamp field automatically.  I'm guessing this may be laying the groundwork for just such audit capability.

Thank you for the excellent information.  May not get back to award points this afternoon - too much sunshine outside to do this all afternoon but will be sure to award points by Monday worst case.

Yeah, upsizing can take you through some aggravation, but it can be well worth it if you need that advanced functionality.  You can be sure the wizard will do as good of a job as possible in transfering all your information, but there are certain things you'll still need to go back and 'clean-up', so to speak, relationships and keys being first in that list.

The only problem with the history table is database bloat, which may or may not be a concern for you.  Keep in mind that the maximum db size is roughly 2gb in Access...any larger and <poof>

Agreed.  All the more reason to upsize and run it on SQL plus SQL has the nice job scheduler agent which can make clearing old history to avoid the bloat something completely unattended.  No compacting.  No repairing.

Hi all, I have been following this intersting discussion. I just need to understand one thing. Are you guys saying that Informative, keeps using Access as front end and SQL server as back end? Thx

The issues of SQL Server vs. Access and tracking history are not directly related, it's just that any process which will likely generate a lot of records, like tracking history will, adds to the demands of the back-end database, which is the domain of Access upsinging to SQL.

There are many reasons to upsize to SQL, such as a need for greater security, 24x7 use, number of concurrent users/transactions, speed in executing large chunks of queries/code, etc.

Hi rouinet, i have a VB6 question and tried to email you but it didn't work!

You may want to take a second look at Routinet's email address, and apply some logic.  ;)

i know.....i was stupid!

Well, you can always ask a question, then post a link to it somewhere you know I'm watching.  :)

OK this works pretty well.  

Added user and date-time fields (hidden) to the form being edited as well as the table being edited (two new fields there)  Placed the code to slap the users ID and now() into those two new fields in the beforeupdate beforeinsert events.  Then dropped the code to replicate the current record into a history table in the afterupdate afterinsert.  Upsize the DB to MSSQL and create a simple Agent scheduled job to purge old records from history weekly keeping only the last couple of months of history to limit it's size.

Thanks for all the great input.