Difficult one :) - Logging changes in my database made

[ant1983]

Hi,

I have a database where different users would be able to change the value of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions are:

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

 

[Allen Browne]

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

 

[Jeff Boyce]

As Allen points out, you can build a audit function into your Access
database.

Another approach would be to use a more robust backend (e.g., SQL-Server),
which provides this kind of auditing trail.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ant1983]

Hi Allen,

I followed your steps exactly as you pointed out but am getting an error
when i close my form (after i have made the change)

The error reads:

"Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate
Line...

 

[John W. Vinson]

"Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate
Line...

That suggests that you have two instances of

Sub AuditEditBegin

or two labels

AuditEditBegin:

somewhere in your code. Use the binoculars to search for them. Edit the code
so that there isn't an ambiguous subroutine name or label and you should be
fine.

 

[joseph]

Allen,

I tried this fix with my own db and while performing the debug/compile
function I get an error "user defined type not defined" and it highlights the
section of code that reads:

Dim db As DAO.database ' Current database
Dim sSQL As String ' Append query.

is DAO.database supposed to be where I put the name of my database? If so, I
tried that and my database file name has an apostrophe in it ... is that
causing the problem and should I rename my db so that isn't an issue?

Thank you,

Joseph

 

[Roger Carlson]

More than likely, you don't have a reference to DAO set in your database.
You do that by going to Tools > References and scroll down to Microsoft DAO
3.6 Object Library. Check the box and click OK.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[LindaBee]

Hi Allen
I coppied your code and followed instructions on creating an audit trail
when Itry to run it I get the error message

"Compile error Su or Function not defined"
When I clcik ok it is taking me to call AuditEnd in the BeforeUpdate Sub

What am I doing wrong

Linda