By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Creating an audit trail in Access 2000

P: n/a
Dear Sirs,

Having previously used other SQL implementations I have grown used to
having an audit trail to find when problems occured, the problem I am
increasingly finding with Access is that there is no 'inbuilt' audit
trail for me to see what has happend to the database to get it into its
current state.

Is there a way to make Access 2002 (in 2000 mode) to generate a log of
ALL changes to table data ? A time/datestamp to start with would be
good, but eventually with a source hostname/IP would be good.

Many thanks in advance for your kind help,
Regards,

Alan Knipmeyer.
--
IT Technician

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
al***********@yahoo.com wrote in message news:<10**********************@c13g2000cwb.googleg roups.com>...
Dear Sirs,

Having previously used other SQL implementations I have grown used to
having an audit trail to find when problems occured, the problem I am
increasingly finding with Access is that there is no 'inbuilt' audit
trail for me to see what has happend to the database to get it into its
current state.

Is there a way to make Access 2002 (in 2000 mode) to generate a log of
ALL changes to table data ? A time/datestamp to start with would be
good, but eventually with a source hostname/IP would be good.

Many thanks in advance for your kind help,
Regards,

Alan Knipmeyer.


See Allen Browne's website. He has code for this and instructions.
Nov 13 '05 #2

P: n/a
Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:
Allen Browne has an example of how to do it on his site however, and I
strongly encourage it's reading:
<http://members.iinet.net.au/~allenbrowne/AppAudit.html>


I gratefully used Allen's method for ages but found that querying it took
ages too and it also stores a lot of static data. I developed my own method
of tracking changes which I'll evetually put on my web site.

I have the following code in my form's before update event (DAO in A97):

****
Dim ctl As Control
Dim db As Database, rs As Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)

For Each ctl In Me.Detail.Controls
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
If ctl.SpecialEffect = 0 Then GoTo Skip
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If (IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <> ctl.Value Then
'MsgBox ctl.name
With rs
.AddNew
![DataSource] = "MyTable"
![ID] = Me.txtID
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
![UpdatedBy] = CurrentUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

****

The code cycles through controls with data sources and fires the update
routine when it finds one that has changed. This keeps the amount of stored
data to a minimum and you can call the data from a 'history' form if you
want.

HTH - Keith.
www.keithwilby.com
Nov 13 '05 #3

P: n/a
Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:
You may want to look at using the TypeOf operator in your code to
select what controls for your code to work on/with


Hi Chuck, thanks for the info.

I did try the TypeOf method first but I seem to remember hitting a snag
(can't quite remember what it was), maybe I'll revisit that soon :o)

Keith.
www.keithwilby.com
Nov 13 '05 #4

P: n/a
Hi Keith,
Thanks for your great function for creating an Audit Trail. To
eliminate labels and other unwanted stuff I used the following
structure.
If ((ctl.ControlType = acTextBox) Or _
(ctl.ControlType = acComboBox) Or _
(ctl.ControlType = acCheckBox) Or _
(ctl.ControlType = acOptionGroup)) Then

Then I limited my search to the controls I was editing
strControlName = ctl.Name
If (Me(strControlName).Locked = False) Then

Hank Reed
Nov 13 '05 #5

P: n/a
ha********@aol.com (Hank Reed) wrote:
To
eliminate labels and other unwanted stuff I used the following
structure.
If ((ctl.ControlType = acTextBox) Or _
(ctl.ControlType = acComboBox) Or _
(ctl.ControlType = acCheckBox) Or _
(ctl.ControlType = acOptionGroup)) Then

Then I limited my search to the controls I was editing
strControlName = ctl.Name
If (Me(strControlName).Locked = False) Then


Cheers Hank, will look into that.

Regards,
Keith.
www.keithwilby.com
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.