Chuck Lowe
asked on
Audit Access Table
Auditing! Never an easy thing to do. I need to report changes made by Access queries on two access tables. Unless anyone has other suggestions I'd like the Audit tables to be separate for each table. One for the Policy and one for the Clients:
PolicyID or Client ID (Unique key on the tables that are updated)
Field_Name
Original_Value
New_Value
Date_Modified
New records do not need to be reported. Modified records that have most but not all columns that changed need to be captured. And only if the Policy is of a certain status (i.e. there is a record on another table with the PolicyID as Status = "IN PROGRESS" on that other table) does it need to have the changes captured for auditing. This is strictly for the tables that are updated using an update query. Also if the Policy or Client is deleted and the Policy was that certain status I need to have that reported as well. There are no changes via forms so Allen Browne's procedure (http://allenbrowne.com/AppAudit.html) while great is not useable here. Anyone have any ideas or suggestions? Any links or discussion groups?
Thanks.
PolicyID or Client ID (Unique key on the tables that are updated)
Field_Name
Original_Value
New_Value
Date_Modified
New records do not need to be reported. Modified records that have most but not all columns that changed need to be captured. And only if the Policy is of a certain status (i.e. there is a record on another table with the PolicyID as Status = "IN PROGRESS" on that other table) does it need to have the changes captured for auditing. This is strictly for the tables that are updated using an update query. Also if the Policy or Client is deleted and the Policy was that certain status I need to have that reported as well. There are no changes via forms so Allen Browne's procedure (http://allenbrowne.com/AppAudit.html) while great is not useable here. Anyone have any ideas or suggestions? Any links or discussion groups?
Thanks.
BTW, personally I'd use VBA. Far more flexible.
Jim.
Jim.
Here's a link as to how it's done:
https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/
Jim.
https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/
Jim.
ASKER
@Jim
Thanks. That's exactly what I was looking for. And the explanation is an easy step by step to follow.
Thanks. That's exactly what I was looking for. And the explanation is an easy step by step to follow.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Jim.
Thanks. I did note that. Fortunately it won't be an issue for me.
I will code it over the week and let you know the results. But it seems there should be no issues.
Thanks. I did note that. Fortunately it won't be an issue for me.
I will code it over the week and let you know the results. But it seems there should be no issues.
ASKER
@Jim,
Thanks. This is the solution I needed. There were a few gotchas but I was able to get it to work. I'm currently using it for After Update. I will use it also for After Delete. The basic capturing of changes is easy to code.
I will add some logic for mine where if the AuditID field is blank (no audit started on the plan) We do not care if the Plan is updated or deleted. It's only when a Plan is in the process of Auditing that a change to certail data could affect the work being done on the plan.
Thanks Again.
-Chuck
Thanks. This is the solution I needed. There were a few gotchas but I was able to get it to work. I'm currently using it for After Update. I will use it also for After Delete. The basic capturing of changes is easy to code.
I will add some logic for mine where if the AuditID field is blank (no audit started on the plan) We do not care if the Plan is updated or deleted. It's only when a Plan is in the process of Auditing that a change to certail data could affect the work being done on the plan.
Thanks Again.
-Chuck
<<Anyone have any ideas or suggestions? >>
Use table level data macro's:
https://support.office.com/en-us/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200
Other option: do it in VBA code unless your using SQL Server as a BE, in which case you can just use triggers in SQL.
Jim.