ado recordset question(s) 1

[KevCon]

I have an ADP (ADE compiled) project that also requires HIPAA auditing, so I created a function that logs changes into a separate table when fields (text boxes) are changed. It works great, but there are some workstations at one location (on a hospital system's WAN) with extremely slow network access, so they're experiencing a 4-5 second lag when changing values and tabbing from one field to the next. I want to streamline the code, perhaps not having to initiate/open a new recordset every time a field is changed.

the function sits at the top of the form's code:

Function audz(FldVal, FldNm)
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Dim rs_A As ADODB.Recordset
Set rs_A = New ADODB.Recordset
With rs_A
Set .ActiveConnection = cn
.Source = "SELECT * FROM tbl_Audit"
.LockType = adLockOptimistic
.Open
.AddNew
!ActionUser = uzer
!ActionDateTime = Now
!ActionType = "Edit"
!ActionTbl = "tbl_patients"
!PT_ID = PTID 'global
!EditField = FldNm
!EditPreValue = preval 'global
!EditNewValue = FldVal
.Update
End With
End Function

'this is used to hold the previous value
Private Sub City_Dirty(Cancel As Integer)
preval = Me.City
End Sub

'this is used to record the changes
Private Sub City_AfterUpdate()
Call audz(Me.City, "City")
End Sub

Question: Do I have to open the recordset every time or can I open it once (like when the form opens) and then just call the function as needed until form exits?
I'm trying to think of methods to improve efficiency and not have to hit the SQL server so frequently.

thanks,
KPC

 

[cmmrfrds]

Don't open a recordset, instead use the Execute method of the currentproject.connection to Insert the record.

For example use this syntax:
Dim sql1 As Variant
Dim recsAff As Long

sql1 = "Insert into CombinedPaymentIDs (paymentid, id) " & _
"Values(5555, 18);"
currentproject.connection.Execute sql1, recsAff
If recsAff = 1 then
msgbox "1 record added"
Else
msgbox "no record added"
End if

 

[KevCon]

cmmrfrds, thank you very much, that is clearly the way to go.
there used to be a slight delay on my end too, now it's instant.
looks like this:

Function audz(FldVal, FldNm)
Dim sql1 As Variant
sql1 = "Insert into tbl_Audit (ActionUser, ActionDateTime, ActionType, ActionTbl, " & _
"PT_ID, EditField, EditPreValue, EditNewValue) " & _
"Values('" & uzer & "', '" & Now & "', 'Edit', 'tbl_patients', " & PTID & ", '" & FldNm & _
"', '" & preval & "', '" & FldVal & "');"
CurrentProject.Connection.Execute sql1
End Function

 

[ESquared]

May I recommend that you use a trigger instead of trying to enforce this in the application? That way you'll be 100% sure that every change is properly entered in the audit logs. The biggest thing is that you may need to run a query in the "back end" for maintenance and this will not be logged by the application.

There are two big wrinkles in this that I know about.

• The first is that if on a form insert operation on a table with an identity column, your trigger inserts to another table that has an identity column, Access ADP will blow up because it uses @@Identity instead of Scope_Identity(). So it returns the identity value of the second table and then blows up when trying to synchronize again (run profiler to see this in action). The workaround is, at the end of your trigger (make sure you SET NOCOUNT ON at the beginning):

SELECT TOP 1 IdentityColumn INTO #Trash FROM Inserted ORDER BY IdentityColumn DESC

This specific syntax will now provide the correct identity value for Access ADP when it uses @@Identity. It also will provide the expected value for multi-row inserts (not something done by the ADP but perhaps useful in other contexts).

• The second wrinkle is passing the current user. One solution is to make the access data project, at startup, use a default low-privileges account to start up, then once the user supplies username & password, reconnect to the database with those. You can now use the appropriate SQL Server functions to return information about the current user. Or you can just use windows integrated authentication from the start.

If you absolutely cannot do this and must have the application use the same username & password for each instance, then you're stuck in a nasty land of examining Access ADP connections and contexts. It generally uses one connection for main forms, one connection for comboboxes and listboxes, and sometimes an additional connection for subforms. But there's no guarantee that it won't open a new connection. The point of all this is that in the context of each of these connections you can run something that switches the user or that inserts into the sysprocesses table some context information that you can retrieve for use with your trigger. There are Microsoft help articles about this. Search for something about application role security with Access ADPs... sorry I don't have a reference for you right this second.

 

[KevCon]

esquared thanks for your suggestion. I see your point(s).
at this point my VB skills are stronger than my SQL server.
however I am slowly appreciating the need to move in the direction of stored procedures etc.