logging changes in a separate table

[verizon1]

I have a large database where data in a record may change
frequently but I want to track automaticly a log of all
changes as they occur. How can I build a table that
captures changes in a record and logs it in a table along
with the date and user that made those changes?

 

[Allen Browne]

This is not a simple process because Access does not provide triggers at the
engine level.

However, it is possible if all your changes are made through a form. For a
discussion and code to log edits, deletes and inserts, see:
Audit Trail - Log changes at the record level
at:
http://members.iinet.net.au/~allenbrowne/AppAudit.html

 

[John Vinson]

I have a large database where data in a record may change
frequently but I want to track automaticly a log of all
changes as they occur. How can I build a table that
captures changes in a record and logs it in a table along
with the date and user that made those changes?

You cannot do this *with a Table*. You can use a Form to update the
table, and use some VBA code in the Form's AfterUpdate event to open a
recordset based on the log table and add a record.

 

[Marc Noon]

Realistically,

You could create a new One to Many relationship for a table with the same
fields you are interested in, in addition to the users name. Then just set
the old values into your change table. If you decide later to delete some
of the old changes made, you can do so.

Theorhetically you can have as many changes as you want, and can see the
entire process without using a form, just a little sql processing.

original table <1> -------<Many> Change /Table

Regards,
Marc N