[AccessD] Audit Trails

Hi Rocky

1. Yes.
2. Yes, for updates and deletes. One line of code using DAO or ADO.
2. No, for inserts and reads.

/gustav

>>> rockysmolin at bchacc.com 19-07-2009 14:49 >>>
Gustav:

Storage space won't be an issue.  However, this implies an audit table for
each table with userID and timestamp, yes?  It would seem the reporting
would be a little more tricky because you'd have to go back to the previous
audit trail record for a table to find the differences, no?

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Sunday, July 19, 2009 12:34 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Audit Trails

When you consider that last week I bought 1.5Tb HD for £110 sterling from PC
World. Plugs straight into my usb port and comes up as 1.3Tb formatted.

I can make copies to my hearts content and never run out of space.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 19 July 2009 07:58
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Audit Trails

Hi Stuart and Rocky

Why all this trouble? Today disk space cost is very low so why not just create a copy of the record before any change? 
Just follow CRUD:

C. At OnInsert:
  Append a copy of the new record to the audit table.

R. At OnCurrent (if needed which seldom, though sometimes, is the case):
  Append a copy of the current record to the audit table.

U1. At OnCurrent:
  Create a temporary copy of the current record.
U2. At OnAfterUpdate:
  Append the temporary copy to the audit table.
  Append a copy of the updated (now current) record to the audit table.

D1. At OnCurrent:
  Create a temporary copy of the current record.
D2. At OnDelete:
  Append the temporary copy to the audit table.

This is for forms. If you have code that modifies tables, adjust the code to
include similar operations. Too much trouble, you may ask? True. Auditing is
trouble and use of resources.

The audit table is identical to the table to be audited with the addition of
a timestamp, a user id, and an operation code. This allows extensive and
fast searching which should be the key requirement to any auditing system
(what else?) like "who deleted records between then and now?" and "when were
changes made for customer id x?" or "which appends or deletes have been made
by user id n?". The result will - without access to the audited table -
contain _all_ info, not only a PK or a few changed fields.

When the client whines about potential disk space consumption, tell him that
auditing does cost resources including disk space. If this is a true issue -
if you may touch the physical limits of the database like the 2 GB limit of
JET - place the audit tables in another database - or databaseS like one for
each year or month.

/gustav


>>> stuart at lexacorp.com.pg 19-07-2009 01:24 >>>

I certainly wouldn't try to trigger an audit trail at the control level. The logical place is at the time you insert, update and delete a record.   

For the insert, you don't need to store any info about the record other than its PK.

For the delete, you need to store all fields for the record whether they are displayed on the deleting form or not.

In neither of these cases do you need to iterate through controls.  It's only the Edit audit that needs iterate through the fields exposed to controls on the editing form and record which ones have changed.

There is a lot to be said tor using three different audit tables for the three different cases.
(That's exactly the way I've done it in the past in SQL Server using triggers).