Psuedo Audit Trail

  • Thread starter Thread starter Crystal
  • Start date Start date
C

Crystal

I need to know the additions, deletions, and edits
performed on a single table in my database, every day.
This table is not used in conjunction with a form, so the
traditional method used to set up an audit trail won't
work.

I was thinking that if I made a copy of the table at a set
time during the day, then compared the two versions
(current and previous) I'd be able to identify the
differences.

Problem:
How to I compare, record by record, field by field, the
differences in the two tables?

Any suggestions?

Crystal
 
How is the table populated? Often this is done via DAO/ADODB, in which case
it's a simple matter to "mirror" the table. Basically, you build another,
identical table with a few exceptions:

1) You'll want to add an Autonumber field to the audit table, since a record
may be edited more than once.

2) You'll want to time- and user-stamp the record, so you'll want to add a
dteTimeStamp field (Default Value=Now()) and a strUser field, which you'll
fill in your code routine.

After doing this, you can simply compare the two using standard query
syntax.
 
Back
Top