Search code examples
vbaloggingms-access-2010audit

Audit trail in Access 2010 database

I am trying to create an audit trail in an Access 2010 database. I found some code on www.wvmitchell.com and it works well except for one issue. It records records that are updated but not new records or deleted records. It is very important that those are recorded. The following is the information and code that I used: 

Option Compare Database
Option Explicit

Sub TrackChanges(F As Form)
Dim ctl As Control, frm As Form
Dim MyField As String, MyKey As Long, MyTable As String
Dim db As DAO.Database, rs As DAO.Recordset
On Error Resume Next
Set frm = F
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl__ChangeTracker")
With frm
    MyTable = .Tag
    ' find the primary key & its value, based on the Tag
    For Each ctl In .Controls
        If ctl.Tag = "PK" Then
            MyField = ctl.Name
            MyKey = ctl
            Exit For
        End If
    Next ctl
    For Each ctl In .Controls
        ' inspect only data-bound controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acCheckBox
                If Nz(ctl.ControlSource, "") > "" Then
                    ' if changed, record both old & new values
                    If Nz(ctl.OldValue, "")<> Nz(ctl, "") Then
                        rs.AddNew
                        rs!FormName = .Name
                        rs!MyTable = MyTable
                        rs!MyField = MyField
                        rs!MyKey = MyKey
                        rs!ChangedOn = Now()
                        rs!FieldName = ctl.Name
                        If ctl.ControlType = acCheckBox Then
                            rs!Field_OldValue = YesOrNo(ctl.OldValue)
                            rs!Field_NewValue = YesOrNo(ctl)
                        Else
                            rs!Field_OldValue = Left(Nz(ctl.OldValue, ""), 255)
                            rs!Field_NewValue = Left(Nz(ctl, ""), 255)
                        End If
                        rs!UserChanged = UserName()
                        rs!CompChanged = CompName()
                        rs.Update
                    End If
                End If
        End Select
    Next ctl
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Private Function YesOrNo(v) As String
    Select Case v
        Case -1
            YesOrNo = "Yes"
        Case 0
            YesOrNo = "No"
    End Select
End Function
  1. A table to store the results. For text fields, I've indicated the length in the Description:

Here is a VBA module that will create the table for you.

Option Compare Database
Option Explicit

Sub Create_tbl__ChangeTracker()
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim idx As DAO.Index
    Dim tdf As DAO.TableDef
    '
    Set db = CurrentDb
    Set tdf = db.CreateTableDef("tbl__ChangeTracker")
With tdf
    ' ID is AutoNumber and Primary Key
    Set fld = .CreateField("ID", dbLong)
    fld.Attributes = dbAutoIncrField
    .Fields.Append fld
    Set idx = .CreateIndex("ID")
    idx.Fields = "ID"
    idx.Primary = True
    .Indexes.Append idx
    '
    ' add remaining fields
    Set fld = .CreateField("FormName", dbText, 64)
    .Fields.Append fld
    Set fld = .CreateField("MyTable", dbText, 64)
    .Fields.Append fld
    Set fld = .CreateField("MyField", dbText, 64)
    .Fields.Append fld
    Set fld = .CreateField("MyKey", dbText, 64)
    .Fields.Append fld
    Set fld = .CreateField("ChangedOn", dbDate)
    .Fields.Append fld
    Set fld = .CreateField("FieldName", dbText, 64)
    .Fields.Append fld
    Set fld = .CreateField("Field_OldValue", dbText, 255)
    .Fields.Append fld
    Set fld = .CreateField("Field_NewValue", dbText, 255)
    .Fields.Append fld
    Set fld = .CreateField("UserChanged", dbText, 128)
    .Fields.Append fld
    Set fld = .CreateField("CompChanged", dbText, 128)
    .Fields.Append fld
    Set fld = .CreateField("Action", dbtext, 64
    .Fields.Append fld
End With
db.TableDefs.Append tdf
Set idx = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

After adding these objects, modify each form as follows: 1. Set the Tag property for the form = the name of the underlying table. 2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere. 3. Add the 

Form_BeforeUpdate event and invoke the tracking code using:
   TrackChanges Me

4. If you are using subforms, you'll need to perform these three steps for each subform as well.

In my database I added a text field called Action to the tbl_ChangeTracker. I need to know how to write the code to populate it. Thanks in advance for any help I get.

Solution

  • You have your code written ;

                    If Nz(ctl.ControlSource, "") > "" Then
                ' if changed, record both old & new values
                If Nz(ctl.OldValue, "")<> Nz(ctl, "") Then

    Where it specifies if changed record both old and new values. You have nothing in there to specify what to do when adding new values.

    In your before update procedure of the form place the following ;

       If Me.NewRecord Then
        Call TrackChanges(me, "NEW")
    Else
        Call TrackChanges(me ,"EDIT")
    End If

    Then with your procedure for the audit thing change it to accept another argument of type String so for instance;

    Sub TrackChanges(F As Form, action As String)

    Then in your code you want to have;

    Select Case action
Case is = "New"
 ' code here for adding new record, as an example
Case is = "Edit"
'your code as you have it above here for edits

    If you want to track deletes, same sort of thing in the before delete event;

    Call TrackChanges(Me,"DELETE")

    Then in your TrackChanges procedure have another Case is = "Delete" then the code for handling the delete.

    This should set you on the right path, then with your Action field you can do !action = action so you know if it's edit add or delete etc.

    HTH Mark

    Edit 1 following your comment so you can see the layout; The Select Case code would be in your TrackChanges function. Before 'with Frm' place the select case. Then you want to set it out as follows;

    Select Case action
 Case is = "Edit"
       With frm 'etc all your code here for if you edit record
Case is = "new"
       With frm 'etc all your code here for if adding new record
End select

    Let me know if that makes sense, if not shoot me a pm and I can talk you through it in more depth if you need :)