Dave Denoy
asked on
audit trail for Access 2016 database changes
Is there a way for me to track which user makes a change to a record in an Access database? For example, if a field is changed from one selection to another, how would I record the user and the timestamp when the change is made? We don't sign into the database so there's no prior authentication before using it (access comes via active directory permissions). We are on Office365 subscriptions so the apps are integrated with our accounts but I don't see anywhere I can capture that info.
ASKER
Hmmm... looking at the audit log method, it states that "Access cannot log changes to your data at the record level". Is this still true of Access 2016? It looks like the only things recorded are a deletion, an insertion or an edit. I'm guessing that means that I will only show that an edit was made by a user but not exactly what that change was (e.g., it will only tell me that user x changed a record but not specifically what that change was. We are trying to identify who makes a change to one of the fields in one of the several tables tied to our data entry form.
You have to read further. Access can't do the logging so you are using Allen's code to do it for you.
No, it is not entirely true of Access 2016, you could learn to use data macros (available since A2010), although they are complicated and not well documented.
If you read the article completely, you will see that the initial code which fires in the BeforeUpdate and BeforeDelete events only stores the ID of the record, the date/time and the person, in a temporary table. Then, in the AfterUpdate and AfterDeleteConfirm events the entire record is written to the audit table.
Another way I've seen discussed but never tried is to use the BeforeUpdate event to check for the Value and OldValue properties of each field in the forms current recordset to determine whether those values have changed, and only write those changes to the audit log which would have fields like:
TableName - would not need this if only auditing a single table
Action: Insert, Edit, Delete
ActionBy: person
ActionDT: dateTimeStamp
FieldName:
FieldValue: I would make this a string as it would hold any data type
I've never really thought this through to completion, but this might give you an idea.
If you read the article completely, you will see that the initial code which fires in the BeforeUpdate and BeforeDelete events only stores the ID of the record, the date/time and the person, in a temporary table. Then, in the AfterUpdate and AfterDeleteConfirm events the entire record is written to the audit table.
Another way I've seen discussed but never tried is to use the BeforeUpdate event to check for the Value and OldValue properties of each field in the forms current recordset to determine whether those values have changed, and only write those changes to the audit log which would have fields like:
TableName - would not need this if only auditing a single table
Action: Insert, Edit, Delete
ActionBy: person
ActionDT: dateTimeStamp
FieldName:
FieldValue: I would make this a string as it would hold any data type
I've never really thought this through to completion, but this might give you an idea.
ASKER
not much of a VB guy but I'll give it a try. Does this require a user to sign into the app or will it take the user's windows login?
Dave,
Take a look at the attached database, I knocked together this afternoon. It is rudimentary and will only work for records which are created, edited, or deleted from within a form; but should meet 95% of peoples needs. This uses the technique I described above to just save the changes to the audit log, not entire records as Allen does. The neat thing about this technique is that if you know the associated ID value, you can run the query (qry_Audit_Log_XTab) and actually see the Insert, any changes, where you only see the values which changed, and the Deletion record and very easily see where the changes were.
Open form frm_Audit_Test_Continuous make a couple of entries, then go back and edit a couple of items, then delete a record or two.
Then run qry_Audit_Log_XTAB, which is sorted by the table being edited, the ID value (PK of that table), and then by the date/time stamp of the insert/edit/delete DESC, so that the most recent change for a given ID will be at the top.
Another advantage of this technique is that you don't need a whole lot of audit tables, two tables (tbl_Audit_Log and tbl_Audit_Log_Temp) will handle all of your tables. You would need to add some code in here to handle cases where the field type is a memo (I've saved all values as Short Text), and there are probably some other field data types OLE, MultiValue, Attachment for which this technique might not work).
Dale
Audit-Log.accdb
Take a look at the attached database, I knocked together this afternoon. It is rudimentary and will only work for records which are created, edited, or deleted from within a form; but should meet 95% of peoples needs. This uses the technique I described above to just save the changes to the audit log, not entire records as Allen does. The neat thing about this technique is that if you know the associated ID value, you can run the query (qry_Audit_Log_XTab) and actually see the Insert, any changes, where you only see the values which changed, and the Deletion record and very easily see where the changes were.
Open form frm_Audit_Test_Continuous make a couple of entries, then go back and edit a couple of items, then delete a record or two.
Then run qry_Audit_Log_XTAB, which is sorted by the table being edited, the ID value (PK of that table), and then by the date/time stamp of the insert/edit/delete DESC, so that the most recent change for a given ID will be at the top.
Another advantage of this technique is that you don't need a whole lot of audit tables, two tables (tbl_Audit_Log and tbl_Audit_Log_Temp) will handle all of your tables. You would need to add some code in here to handle cases where the field type is a memo (I've saved all values as Short Text), and there are probably some other field data types OLE, MultiValue, Attachment for which this technique might not work).
Dale
Audit-Log.accdb
Dave,
After playing around a bit, and posting the database in my previous response, I actually wrote an article, which discusses the process used in a little more depth.
Dale
After playing around a bit, and posting the database in my previous response, I actually wrote an article, which discusses the process used in a little more depth.
Dale
Hope this help you: https://stackoverflow.com/questions/29617067/audit-trail-in-access-2010-database
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dale,
Thank you so much for the effort you put into answering my question. I haven't had a chance yet to play with it but I will in the next couple of days. Your article is very well written!
Thanks!
Dave
Thank you so much for the effort you put into answering my question. I haven't had a chance yet to play with it but I will in the next couple of days. Your article is very well written!
Thanks!
Dave
ASKER
Excellent solution and way above and beyond my expectations!
Glad to help, Dave. I've been meaning to look into this for some time, you just gave me the push I needed.
Dale
Dale
An audit log is a bit more complicated, but can be accomplished. Check out this link for one method to implement an audit log