building an audit trail

[Paul B.]

Can someone give me an idea of how best to build an audit trail?

I have built my first two databases, mostly using exmaples and help from the
good people that answer questions here, and now that I have sort of an audit
trail, it appears to me that there must be a better way.

I have several forms that write to one or more tables. Call it a lack of
design forethought, however, now that I look at it, I am wondering if I could
have one table act as the audit trail, maybe tying the Row#(autonumber) and
table name to the data row in the audit trail table.

I am asking to see if I have the time or energy to modify my database before
putting it online for trials.

Cheers

 

[Rick B]

There are tons of posts out there on how to create audit trails and
timestamps. do a search and you will find plenty. Look in the tabledesign
group and the formscoding group for best results.

Rick B

 

[Douglas J. Steele]

Take a look at what Allen Browne has at
http://members.iinet.net.au/~allenbrowne/AppAudit.html

 

[Trish Sutter]

Yes you could create another table to journal, but here's a simple way:

KISS -- and Best Practice is the following:
1. create all rows with
CreateDate default (in the table definition) =
date()
UpdateDate On Update = Date()
DeleteDate On Update = Date()
Deleted

 

[Paul B.]

Sorry everyone, I should have thought more about this prior to posting....

It is not an audit trail I need. In fact what I have will work, just need to
organize my table design better next time.

BTW, I think I have read just about everything on Allen's site....BIG HELP,
excellent work Allen, thank you.

And Thanks again to everyone else.

Cheers

 

[Trish Sutter]

Sorry, I used the tab key and it posted on me.....
So,
DeleteDate On Delete = date()
NotDeletedRec Boolean Default = True, On Delete = False

Your on delete will actually be an on update, (or cancel the delete and
replace it with code that updates the Deletexxx tracking fields....etc...)
you have to programmatically enforce the delete as a logical rather than
physical delete. There are multiple ways of doing that as well. All
reports, forms, queries etc... should be based off of a new query (view) that
only includes "NotDeletedRec = True" . It processes faster by looking for a
True, rather than looking for a NOT TRUE. For speed try to avoid "NOT".

For userid, I love the ENVIRON$("USERNAME") default your CreateId to this
and do the same programatically with the UpdateID and DeleteID.

How you find this helpful,

Trish

 

[Paul B.]

Thanks Trish....

Sorry, I used the tab key and it posted on me.....
So,
DeleteDate On Delete = date()
NotDeletedRec Boolean Default = True, On Delete = False

Your on delete will actually be an on update, (or cancel the delete and
replace it with code that updates the Deletexxx tracking fields....etc...)
you have to programmatically enforce the delete as a logical rather than
physical delete. There are multiple ways of doing that as well. All
reports, forms, queries etc... should be based off of a new query (view) that
only includes "NotDeletedRec = True" . It processes faster by looking for a
True, rather than looking for a NOT TRUE. For speed try to avoid "NOT".

For userid, I love the ENVIRON$("USERNAME") default your CreateId to this
and do the same programatically with the UpdateID and DeleteID.

How you find this helpful,

Trish

 

[Belinda]

Allen's code end result is exactly what I need as well, except one that my
database does not meet the #1 condition, i.e., "each table to be audited must
have an AutoNumber primary key"

Is there a way around that?

 

[Douglas J. Steele]

You can make changes to his code. If, for example, your table has two fields
as its key (a text field and a numeric field), you can change AuditDelBegin
from

Function AuditDelBegin(sTable As String, sAudTmpTable As String, _
sKeyField As String, lngKeyValue As Long) As Boolean
Dim db As DAO.Database ' Current database
Dim sSQL As String ' Append query.

' Append record to the temp audit table.
Set db = DBEngine(0)(0)
sSQL = "INSERT INTO " & sAudTmpTable & _
" ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, " & _
"NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & _
sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError

End Function

to

Function AuditDelBegin(sTable As String, sAudTmpTable As String, _
sKeyField1 As String, sKeyValue1 As String,
sKeyField2 As String, lngKeyValue2 As Long) As Boolean
Dim db As DAO.Database ' Current database
Dim sSQL As String ' Append query.

' Append record to the temp audit table.
Set db = DBEngine(0)(0)
sSQL = "INSERT INTO " & sAudTmpTable & _
" ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, " & _
"NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & _
sKeyField1 & " = '" & sKeyValue1 & "' AND " & _
sTable & "." & sKeyField2 & " = " & lngKeyValue2 & ");"
db.Execute sSQL, dbFailOnError

End Function

and then change how you call it from

Call AuditDelBegin("tblInvoice", "audTmpInvoice", _
"InvoiceID", Nz(Me.InvoiceID,0))

to

Call AuditDelBegin("tblInvoice", "audTmpInvoice", _
"CustomerNm", Nz(Me.Customer,"")), _
"InvoiceID", Nz(Me.InvoiceID,0))

 

[Belinda]

Thank you Doug! I'm sooo green at coding that it'll probably take me a while
to read/understand (fingers crossed!)/customize for my own use.

I'll definitely get back to you with any comments/positive results.

Thanks again