Martin Green Audit Trail, ADO (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 08:55
Joined
Jul 9, 2015
Messages
424
So, while reading some stickies on database design, I came across a post with a link to Martin Green's site.

He uses an ADO connection for his audit trail. I am not familiar with ADO, only been using DAO for record sets. Is there a specific reason for using ADO? Will DAO work fine for creating this audit trail?

Thanks!
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:55
Joined
Jan 23, 2006
Messages
15,364
I just took a copy of the AuditTrail_Simple and after making a few changes I get it to work fine with DAO.

Here are the changes to the routine ( see my 'jed in comments)
Code:
Sub AuditChanges(IDField As String)
10       On Error GoTo AuditChanges_Error 'jed


         ' Dim cnn As ADODB.Connection  'jed
         ' Dim rst As ADODB.Recordset   'jed
          Dim db As dao.Database 'jed
          Dim rst As dao.Recordset 'jed
          Dim ctl As Control
          Dim datTimeCheck As Date
          Dim strUserID As String
         ' Set cnn = CurrentProject.Connection 'jed
         ' Set rst = New ADODB.Recordset  'jed
20       Set db = CurrentDb  'jed
30       Set rst = db.OpenRecordset("tblaudittrail") 'jed

       '   rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic  'jed
40        datTimeCheck = Now()
50        strUserID = Environ("USERNAME")
60        For Each ctl In Screen.ActiveForm.Controls
70            If ctl.Tag = "Audit" Then
80                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
90                    With rst
100                       .AddNew
110                       ![DateTime] = datTimeCheck
120                       ![UserName] = strUserID
130                       ![FormName] = Screen.ActiveForm.Name
140                       ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
150                       ![FieldName] = ctl.ControlSource
160                       ![OldValue] = ctl.OldValue
170                       ![NewValue] = ctl.Value
180                       .Update
190                   End With
200               End If
210           End If
220       Next ctl
230
AuditChanges_Exit:
240       On Error Resume Next
250       rst.Close
         ' cnn.Close     'jed
260       Set rst = Nothing
         ' Set cnn = Nothing   'jed
270       Exit Sub
AuditChanges_Err:
280       MsgBox Err.Description, vbCritical, "ERROR!"
290       Resume AuditChanges_Exit

300      On Error GoTo 0
310      Exit Sub

AuditChanges_Error:  ' I added this and line numbers to go with my Error handler (MZTools for VBA)
320       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure AuditChanges of Module basAudit"
End Sub

I made a few changes while testing and here are a few of audit records re my latest changes
Code:
AuditTrailID	DateTime	UserName	FormName	RecordID	FieldName	OldValue	NewValue
11	29/06/2016 8:02:15 PM	mellon	frmEmployees	3	Department	IT	Admin
12	29/06/2016 8:02:35 PM	mellon	frmEmployees	1	Lastname	Greene	Greenlees
13	29/06/2016 8:10:14 PM	mellon	frmCustomers	1	AddressLine2	15 Upper St Martin's Lane	159 Upper St Martin's Lane
 

mjdemaris

Working on it...
Local time
Today, 08:55
Joined
Jul 9, 2015
Messages
424
Nice!

So, is there a benefit with ADO versus DAO? Does it really matter if the database is internal with ten distributed front ends?
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:55
Joined
Jan 23, 2006
Messages
15,364
I have used DAO almost exclusively, so I can not tell you of benefits of one vs the other.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:55
Joined
Jan 20, 2009
Messages
12,849
So, is there a benefit with ADO versus DAO?

ADO recordsets can:

  • Be disconnected from their data source and exist solely in memory.
  • Be reconnected back to their data source and update changes.
  • Be saved to a file and reloaded back to memory.
  • Return a valid count without MoveLast.
 

Users who are viewing this thread

Top Bottom