Detect time of last change on a Microsoft Access database table

Question

Does anyone know of a way to detect when the last time a Microsoft Access table has been changed (inserted into or updated)? We used OLEDB via ADO COM to communicate with an access database programmatically and were looking for a way of detecting changes to specific tables. We don't need to know what those changes are, just that changes were made.

Answer 1

There isn't a way without "manually" writing to a column each time you access the table.

Answer 2

The only way to detect if data in the table has changed is to perform a query against the table.

You must add a column of type DATETIME to the table e.g. named LastUpdatedDate that indicates the last updated date/time of each row. Make it NOT NULL so that you will have to write an updated DATETIME value to that column for each INSERT or UPDATE. Also, set the column to have a default of DATE() for the current date stamp or NOW() for the current date/time stamp. Then add a Validation Rule or CHECK constraint e.g. CHECK (LastUpdatedDate = NOW()) to ensure the column is actually updated on each UPDATE and INSERT.

Finally, run a MAX(LastUpdatedDate) query and you will get what you need.

Answer 3

As others have indicated there is no way to track changes without coding it yourself.

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://allenbrowne.com/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.

Answer 4

You will need to implement a timestamp column in your table, and update the value during your data changes.