CODEWITHSUNDEEP
excelvbams-access
Raghav Chamadiya
Raghav Chamadiya

Reputation: 240

Track changes (Create Audit Trail) while Editing Access database via Excel Userform

I am shifting to an Access database from Excel database to support multi-user entry. I have excel Userforms which I am using to add and update Access database stored in sharepoint. I want to track all the changes in this database in a seperate table in the database to maintain an Audit trail for review purpose later.

This is an example Update function:

Sub Edit()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.AutomationSecurity = msoAutomationSecurityLow
        Dim cnn As New ADODB.Connection 'dim the ADO collection class
        Dim rst As New ADODB.Recordset 'dim the ADO recordset class
        Dim dbPath As String
        Dim qry As String
        
     
        dbPath = "Share-point UNC path"
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:Database"
        
        Set rst = New ADODB.Recordset 'assign memory to the recordset
    
        qry = "SELECT * FROM FileNumbers WHERE [File_Number]= '" & EditForm.txtFile.Value & "'"
        
        rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
        
        
        With rst
            .Fields("Archival_id").Value , EditForm.txtArchival.Value
            .Fields("Remarks").Value , EditForm.txtRemarks.Value
            .Fields("Retention Category").Value , EditForm.cmbRetention.Value
            .Update
        End With
        
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

I want to track the changes in a seperate table which will look like this:

enter image description here

I can record Edited by, edit date and time, and the new values easily. I am having trouble in recording the old values and Parameters edited, which will be the Column Name in the File Numbers table

I also have a Log Changes function I was using when I was using Excel as database. This is the function:

Sub LogChanges(c As Range, vNew)
    With c
        sep = IIf(Len(titles) > 0, "; ", "") 'need a separator?
        If .Value <> vNew Then
            'track the changes
            titles = titles & sep & .Parent.Cells(1, .Column).Value 'column titles in Row1
            oldValues = oldValues & sep & ValueOrBlank(.Value)  'track old value
            newValues = newValues & sep & ValueOrBlank(vNew)    'track new value
            .Value = vNew                                       'update the cell
        End If
    End With
End Sub

Function ValueOrBlank(v)
    ValueOrBlank = IIf(Len(v) > 0, v, "[blank]")
End Function

And then I was using the variables, oldValues, newValues, and titles to update the Audit trail sheet. I can't seem to implement this method here. Please help

Upvotes: 0

Views: 492

Answers (1)

FunThomas
FunThomas

Reputation: 29171

You shouldn't implement things like Audit Trails on the front-end side (Userform or data objects). Those things belong to the back-end (Database). The front-end shouldn't care about facts how data is stored and if there is an audit trail.

On a database, you usually use a mechanism called trigger. A trigger is a kind of script that runs in case of a database event, eg an insert or an update command. Within such a trigger, you can implement the audit trail logic.

Now in Access, the name for those mechanism is not trigger, but data macro, but basically it's the same idea. I found this answer about trigger in Access on SO which basically links to the official docu from Microsoft about data macros

Upvotes: 2

Related Questions