L
leadhead
Guest
is there a way to log changes to records in a table? I was told that the newer versions of Access could turn on some sort of logging wherein a record was kept of when records were changed or added. Thoughts?
Do a search on "audit trail"
ghudson has provided a great sample:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=44231&highlight=audit+trail
ghudson has provided a great sample:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=44231&highlight=audit+trail
- Local time
- Today, 03:31
- Joined
- Feb 28, 2001
- Messages
- 30,567
I'm not disagreeing with the other posters but I think you need to know a bit more about the problem in order to keep everything in context.
You must recognize that Access is really a SMALL BUSINESS Office solution. It is not a fully-featured database, despite the fact that it is so darned good at what it does. In the small business model, you don't get big-boy features like audit trails and full security logging and extremely granular security. You have to "roll your own" solution for anything exotic.
The key to audit logging is EVENTS. For most cases, the only events over which you have any control will be in the context of a form. Therefore, you will have to concentrate your efforts there. GHudson's solution is based on that concept.
In general, you trap any event that would close a form, step to another record, or commit the current record in place. All of these have in common that an update is about to occur, or could occur. Before you allow the implied update to occur, you scan the form to see what, if anything, would be changed by the update. Then you write change-detail records in a separate audit table to record the changes, and finally you allow the changes to occur.
Now, a couple of wrinkles.
First, you can never let your general user population see the database window because that means they could just open a table and BANG ZOOM do anything they want. Since no events apply to tables open in datasheet view, there is no chance of trapping anything. And suddenly you have unaudited changes.
Second, ditto to queries.
Third, ditto to macros.
Fourth, you have a chance of working your way around the problem for bulk-mode operations. But you have to build a form to control that operation. Then build a button control on the form to trigger the bulk-mode operation. Then add code underneath the button to do the audit logging for you, built-in with respect to your bulk-mode operation. Which usually means lots of VBA code and direct recordset manipulation.
Fifth, if you are going to do audit logging, you darned well have to implement some form of security, probably like WorkGroup security, to assure that you can tell one user from another. Because if you can trap the change but can't tell who made it, you have no chance to do something remedial like take the culprit down to the sub-basement for a fingers-and-toes barbecue.
Sixth, you need to recognize that audit logs get BIG over time, so you need to assure yourself of a way to flush your audits either to another database or to a usable text file to act as your permanent long-term audit log. More code or at least an export operation to a text file.
Now go look at what GHudson did with the above comments in mind.
You must recognize that Access is really a SMALL BUSINESS Office solution. It is not a fully-featured database, despite the fact that it is so darned good at what it does. In the small business model, you don't get big-boy features like audit trails and full security logging and extremely granular security. You have to "roll your own" solution for anything exotic.
The key to audit logging is EVENTS. For most cases, the only events over which you have any control will be in the context of a form. Therefore, you will have to concentrate your efforts there. GHudson's solution is based on that concept.
In general, you trap any event that would close a form, step to another record, or commit the current record in place. All of these have in common that an update is about to occur, or could occur. Before you allow the implied update to occur, you scan the form to see what, if anything, would be changed by the update. Then you write change-detail records in a separate audit table to record the changes, and finally you allow the changes to occur.
Now, a couple of wrinkles.
First, you can never let your general user population see the database window because that means they could just open a table and BANG ZOOM do anything they want. Since no events apply to tables open in datasheet view, there is no chance of trapping anything. And suddenly you have unaudited changes.
Second, ditto to queries.
Third, ditto to macros.
Fourth, you have a chance of working your way around the problem for bulk-mode operations. But you have to build a form to control that operation. Then build a button control on the form to trigger the bulk-mode operation. Then add code underneath the button to do the audit logging for you, built-in with respect to your bulk-mode operation. Which usually means lots of VBA code and direct recordset manipulation.
Fifth, if you are going to do audit logging, you darned well have to implement some form of security, probably like WorkGroup security, to assure that you can tell one user from another. Because if you can trap the change but can't tell who made it, you have no chance to do something remedial like take the culprit down to the sub-basement for a fingers-and-toes barbecue.
Sixth, you need to recognize that audit logs get BIG over time, so you need to assure yourself of a way to flush your audits either to another database or to a usable text file to act as your permanent long-term audit log. More code or at least an export operation to a text file.
Now go look at what GHudson did with the above comments in mind.
Similar threads
- Deleted Bruce 182381
- Queries
