Audit Trail – Access 2010 w/SQL Server backend

I don’t do these posts much anymore, but something I just got working this morning definitely warrants one.

I maintain a database here that has an Access 2010 front-end and SQL Server back-end. Since I had to rebuild the database from scratch and actually know how to maintain it I took a few database classes and got a certificate where I learned some SQL and basic database knowledge. Little did I know that I also should’ve learned visual basic. I’ve played around with Access in the past and used it for little personal databases, but I’ve never actually built and maintained one like this. I tried desperately to avoid VB since I’ve never even looked at it before and I’ve always hated programming. But there were times where I couldn’t avoid it, some things you just can’t do without it.

The major thing I’ve been working on this week is creating an Audit Trail so I have record of who, what, and when a person edits or deletes a record from the database. The first link I found was this one http://www.fontstuff.com/access/acctut21.htm. I created the table just as he described it. The first problem I had was, I’m guessing, because it isn’t an Access database – it has a SQL back-end. Again, I knew basically nothing about VB when I started this a few days ago. Running the code it wasn’t even finding the table so I had to use my google-fu to figure what wtf I needed to do. That led me to learning about connection strings.

So this is what the section of my code looks like before it actually gets to the auditing part:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
    cnn.Provider = “sqloledb”
    cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=[server IP Address];Database=[DB Name];Trusted_Connection=yes;”
    cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()

The sections in bold are what I changed or added to the original code. I already have a function in Access, that I found somewhere else online, that automatically enters the username of the person logged into the computer. That’s part of the code I use to track new entries. All the reading I did the last few days said something like that is better than using enviorn(“username”) and since I already had the function I just used it.

So getting that far and using the rest of his code exactly the same at least got it partially working. I was able to track edits with all the information I wanted. But deletes and additions were not being recorded properly. It would record when a new record was added and also deleted, but it wasn’t capturing the primary key value for some reason, which is the entire point. I really don’t understand why it wasn’t working, I downloaded his example DB and looked at it, still couldn’t figure it out.

More research led me to another forum where the person used Martin Green’s code, but had the same problems I was having. It’s the 5th post down the page, by johngachui. He changed some other things in the code that weren’t what I was interested in, so I just focused on the part about the deleted records. He added the DeletedID As String part that I added up above. I didn’t really care about recording additions either since I already do that another way so once I got the code functioning, I removed the parts about recording new records.

Here’s the functioning code as I need it, without recording additions:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
cnn.Provider = “sqloledb”
cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=10.55.12.4;Database=Deconfliction;Trusted_Connection=yes;”
cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()
Select Case UserAction
Case “EDIT”
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = “Audit” Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = DeletedID
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, “ERROR!”
Resume AuditChanges_Exit
End Sub

After making the changes in this code, The person in the 2nd forum also said to do things differently on the forms that call the auditchanges function and this is what I used:

Dim DelID As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges(“cevid”, “EDIT”)
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
DelID = [cevid]
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call AuditChanges(“cevid”, “DELETE”, DelID)
End Sub

Works great now, on the main form at least. I came across a ton of other forums where people reported issues with this not working on subforms because of the Screen.ActiveForm.Controls(IDField).Value part. I haven’t gotten that far yet. That is my next obstacle.

Sources:

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: