Hanging Recordset.Find

[londonboy]

Hi. I'm having trouble getting record locking to work between an Access front-end and a SQL database. After reading a number of web posts, I found the relevant options to the Recordset.Open command to obtain the type of locking I want, however I now have a hanging Recordset.Find command.

The code sample is below:
-------------------------
Private Sub FindRecord()
Dim cnConnx as ADODB.Connection
Dim rsDetails as ADODB.Recordset

set cnConnx = New ADODB.Connection
set rsDetails = New ADODB.Recordset

cnConnx.Open "Driver={SQL Server};Server=srv;Database=db;Uid=user;Pwd=pass;"
cnConnx.CommandTimeout = 5

rsDetails.Open "Table", cnConnx, adOpenDynamic, adLockPessimistic, adCmdTableDirect

On Error GoTo RecordLocked
rsDetails.Find "Username = 'User'", 0, adSearchForward

....
-------------------------
Everything works fine for the first user of the system, they get the data they want and can edit it. However, if a second user comes along and tries to edit the same record, the system hangs until user1 performs a Recordset.Update or Recordset.Close. The CommandTimeout is being ignored and the system never returns to the user.

I can't use adLockOptimistic as this doesn't give me the control the system requires. I have also tried using adCmdTable but this locks the whole table rather than just the found record!

Can anyone shed any light on this?

Thanks.

 

[Rank Am]

I think the problem occurs becuase you are keeping the recordset connected to the server, try using a disconnected recordset. This is inefficient in terms of network resources and prevents other users from loading data.
I use the server with Stored SQL procedures and open disconnected recordsets on the client machine through an ADO command object. When the user has finished editing the records I then return only the changed data to the server
The command timeout will be ignored if the connection take longer than 5 secs to be established, it has no bearing on how long the connection between server and client remains you should always explicitly close connection objects.
You also have to write code according to your requirements to prompt the user if two persons have the same record opened for editing at the same time and deal with any conflict.
Unfortunately this is a slightly bigger issue than just tweaking your procedure if you can't use optomistic locking

Regards

Jon