I am trying to create a transaction using an ADO connection to a MS SQL 2000 db. I just want to set the status flag on a record so other instances of the application will know that the record is being processed and yet will rollback if the application suddenly stops (crash or power failure).

If I start a transaction on the connection by either the ADO connection object .BeginTrans method or by executing "Begin Transaction" everything looks OK. Next I try either an "Update Test Set Status = 'P' Where TestID = 2" or open a new ADO recordset using "Select * From Test" and then use the .Find method ("TestID = 2") on the recordset, then .Fields("Status") = "P", and finally .Update. At this point the Test table is locked? If I try to use SQL Query Analyzer to "Select * From Test" or another application tries to open an ADO recordset against the Test table, it sits until it times out. If I issue the appropriate .CommitTrans or "Commit Transaction" command the secondary application (SQL Query Analyzer or VB) immediately gets access to the table and continues (assuming a timeout error hasn't occured yet).

I'm pretty sure SQL 2000 and ADO support record level locking, but cannot find any examples of how to implement this. Also, if trying to reproduce this behavior, note that the value of the Status field must actually change during the update or the lock never happens. Any help is much appreciated.

Thanks,