Audit trail. Preference and why (1 Viewer)

[tucker61]

Last year I implemented Allen brownes audit trail into my database, by adding the audit trial field into my table. So for the last 12 months I have successfully captured any changes to by fields. ( I cannot see this original code I used but i added a field to my table instead of creating a new table to hold the changes .

I need to add audit trail to additional forms, so was thinking of moving away from the Allen Browne way of doing things to the Martin green way.

So, my question is.. which is the best way to capture any changes to data.?

Is it better to have 1 table to track all changes, or to have a audit trail field in all the tables?

Some if my fields I will need to audit will be unbound, so that is the next question...
Is it better to capture the values on before and after updates and insert into table?

Thank in advance..

G

Posted from Samsung s2

Sent from my SM-T715 using Tapatalk

 

[jdraw]

Be aware that Martin Green's audit (based on his sample and this thread) is identifying an incorrect record when recording the audited record. And we could not get it too work with subforms. See posts( 4 and 5) at https://www.access-programmers.co.uk/forums/showthread.php?t=283942&highlight=audit

I did post an example based on Martin's code, but added a new command to deal with DeleteConfirmation, and works with subforms and records the correct record. That sample deals with field details in the audit log -not just the record id.

However, depending on your version of Access, I would strongly recommend using table data macros and named data macros. See this link for info and ideas and post 14 for sample.

Good luck.

 

[tucker61]

Be aware that Martin Green's audit (based on his sample and this thread) is identifying an incorrect record when recording the audited record. And we could not get it too work with subforms. See posts( 4 and 5) at https://www.access-programmers.co.uk/forums/showthread.php?t=283942&highlight=audit

I did post an example based on Martin's code, but added a new command to deal with DeleteConfirmation, and works with subforms and records the correct record. That sample deals with field details in the audit log -not just the record id.

However, depending on your version of Access, I would strongly recommend using table data macros and named data macros. See this link for info and ideas and post 14 for sample.

Good luck.

Just read post 14, not even going to pretend that I understand it. Our db is created in access 2010,

I did see a post from yourself in one of the other threads when you managed to get it working for subforms.. I might have a go at adapting that tomorrow..

Sent from my SM-T715 using Tapatalk

 

[tucker61]

I have now updated to the new version of the code amended by Jdraw, which now looks at the subform.

One of my operators is having a error message when it reaches the code

 Set cnn = CurrentProject.Connection

and i get the error "Class not Registered"
I am not logging the new records, or the deletions. just the edits.

I have checked and he has Microsoft ActiveX Data objects 2.8 Library installed. Any ideas ?

Currently i have had to disable the code for this one user.

looking at the error code he is getting error 91, Object variable or with block variable not set.

 

[jdraw]

I just opened the audit log zip.(from https://www.access-programmers.co.uk/forums/showpost.php?p=1530693&postcount=43)

Here are the references I was using

and here is the updated log as of today.
I updated the Title and Hourly Rate of EmployeeID 1; then Deleted 1 of her
Cafeteria item records.

Here is the immediate window info for these actions:

Edit Employee: 1  13-Feb-2018 7:18:06 AM
FrmOrderSubform     Error 438 in frmEmployee being ignored -13-Feb-2018 7:18:06 AM
FrmOrderSubform     Error 438 in frmEmployee being ignored -13-Feb-2018 7:18:06 AM
frmOrderDetailSubSubForm Form_Delete(Cancel As Integer)  gRecID is 20
ORDERDetail Calling Audit to prepare to DELETE ID =20
frmOrderDetailSubSubForm Form_AfterDelConfirm(Status As Integer)
tmpAuditRec  was deleted 13-Feb-2018 7:27:05 AM
ORDERDetail Record physically deleted ID 20

I hope this helps.

NOTE: I did not update the zip file, only the extracted copy of the accdb
I had locally to test the references and the transactions.

 

[tucker61]

Thanks, we are using office 2013, could that be the difference ?

 

[jdraw]

It's possible, but I would move the Microsoft ActiveX Data Objects 2.8 Library higher up (use the up arrow). Just a guess????