Creating An Audit Trail

I

imcphers

Hi,

I want to track the changes made to one table through a form to another
table. I want to include the field name and the new and old value. How do I
do this?

Thanks!
 
J

jquadsupport

I had the same need recently. Below is what I ended up doing. Basically it
just uses the .ActiveControl and .OldValue properties. It works like it
should, but one drawback is the logging routine needs to be called from the
"On AfterUpdate" event of the field being edited -- not that big of a deal
unless you're constantly adding/dropping fields from a form (if so, you may
be able to find a better place to call it).

Private Sub LogChange()

Dim ctl As Control
Dim strFieldName As String
Dim strOldValue As String

Set ctl = Me.ActiveControl

strFieldName = ctl.ControlSource
strOldValue = Nz(ctl.OldValue, "null")

Call LogDataChange(Me!Import_ID, "PHA", Me!participant_code, strFieldName,
strOldValue)

End Sub


Public Sub LogDataChange(lonImport As Long, strDataType, strKey As String,
strFieldName As String, strOldValue As String)

Dim strSQL As String

strSQL = "INSERT INTO cff_log_Data_Audit (Import_ID, DataType, UniqueKey,
Field_ID, OldValue, User_ID) " & _
"SELECT " & lonImport & ", '" & strDataType & "', '" & strKey & "', '" &
strFieldName & "', '" & strOldValue & "', '" & pubstrUser & "'"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub
 
R

RuralGuy

Here's one suggestion from MVP Allen Browne:
http://allenbrowne.com/AppAudit.html

Hi,

I want to track the changes made to one table through a form to another
table. I want to include the field name and the new and old value. How do I
do this?

Thanks!
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
D

Douglas J. Steele

Ray S. said:
This solution seems impractical. My database has hundreds of forms,
tables,
and users. Isn't there something like Oracle's "audit on"? My company is
on
the verge of dumping Access due to this.
Click to expand...

I'm unaware of any desktop DBMS that has audit capabilities built into it.

Comparing Access to Oracle isn't realistic. Oracle is an enterprise-level
DBMS (like SQL Server or DB2). Access is a development environment that
happens to come with a DBMS (two of them, in fact).

It's possible that you might be able to get some built-in audit capabilities
if you use MSDE, rather than Jet, for the DBMS.
 
R

Ray S.

Douglas J. Steele said:
I'm unaware of any desktop DBMS that has audit capabilities built into it.

Comparing Access to Oracle isn't realistic. Oracle is an enterprise-level
DBMS (like SQL Server or DB2). Access is a development environment that
happens to come with a DBMS (two of them, in fact).

It's possible that you might be able to get some built-in audit capabilities
if you use MSDE, rather than Jet, for the DBMS.
Click to expand...


You're right, of course. I work for a large enterprise that chose to develop
a sarbanes-oxley issues management and tracking database application. An
absolute requirement imposed from management and the audit personnel is that
we must be able to track and report on any and every single change of
information. There is now discussion as to whether we should have developed
from the start in Oracle because it is neither easy nor practical to do this
type of auditing in Access. You mention SQL Server. Can you point me in the
direction of an answer in SQL?
 
D

Douglas J. Steele

Ray S. said:
You're right, of course. I work for a large enterprise that chose to
develop
a sarbanes-oxley issues management and tracking database application. An
absolute requirement imposed from management and the audit personnel is
that
we must be able to track and report on any and every single change of
information. There is now discussion as to whether we should have
developed
from the start in Oracle because it is neither easy nor practical to do
this
type of auditing in Access. You mention SQL Server. Can you point me in
the
direction of an answer in SQL?
Click to expand...

You make it sound as though you will use Access or you will use Oracle.
There's no reason you can't use both.

Access is a development environment that's ideally suited for dealing with
databases. While, as I mentioned previously, Access comes with 2 separate
DBMS, but you can easily use it with any other ODBC-compliant DBMS.

I can't find any articles describing Access 2003 and MSDE, but take a look
at http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_msdeintro.asp
to get an idea. MSDE is a "lite" version of SQL Server, but I think it still
has the audit logs built into, just as SQL Server does. If not, you should
be able to create your own triggers for each table to allow you to log the
details.
 
R

Ray S.

You make it sound as though you will use Access or you will use Oracle.
There's no reason you can't use both.

Access is a development environment that's ideally suited for dealing with
databases. While, as I mentioned previously, Access comes with 2 separate
DBMS, but you can easily use it with any other ODBC-compliant DBMS.

I can't find any articles describing Access 2003 and MSDE, but take a look
at http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_msdeintro.asp
to get an idea. MSDE is a "lite" version of SQL Server, but I think it still
has the audit logs built into, just as SQL Server does. If not, you should
be able to create your own triggers for each table to allow you to log the
details.
Click to expand...

Thanks Doug,

I guess I did seem pessimistic. I do like Access. People at work are
familiar with the simple turn audit on feature on Oracle and find anything
less easy to implement as a pain in the arse. I guess I'm just searching for
something practical and easy to implement.
 
D

Douglas J Steele

Ray S. said:
Thanks Doug,

I guess I did seem pessimistic. I do like Access. People at work are
familiar with the simple turn audit on feature on Oracle and find anything
less easy to implement as a pain in the arse. I guess I'm just searching for
something practical and easy to implement.
Click to expand...

But that's my point. The audit features in DBMS such as Oracle and SQL
Server is built into the engine: it happens automatically whenever you make
any changes to the data, regardless how you make those changes. Turn those
features on, but use Access as the front-end to get the data in and out of
the database.
 
Top