title: Track changes (Create Audit Trail) while Editing Access database via Excel Userform
body: <p>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.</p>
  <p>This is an example Update function:</p>
  <pre><code>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 = &quot;Share-point UNC path&quot;
          
          cnn.Open &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot; &amp; dbPath &amp; &quot;;Jet OLEDB:Database&quot;
          
          Set rst = New ADODB.Recordset 'assign memory to the recordset
      
          qry = &quot;SELECT * FROM FileNumbers WHERE [File_Number]= '&quot; &amp; EditForm.txtFile.Value &amp; &quot;'&quot;
          
          rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
          
          
          With rst
              .Fields(&quot;Archival_id&quot;).Value , EditForm.txtArchival.Value
              .Fields(&quot;Remarks&quot;).Value , EditForm.txtRemarks.Value
              .Fields(&quot;Retention Category&quot;).Value , EditForm.cmbRetention.Value
              .Update
          End With
          
      rst.Close
      cnn.Close
      
      Set rst = Nothing
      Set cnn = Nothing
  End Sub
  </code></pre>
  <p>I want to track the changes in a seperate table which will look like this:</p>
  <p><a href="https://i.stack.imgur.com/CiQVR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CiQVR.png" alt="enter image description here" /></a></p>
  <p>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</p>
  <p>I also have a Log Changes function I was using when I was using Excel as database. This is the function:</p>
  <pre><code>Sub LogChanges(c As Range, vNew)
      With c
          sep = IIf(Len(titles) &gt; 0, &quot;; &quot;, &quot;&quot;) 'need a separator?
          If .Value &lt;&gt; vNew Then
              'track the changes
              titles = titles &amp; sep &amp; .Parent.Cells(1, .Column).Value 'column titles in Row1
              oldValues = oldValues &amp; sep &amp; ValueOrBlank(.Value)  'track old value
              newValues = newValues &amp; sep &amp; ValueOrBlank(vNew)    'track new value
              .Value = vNew                                       'update the cell
          End If
      End With
  End Sub
  
  Function ValueOrBlank(v)
      ValueOrBlank = IIf(Len(v) &gt; 0, v, &quot;[blank]&quot;)
  End Function
  </code></pre>
  <p>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</p>