Gila,

I read this as "it's normal for a SQL DB to lock at the record level". I have a SQL Server 2005 backend connected to MS Access FEs using an ODBC connection. I learned today that no matter what I set for record locking in the front end, it is ignored by an ODBC connection.

I have a "work flow" piece. A user opens into a form a record from a main table (table 1) that pulls into a sub-form a second set of related record(s) from a second table. It is my assumption that the first person to open this record locks it (table 1 record) from another person opening it. This is not happening in my FE. I continue to get complaints that two people will open the same record.

I added to the table (and forms) a "checkout" bubble that the user checks as soon as the open the record (from the first table), saves the record and moves the curser to the lower portion of the form. Then told the system to ignore the record (move to the next) if the bubble is checked (-1 in the bit field). My users say that they check the bubble, but a second person can still open the same record. I cannot replicate the issue, but can see it in the data that one person marks the main record (table 1), but someone else marks the related records (table 2). Since a second person should not be able to get to the related records without opening the main record, I don't understand.

Is there a way to create record level locking in SQL that the query in the Access FE will be able to "see" and correctly open the next main record each time when the previous record is locked (or completed)? Or is it possible that there is a measurable delay in the connection from FE to BE back to FE with the update that the record has been locked?

Thanks for your help.