Detect changes in a table/query

Topic

New Reply

I noticed there’s events attached to onChange and onDirty and i was wondering if it’s possible to scan a table to detect all rows where one or more fields have either changed or become marked as Dirty? i am trying to come up with a batch operation that kicks in when the user is done making edits, and i thought, if it’s possible to detect a field changes status that would be a good way to find all edits.

Hope that makes sense…

TIA!

Reply | Quote

Replies

On Change and On Dirty are events of several controls such as the text box. They are not properties of a record.

If you want to keep track of records being changed, you can add a date/time field LastModified to the table, and set its value in the Before Update event of the form(s) used to enter/edit data in the table:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.LastModified = Now
End Sub

Access does not have triggers at the table level. SQL Server does, though, but you have to set them in SQL Server – Access can’t get at them.

Reply | Quote

Thanks – I’ll think about doing the time stamp column. what i want is a quick way to automatically flag all rows where some changes was made.

Reply | Quote

Also see Allen Browne – Creating an Audit Log.

Reply | Quote

Also see Allen Browne – Creating an Audit Log.

interesting article on audit logging. not sure if i’ll use it but nice to know about the option.

Reply | Quote

As Allen Browne notes, there are a number of limitations to the approach he has suggested, and he also notes that Access 2010 will have macros that operate at the record level in tables. But that requires that you use ACCDB format databases. And frankly SQL Server triggers are much more powerful in my opinion, even if they are a pain to design and debug. We do archiving on selected tables in some of our applications, and it seems to work quite well. The advantage is that it works even if the user gets to the table to make changes or deletions.

Reply | Quote