Track any changes made to a table

[cheetarah]

I need to find a way to track any changes made to the Access Database tables and the user that made the change.. I want to create a history table, but can't figure out how to do it.. HELP please..%-)

 

[rubbernilly]

There are no table events, so there is no way to really trap this... but...

If you are talking about the structural/architectural changes to the tables (rather than record/transactional changes), you would need a table that had a field for each of the fields of a table that you wanted to track or were afraid that a user might change. Add to that table a date and a time field to know when the snapshot of the table properties was taken, and add a field for the User.

Then, probably on a timer (from a form that you loaded as hidden at database startup) you step through the TableDefs collection and check the properties against the last entry in the table. If the current properties of any table are different from the most-recent, previously recorded set of values in the table, make a new record in the table and record the CurrentUser() into the table.

That will tell you whose scan revealed that a change had been made.

About the only other thhing you can do is if there is a change to record, expose the .LDB file for the database and examine who is connected to the database at that given moment. That way, you would have a list of potential users who could have been the ones to make the change...

I don't know that you're going to get much closer than this.

 

[Tigerlili3]

You can create an audit trail using information from the following site...

http://allenbrowne.com/AppAudit.html

 

[BSman]

For changes to the data in a table, if you create a copy of the table and add two columns, one for the userid and the second for the date/time of change, you could append a new record to it whenever a record was changed. Doing this would involve a LOT of overhead. You'd need to store the values in a record before changes are made, then after the changes are made examine the new data to be sure something is change. Then, if something has changed, write a copy of the new record to the history table along with the userid and date and time the change was made.

If you're tracking changes to table layouts, etc, then why not just add a table with the appropriate fields and let the developer enter the appropriate data?

Bob S.

 

[cheetarah]

Reason for my doing this, is I have a Database that 7 Newbie Users have to change data in, I've limited the fields they can change depending on the user, but they still manage to click in the wrong field (line) (especially when part numbers are the same) and then blame each other for the wrong change and no one knows what the original data was.. So I want to keep a history in a separate table, so that I can review changes to fix errors.., so it's not as easy as "add a table with the appropriate fields & let the developer enter the appropriate data" I wish it was, but as we're all in different areas of the building and updating depending on our job, I need this audit trail... I've tried some "Audit Trails" but they loop through ALL the controls on form, not just the ones that they're allowed to change and is giving me a hassle.

Thanks for help

 

[PHV]

they loop through ALL the controls on form
Can't you tweak the code playing with the Tag property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cheetarah]

tried, but haven't had any luck... trying other options now thanks

 

[MichaelRed]

per already noted, there is no way to track changes made direstly to the table (or a recordset based on a table). You can, however track all changes madr through BOUND forms. See faq181-291.





MichaelRed

 

[BSman]

If you set up the separate "history" table as I suggested (with only the reference/ID column plus the columns that you actually need the history for and the column for the userid and the date/time of the change), then you can set up an append SQL statement or query that you execute for the AfterUpdate and AfterInsert properties of the form. That way, every time a user either adds a new record or changes an existing record the changed data will be recorded. This method has the benefit of appending data only when changes/additions are made, but it would mean that the history table would contain a duplicate record of the current data.

Otherwise, you would use the before update property to save the data in the history file before it is updated.

Either way should provide the history you are looking for, although it will greatly expand the database. To reduce the growth of the existing database, you might create a second database for the history and link to the history table in that. That way, your production database would not slow down because of the large amount of history data that is saved, but you can always access both the history and current files when needed through links.

Bob S.