Solved: Detect and Capture change in Access Table

[Trinidad]

How do I detect changes made to my Database Table data and print out any changes?

[Oorang]

:welcome:
Hi Trinidad,
This is a common question with no simple answer. To know what a value was changed from and what it was changed to, you need to store both values. This can create some tremendous storage demand. This is a matter of much study and discussion. The current line of thought lies in Temporal Database Design (http://en.wikipedia.org/wiki/Temporal_database) or 6NF (http://en.wikipedia.org/wiki/6NF). Sadly Access is ill suited to this design and while it can be done, it does add a lot of complexity to your project.

Generally speaking it is simpler to lock down your database so no one but the administrator can access the data via any other method then your forms. Then use the form's on current event to get the value before changes, and the afterupdate event to get the new value. When those two values are different, write them to a change log, and update yourself with the new values.

[Trinidad]

Thank you very much, Lets keep it open for another opinion

[matthewspatrick]

Oorang gave a very complete answer.

All that I would add is that enterprise class RDBMS, of which Access
is *not* a member, include support for triggers. Triggers are basically
a special class of stored procedures that are typically used to perform
specific tasks when data are added, deleted, or updated on specific
tables.

So, for example, in SQL Server I could use table triggers to enable
the audit logging that Oorang refers to above, and have this handled
in the data layer rather than the application layer.