Record Locking with VB6/VBA, ADO, and SqlServer
Posted on 2008-06-09
Using VBA code, ADO, and Sql Server, I get "Timeout Expired" when trying to find a record in a table. All other machines are executing the same code, updating different document records. They do Optimistic locking, so they do not keep any locks except at the "rs1.Update" stage.
I understand Sql Server sometimes does page level locking when doing its updates, but 60 second command timeout should be more than enough for any updates to finish, and for a machine to be able to do the Find operation.
Does anyone see what I'm doing wrong, of what else I can try?
Also, here is a note from previous problems I've had, if anyone as any insight on why this happens.
On sql server, if we do Find_DocumentByDocId(rs, 1), then for DocId 2, 3, etc.,
the first one is ok, all subsequent ones return an empty dataset.
If we change to adUseClient (even just for the first item), it works ok for all items,
even the subsequent ones that are not set as adUseClient
If we do adOpenDynamic, subsequent adOpenKeyset are ok; if we do adOpenStatic, the first adOpenKeyset is ok but subsequent ones are EOF
so... for now, we're using adUseClient until weI find out what the problem is
dim SrcConnection as ADODB.Connection
... omitted some code to setup SrcConnection
... CommandTimeout = 60
Dim cmd1 As ADODB.Command, _
Set cmd1.ActiveConnection = SrcConnection
cmd1.CommandText = "select * from tDocument where DocId = ?"
cmd1.CommandType = adCmdText
cmd1.CommandTimeout = SrcConnection.CommandTimeout
cmd1.Prepared = True
Dim prm1 As ADODB.Parameter
Set prm1 = cmd1.CreateParameter("DocId", adInteger, adParamInput): cmd1.Parameters.Append prm1
sub Find_DocumentByDocId(byref rs1 as ADODB.Recordset, DocId as Long)
cmd1(0) = DocId ' can also do Cmd1("DocId") = DocId
dim rs1 as ADODB.Recordset
Set rs1 = New Recordset
rs1.CursorLocation = adUseClient
rs1.Open cmd1, , adOpenKeyset, adLockOptimistic, 0
'Repeated calls to find and process another DocId:
sub ProcessDocId(DocId as long)
dim rs1 as ADODB.RecordSet
If (rs1.EOF Or rs1.BOF) Then
... not found
do some process which may take a minute or two
the process may change fields in rs1 while it's working, but doesn't do an Update yet
rs1!DocStatus = "Complete"