Access DB Modification Tracking

  • Thread starter Thread starter C. Sharp
  • Start date Start date
C

C. Sharp

Is there anyway to tell when a record's data was modified
and what in that record was modified, i.e. First Name?

Kind of like a track changes feature?

I can never tell who changed what last and would love to
sort records by those that were changed on a specific
date.

Thanks!
 
Is there anyway to tell when a record's data was modified
and what in that record was modified, i.e. First Name?

Kind of like a track changes feature?

I can never tell who changed what last and would love to
sort records by those that were changed on a specific
date.
Click to expand...

No built-in way; you must do so yourself. To track every modification
to every field is pretty tedious, though it can be done; for starters,
you would need to ensure that users can ONLY update the data using the
Form that you provide (using Access security).

Then, on the AfterUpdate event of each and every control bound to a
field you want to track, you would need to open a recordset based on a
logging table, and append the control's Value and OldValue (showing
what they changed to, and from); their system ID from a GetUser()
function; and Now() into a date/time field. This table will get pretty
big pretty quickly!
 
C. Sharp said:
Is there anyway to tell when a record's data was modified
and what in that record was modified, i.e. First Name?
Click to expand...

There's a simple example at
ACC2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197592

Audit Trail - Log changes at the record level at:
http://users.bigpond.net.au/abrowne1/AppAudit.html
The article addresses edits, inserts, and deletes for a form and
subform.

Modules: Maintain a history of changes
http://www.mvps.org/access/modules/mdl0021.htm
The History Table routine is designed to write history records that
track the changes made to fields in one or more tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top