[AccessD] Audit Trails

Hi John

All the data? Two records to compare, that's all.

Some of the advantages of this method:

1. Zero "special code". No looping through controls. Add auditing to any table with no fuzz.
2. No doubt if "all" changes are recorded. All fields are present.
3. No trouble with user canceling. If U or D is canceled, no audit entry (again at zero code).
4. Access to the original table(s) is not needed. All info is hold in the audit records.

/gustav

>>> jwcolby at colbyconsulting.com 19-07-2009 14:28 >>>
That is certainly one way to do it.  OTOH, what changed?  I have tables with 60 or more fields.  One 
field changed.  Why save all of that and then have to go looking through all the data to discover 
what changed?

Not the way I would choose to do it.

John W. Colby
www.ColbyConsulting.com 


Gustav Brock wrote:
> 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).