Audit Trail

[Tony Williams]

Does anyone know of an Access add in or routine that would work as an audit
trail? I want to be able to see which fields have been amended and when and
what the changed data was originally.
Thanks
Tony

 

[(PeteCresswell)]

Per Tony Williams:

Does anyone know of an Access add in or routine that would work as an audit
trail? I want to be able to see which fields have been amended and when and
what the changed data was originally.

I've done it, but it wasn't trivial. Maybe greater minds than mine have come
with something more portable/easier to implement.

Before the user saves a record, I had to determine which fields were altered
(and not just entered/exited or over typed...) and then concoct something like a
"tblAudit" record and write it.

The approach I took was that when a user went into "Edit" mode on the screen, I
squirreled away a copy of all editable fields. Then, when the user clicked
"Save", I did a field-by-field comparison - writing an audit rec for each diff.

For portability, I chose to do recording on a field basis - one audit table
record per field changed and store the table name/field name/English-language
field name in the table.

 

[Douglas J. Steele]

Take a look at what Allen Browne has at
http://www.allenbrowne.com/AppAudit.html

 

[Tony Williams]

Thanks guys. Douglas I'm going to try Allen Browne's solution, thanks for
that
Tony

 

[(PeteCresswell)]

Per Tony Williams:

Thanks guys. Douglas I'm going to try Allen Browne's solution, thanks for
that
Tony

Maybe I didn't read it carefully enough, but Allen Browne's solution seems like
it only records whole records and does not tell which fields have been amended
or before/after values.

OITOH, the MS solution earlier in the thread
(http://support.microsoft.com/?kbid=197592) seems to have more promise in the
field-granularity before/after area.

 

[Douglas J. Steele]

Per Tony Williams:

Maybe I didn't read it carefully enough, but Allen Browne's solution seems
like
it only records whole records and does not tell which fields have been
amended
or before/after values.

"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."

 

[Tony Williams]

Thanks again guys I'll try both solutions and hopefully post back if I'm
successful in a day or two.
Thanks again
Tony