Audit Trail

Hi all,

There are basically two samples in this thread.

  1. An audit trail that requires the "Audit Trail Field" in each table.
  2. An audit trail that requires a table and where all changes are stored in a table.
I really like the idea of the separate table and is how I am currently set up for this. However, as the database grows, I am envisioning this table getting quite large. Granted I don't want users editing records all the time, but it's still going to happen. Given that I am thinking the Audit trail table may get quite large, I now am going back and forth as to which method I may prefer.

So my questions are:

  1. Which method do you prefer and why?
  2. For either method, have you experienced a large table or large memo fields that might be semi-difficult to search?
Thanks,
Craig
 
Just to throw out a alternative- I use MySQL backend, among other reasons, to satisfy the audit trail requirement, which is easier as I can just use binary logs and triggers to timestamp & userstamp everything. Surely one can do same thing with SQL Server if this is available (I don't know whether SQL Server Express, which is free, supports the functionalities), and of course there's PostgreSQL which among free RDBMS has slightly more documentation for working with Access (but that's just one giant FAQ. Not that lot.)

Going back to pure Access solution, I'd use table because it's easier and keeps table normalized. As for size, I'd just truncate the table periodically. How much and how long ago, depends on your individual needs. Logs are good up to a point, but when you're keeping logs of what happened 100 years ago and books has been long since closed, it's kind of... pointless.

As for memo fields, one option is to truncate the memo to 255 character and store it in a separate field. Not normalized, but provides you an option for faster search. Of course, this may mean that auditing memos would require its own table for better performance.

HTH.
 
In my opinion you've got to go with a table, a memo field is well just not right. I can't understand Microsoft even proposing such a thing in the first place, that's where it came from I believe.

If you carefully design how your database records audit information, you can make sure that this audit table Remains as small as possible. Don't store the "text value" you would find in a combo box, store the numeratical value representing this information. You can do various things like this.
 
In keeping with the spirit of this thread, here's my code that uses the separate table method. I'm not so sure I am looking for the existing table in the preferred manner, but this works quite well. Let me know what you think.

Code:
Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim sAuditTable As String
Dim sSQL As String

Dim sTable As String        'Table where the record is being edited
Dim CTL As Control          'The control in the form being edited
Dim sFrom As String         'Original Data in the control
Dim sTo As String           'What the original data was changed to

Dim sPCName As String       'Name of the PC that is being used
Dim sPCUser As String       'Name of the User on the Networked PC
Dim sDBUser As String       'Name of the Database User
Dim sDateTime As String     'Date and Time of the change

'===========================================================================================
'
'   This Audit Trail will track changes to existing records.
'   In the "Before Update" event of the FORM enter the following:
'
'       Call AuditTrail(Me.Form, [RecordID])
'
'   Make sure to create a table called "tbl_AuditLog" and have the following fields:
'   (A table will be created automatically if it does not exist)
'
'       1. RecordID (This is a unique number)
'       2. txt_Table (This is the table where the record was changed)
'       3. lng_TblRecord (This is the RecordID number from the record being changed)
'       4. txt_Form (This is the form being used to edit the record)
'       5. txt_Control (This is the data entry control (field) that was edited
'       6. mem_From (This is the original data in the control (field)
'       7. mem_To (This is what the original data was changed to)
'       8. txt_PCName (This is the name of the PC used to edit the record)
'       9. txt_PCUser (This is the name of the user logged onto the PC)
'       10. txt_DBUser (This is the name of the person looged on to the databse if used)
'       11. dat_DateTime (This is the date and time the record was edited.)
'
'
'   The inspiration behind this code is from:
'       1. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
'       2. http://www.access-programmers.co.uk/forums/showthread.php?t=44231
'
'
'============================================================================================
    


Public Function AuditTrail(frm As Form, lngRecord As Long)
On Error GoTo Error_Handler
    
    '----------------------------------------------------------------------
    '   Skips this procedure if a new record is being entered in the form
    '----------------------------------------------------------------------
    If frm.NewRecord = True Then
        Exit Function
    End If
        
    '----------------------------------------------------------------------
    '   Checks to see if the tbl_AuditLog Exists
    '   Creates the table if it does not exist
    '----------------------------------------------------------------------
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
    
    sAuditTable = "tbl_AuditLog"
    On Error Resume Next
    If IsNull(dbs.TableDefs(sAuditTable)) Then
        'Table does not exist
        On Error GoTo Error_Handler
        sSQL = "CREATE TABLE tbl_AuditLog([RecordID] COUNTER PRIMARY KEY, [txt_Table] TEXT(50), [lng_TblRecord] LONG, " & _
                "[txt_Form] TEXT(50), [txt_Control] TEXT(50), [mem_From] MEMO, [mem_To] MEMO, [txt_PCName] TEXT(50), " & _
                "[txt_PCUser] Text(50), [txt_DBUser] Text(50), [dat_DateTime] DATETIME);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
    Else
        'Table Exists. Do Nothing
        On Error GoTo Error_Handler
        
    End If

    Set dbs = Nothing
       
     
       
    '----------------------------------------------------------------------
    '   Runs through each control on the form and checks for edits/changes
    '----------------------------------------------------------------------
    For Each CTL In frm
    
        Select Case CTL.ControlType     'Only checks data entry type controls.
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                
                sFrom = Nz(CTL.OldValue, "Null")
                sTo = Nz(CTL.Value, "Null")
                
                If sFrom <> sTo Then
                
                    '-----------------------------------
                    '   Gets the required Info
                    '-----------------------------------
                    sTable = frm.RecordSource
                    sPCName = Environ("COMPUTERNAME")
                    sPCUser = Environ("Username")
                    sDBUser = "Me"      'Get Username from the database login
                    sDateTime = Now()

                    sSQL = "INSERT INTO tbl_AuditLog ([txt_Table], [lng_TblRecord], [txt_Form], [txt_Control], " & _
                           "[mem_From], [mem_To], [txt_PCName], [txt_PCUser], [txt_DBUser], [dat_DateTime]) " & _
                           "VALUES ('" & sTable & "', '" & lngRecord & "', '" & frm.Name & "', " & _
                           "'" & CTL.Name & "', '" & sFrom & "', '" & sTo & "', '" & sPCName & "', " & _
                           "'" & sPCUser & "', '" & sDBUser & "', '" & sDateTime & "')"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL sSQL
                    DoCmd.SetWarnings True
    
                End If
        End Select
    Next CTL
    
Error_Handler_Exit:
   Exit Function

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

End Function
 
Last edited:
Hi,

I'd like to combine the audit trail code with the following which also uses the BeforeUpdate preceedure. Anyone done this/know how to do it?

Private Sub Form_BeforeUpdate(Cancel As Integer)
' This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue with the save operation or to cancel it. Then
' the action that triggered the BeforeUpdate event is completed.
Dim ctl As Control
On Error GoTo Err_BeforeUpdate
' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If
Exit_BeforeUpdate:
Exit Sub
Call Audit_Trail

Form_BeforeUpdate_Exit:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub
 
Last edited:
Put the audit trail code in the vbYes operation.

Something like:

Do you want to save?
If yes then
Audit trail here
If no then
Undo

It can be added to the audit trail function.

Just my opinion, but why would you want the user to be able to choose whether or not the audit trail is recorded or not? Wouldn't that defeat the purpose of it?

Just some food for thought.
Craig




Hi,

I'd like to combine the audit trail code with the following which also uses the BeforeUpdate preceedure. Anyone done this/know how to do it?
 
Last edited:
You're right, I dont want users to decide whether to record the audit trail or not.

I just want to incorporate the save changes prompt box with the audit trail when moving to the next record or closing the form.

Sorry im new to this, where is the vbYes operation?
 
You're right, I dont want users to decide whether to record the audit trail or not.

I just want to incorporate the save changes prompt box with the audit trail when moving to the next record or closing the form.

Sorry im new to this, where is the vbYes operation?

So you'd like the user to be able to save or undo the changes, before the audit trail procedure? That make more sense. Sorry I misunderstood your goal.

I believe that would be quite easy.

If you are using my audit trail example, try something like this in the before_update event of the form (You may have to tweak it a little) and I am not sure it will undo ALL of the changes:
Code:
    Dim sPrompt As String
    Dim sTitle As String
    Dim iResponse As Integer
    
    sPrompt = "Do you wish to save the changes made?"
    sTitle = "Database Message"
    iResponse = MsgBox(sPrompt, vbYesNo, sTitle)
    If iResponse = vbYes Then
        Call AuditTrail(Me.Form, [RecordID])
        DoCmd.RunCommand acCmdSaveRecord
    ElseIf iResponse = vbNo Then
        DoCmd.RunCommand acCmdUndo
    End If
 
Last edited:
Two questions;

1. I'm only required to audit the date of changes made to my data so I've selected 'date/time' for the data type of my audittrail column. This works fine, however it doesn’t overwrite with the latest date when subsequent changes are made. Can anyone suggest how I can get the most recent date of change to appear in my table?

2. Is it possible to run the audit trail exclusively on one field rather than the whole form?

Thanks in advance for your help.
 
Last edited:
In keeping with the spirit of this thread, here's my code that uses the separate table method. I'm not so sure I am looking for the existing table in the preferred manner, but this works quite well. Let me know what you think.

Code:
Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim sAuditTable As String
Dim sSQL As String

Dim sTable As String        'Table where the record is being edited
Dim CTL As Control          'The control in the form being edited
Dim sFrom As String         'Original Data in the control
Dim sTo As String           'What the original data was changed to

Dim sPCName As String       'Name of the PC that is being used
Dim sPCUser As String       'Name of the User on the Networked PC
Dim sDBUser As String       'Name of the Database User
Dim sDateTime As String     'Date and Time of the change

'===========================================================================================
'
'   This Audit Trail will track changes to existing records.
'   In the "Before Update" event of the FORM enter the following:
'
'       Call AuditTrail(Me.Form, [RecordID])
'
'   Make sure to create a table called "tbl_AuditLog" and have the following fields:
'   (A table will be created automatically if it does not exist)
'
'       1. RecordID (This is a unique number)
'       2. txt_Table (This is the table where the record was changed)
'       3. lng_TblRecord (This is the RecordID number from the record being changed)
'       4. txt_Form (This is the form being used to edit the record)
'       5. txt_Control (This is the data entry control (field) that was edited
'       6. mem_From (This is the original data in the control (field)
'       7. mem_To (This is what the original data was changed to)
'       8. txt_PCName (This is the name of the PC used to edit the record)
'       9. txt_PCUser (This is the name of the user logged onto the PC)
'       10. txt_DBUser (This is the name of the person looged on to the databse if used)
'       11. dat_DateTime (This is the date and time the record was edited.)
'
'
'   The inspiration behind this code is from:
'       1. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
'       2. http://www.access-programmers.co.uk/forums/showthread.php?t=44231
'
'
'============================================================================================
    


Public Function AuditTrail(frm As Form, lngRecord As Long)
On Error GoTo Error_Handler
    
    '----------------------------------------------------------------------
    '   Skips this procedure if a new record is being entered in the form
    '----------------------------------------------------------------------
    If frm.NewRecord = True Then
        Exit Function
    End If
        
    '----------------------------------------------------------------------
    '   Checks to see if the tbl_AuditLog Exists
    '   Creates the table if it does not exist
    '----------------------------------------------------------------------
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
    
    sAuditTable = "tbl_AuditLog"
    On Error Resume Next
    If IsNull(dbs.TableDefs(sAuditTable)) Then
        'Table does not exist
        On Error GoTo Error_Handler
        sSQL = "CREATE TABLE tbl_AuditLog([RecordID] COUNTER PRIMARY KEY, [txt_Table] TEXT(50), [lng_TblRecord] LONG, " & _
                "[txt_Form] TEXT(50), [txt_Control] TEXT(50), [mem_From] MEMO, [mem_To] MEMO, [txt_PCName] TEXT(50), " & _
                "[txt_PCUser] Text(50), [txt_DBUser] Text(50), [dat_DateTime] DATETIME);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
    Else
        'Table Exists. Do Nothing
        On Error GoTo Error_Handler
        
    End If

    Set dbs = Nothing
       
     
       
    '----------------------------------------------------------------------
    '   Runs through each control on the form and checks for edits/changes
    '----------------------------------------------------------------------
    For Each CTL In frm
    
        Select Case CTL.ControlType     'Only checks data entry type controls.
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                
                sFrom = Nz(CTL.OldValue, "Null")
                sTo = Nz(CTL.Value, "Null")
                
                If sFrom <> sTo Then
                
                    '-----------------------------------
                    '   Gets the required Info
                    '-----------------------------------
                    sTable = frm.RecordSource
                    sPCName = Environ("COMPUTERNAME")
                    sPCUser = Environ("Username")
                    sDBUser = "Me"      'Get Username from the database login
                    sDateTime = Now()

                    sSQL = "INSERT INTO tbl_AuditLog ([txt_Table], [lng_TblRecord], [txt_Form], [txt_Control], " & _
                           "[mem_From], [mem_To], [txt_PCName], [txt_PCUser], [txt_DBUser], [dat_DateTime]) " & _
                           "VALUES ('" & sTable & "', '" & lngRecord & "', '" & frm.Name & "', " & _
                           "'" & CTL.Name & "', '" & sFrom & "', '" & sTo & "', '" & sPCName & "', " & _
                           "'" & sPCUser & "', '" & sDBUser & "', '" & sDateTime & "')"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL sSQL
                    DoCmd.SetWarnings True
    
                End If
        End Select
    Next CTL
    
Error_Handler_Exit:
   Exit Function

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

End Function

I like this method of doing this, keeping it in its own table. Also, these seems like it doesn't directly feed off a text box, as it is not coded to anything except the current frm.

However, when I set up the table and try to run this by changing data in a particular form it gives me the "Compile Error: Invalid Use of Property" at the "Call AuditTrail(Me.Form, [RecordID])" line.

Any ideas why?

To Clarify, under my form I have the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer) --> this is where the error occurs

Call AuditTrail(Me.Form, [RecordID])

End Sub
And then under a separate Public Function known as "AuditTrail" I have the exact code from above. Let me know if I'm doing something wrong.
 
Last edited:
I use RecordID as Long Integer (Auto Number). Is yours the same? Do you have a RecordID on your form? Do you have the "Microsoft DAO 3.6 Object Library" Reference enabled?

Here is a copy of mine. Take a look and see what's different.
View attachment Audit_Trail.zip


I like this method of doing this, keeping it in its own table. Also, these seems like it doesn't directly feed off a text box, as it is not coded to anything except the current frm.

However, when I set up the table and try to run this by changing data in a particular form it gives me the "Compile Error: Invalid Use of Property" at the "Call AuditTrail(Me.Form, [RecordID])" line.

Any ideas why?

To Clarify, under my form I have the following code:

And then under a separate Public Function known as "AuditTrail" I have the exact code from above. Let me know if I'm doing something wrong.
 
Last edited:
I also get the same error despite being the same to Irish634's attachment.

Does the attachment I put in work for you guys? It works here.
Something has to be different. If you want attach yours and I'll look
 
hi ghudson

i have used the audit trail for access2003 and it works fine the problem i have is for all the users it is showing username as admin i am not using access security can u plz help me to solve this problm
 
Hi, I'm using the audit trail. it was working fine until I added more fields in the sql db that is connected to my MS Access db. the new fields are not tracked by audit trail! I have no idea why is that!..
sometimes I get the following error when trying to save changes done in the current record: "Error 0 - Reserved Error"

Can someone help me?

Thanks in advance.
 
Last edited:
it is fixed. there was a control that is related to another table, and this was causing the problem.
 
I am using this code in MDB file quite succesfully. However, when I create a mde file for users, then changes made by them in the in the form are not written back to the audit table. Is there some sort of bar in this code preventing mde file to be audited? MDB works fine. Also, yes/no fields changes are not captured by this code. Could you confirm if this is expected.
 
Hi

Thanks for this.

I am a beginner when it comes to database deisgn and VBA code, I set up a rudamentory DB for work a few years ago to hold all participant details and training details across all our programmes. I have been since adding various reports etc at the managements discreation.

Some staff are now being judged on the accuracy of the information stored in the DB and they came to me and said they swore they entered stuff correctly but the next day it was wrong. So I set up a little trap, after the staff member had entered the data, i took a screenprint showing time, date etc, and low and behold when we checked the next day it was changed.

If staffs jobs are on the line I dont think the way the DB is currently set up is a fair judgement.

The DB is set up to use a single login so there was no way to track what happened, but I now want to set up this Audittrail with some sort of multi user login. I found reference to this post earlier in this thread http://www.access-programmers.co.uk/forums/showpost.php?p=175590&postcount=4 and I really like how it works as Access Security is way beyond me at the moment.

Can anyone help me in getting the Audittrail catch the usernames from that?
 
if you dont login to access with access security every user will be logging in as "Admin". You can easily use a function to pick up the windows login, as an alternative.

However you need to get to the bottom of what is going on - having multiple users sharing a single login is not advisable, and may even be causing these errors.

You should split your database for multiple users

Could you start a new thread for this, as this is a very old long thread
 
Could I request the author of audit trail to answer me on the issue I raised in regards to MDE file?
 

Users who are viewing this thread

Back
Top Bottom