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

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


"Paul B." <(E-Mail Removed)> wrote in message
news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> 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
>

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Paul B." <(E-Mail Removed)> wrote in message
news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> 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
>

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." wrote:

> 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
>

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


"Douglas J. Steele" wrote:

> Take a look at what Allen Browne has at
> http://members.iinet.net.au/~allenbrowne/AppAudit.html
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Paul B." <(E-Mail Removed)> wrote in message
> news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> > 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
> >
>
>
>

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." wrote:

> 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
>

Thanks Trish....


"Trish Sutter" wrote:

> 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." wrote:
>
> > 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
> >

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" wrote:

> Take a look at what Allen Browne has at
> http://members.iinet.net.au/~allenbrowne/AppAudit.html
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Paul B." <(E-Mail Removed)> wrote in message
> news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> > 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
> >
>
>
>

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))


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Belinda" <(E-Mail Removed)> wrote in message
news:14023775-70E0-468E-990D-(E-Mail Removed)...
> 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" wrote:
>
> > Take a look at what Allen Browne has at
> > http://members.iinet.net.au/~allenbrowne/AppAudit.html
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> >
> > "Paul B." <(E-Mail Removed)> wrote in message
> > news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

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

"Douglas J. Steele" wrote:

> 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))
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Belinda" <(E-Mail Removed)> wrote in message
> news:14023775-70E0-468E-990D-(E-Mail Removed)...
> > 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" wrote:
> >
> > > Take a look at what Allen Browne has at
> > > http://members.iinet.net.au/~allenbrowne/AppAudit.html
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > >
> > > "Paul B." <(E-Mail Removed)> wrote in message
> > > news:9087EA6B-A6B1-4386-A339-(E-Mail Removed)...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>