Hi!

ADO cannot do more than SQL Server can do. ANY modification during transaction on SQL Server cause record locking, and you cannot do anything to that. RTFM: transaction should last as little time as possible, this is a rule in database programming. Entire application architecture should be built correct way that account this rule. Otherwise users will wait for long time until someone else finish the transaction. And no matter what you use, ADO or native VFP views.

HTH.

>Hello,
>I still need to look at the record that is locked. The end goal is to have multiple users share the table. When a user is ready to process the next record they should look for records where the status does not equal "P". I beleive this is possible as ADO has a property called IsolationLevel. One of the values that can be set is "adXactBrowse - Indicates that from one transaction you can view uncommitted changes in other transactions." I am assuming I need to do something with this. SQL Query Analyzer must not allow browsing uncommitted changes? Thanks for the help.
>
>>Hi!
>>
>>Select * FROM Test - this query also tries to access the record just updated in transaction, so it will wait. Try select * from Test where TestID = 3 or similar update command - it should work.
>>
>>>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,