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.
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well as a first comment, I can't see having separate tables. It's just a matter of adding a "table" field and you have a more generic solution that can be applied to anything.  In the future, efforts you make won't be wasted.

<<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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, personally I'd use VBA.  Far more flexible.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chuck LoweAuthor Commented:
@Jim
Thanks. That's exactly what I was looking for. And the explanation is an easy step by step to follow.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Scott's a fellow MVP and he laid it out nicely.

However of note, make sure you catch the very end there where he talks about places where  using data macro's for logging will not work (specific data types).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
@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.
0
Chuck LoweAuthor Commented:
@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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.