Problems using access with ULS from excel connecting through a DSN

[Wylie]

I have an access application protected with ULS running on server
which is used by a handful of people at a time. I also have an excel
sheet that contains a custom function to run aggregate queries with
ADO recordsets connecting through a DSN to this Access database. As
several cells in the excel file may contain this formula, it can bog
down in retrieving data.

My problem is that intermittently the excel application will give an
"unable to save file - in use by someone else" error. I can confirm
no one else is in the file, so it seems the user is locking themself
out. It also causes the Access file to error for the user, but not
for other users.

I've tried consistently replicating the error but have been able to
narrow down the cause.

Best,
wylie



The code I'm calling in excel:

Public Function CJSum(Expr As String, Domain As String, Optional
Criteria)
On Error GoTo Err_ELookup
'Note: Requires a reference to the ADO library.
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

'Build the SQL string.
strSQL = "SELECT sum(" & Expr & ") as sumTotal FROM " & Domain
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria
End If

strSQL = strSQL & ";"

'Lookup the value.
Set db = New ADODB.Connection
db.CursorLocation = adUseClient

db.Open "DSN=CJAS;"
Set rs = New ADODB.Recordset
rs.Open strSQL, db, adOpenForwardOnly, adLockOptimistic
If rs.RecordCount = 0 Then
CJSum = Null
Else
CJSum = rs(0)
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit_ELookup:
Exit Function
Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " &
Err.Number
Resume Exit_ELookup
End Function