Logging updates to database

Question

Is there an easy way to log any changes to the fields in my main table? I've seen a few videos on Youtube, but most of them are older versions of Access.

Answer 1

John,

See my sample database showing audit trail with data macros.

Data macros can handle changes whether initiated from query, form, vba or direct table manipulation.

Answer 2

You might find my blog on Audit Trail with data Macros helpful (see below for blog address.)

Answer 3

Is there an easy way to log any changes to the fields in my main table? I've seen a few videos on Youtube, but most of them are older versions of Access.

Hi John,

I use a very simple way to handle this problem.

I catch the value of the Control in the Enter event, and compare it with the value in the Exit event. If there is a difference, you can log it, including the "Enter"-values and "Exit"-values, and other specifics.

The problem with Access-forms is - in my opinion - that they are far too static. But I think It is relatively simple to make them more dynamically on this point (and on many other points).

Imb.

Answer 4

You might like to take a look at ChangedRecordDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes an option to open a 'Form to Copy Previous Data to Audit Table', which illustrates a methodology for doing this.  This differs from most audit trail solutions you'll come across in that it only logs actual changes to the values of data in the table, rather than merely logging updates.  An update can occur without any of the values at the column positions in a row being changed.

The method used is to temporarily store the values in one array before a row is edited, then in another row after it is edited, and to then compare the two arrays.  Values which have changed are identified in a subform by using conditional formatting to highlight those column positions.

Answer 5

Nothing in Access has changed which should have significant impact on most (or nearly all) logging procedures. Pick one that seems accessible to you and implement it. See how it works out. If you have trouble, you could ask specific questions about specific issues.

I will say that Data Macros are a very effective method of logging updates because they always fire when a value in a field in the table with Data Macros is updated, making it very difficult to bypass your logging protocol. Give preference, therefore, to methods using Data Macros.