Pessimistic Locking

I have 2 connections to the same database which both have the following properties:
Mode = adModeShareExclusive and IsolationLevel = adXactReadCommitted;

I then run a "SELECT * FROM TABLE_A FOR UPDATE" query for each connection by creating a recordset with a dynamic cursor and pessimistic locking in an attempt to generate some kind of an error stating that the resource is locked so I can handle it as I wish. Unfortunately, this actually succeeds and the only error I can generate is a "time out" when trying to move through the record sets using MoveFirst, etc.

Does anyone know of a way that I can exclusively lock a record when performing an SQL statement so that it causes an error when another connection/user tries to access it(read or write)? Any information would be greatly appreciated.

I can be e-mailed at atrolio@erggroup.com if you would rather not post to this forum.

Kind regards,
AT.

[Iouri]

If you are using Access you cannot lock a record. You can lock a page (adLockOptimistic) or a table (adLockPessimistic). Also you can lock the whole database by using Mode = adModeShareExclusive. I used the following trick to lock the record. I created an extra fileld in the table and changed the value every time when somebody accessed the record. After record was released the value was changed back.

I hope that will help

Iouri Boutchkine
iboutchkine@hotmail.com