I created the following code sample to test Record/Row Locking in VB6 via ADO and SQL Server 2000. I ran two instances of the code, the 2nd instance indicate that the record is lock. Both instances are functioning correcting when referencing the PUBS database/tables (microsoft sample database). But when I change the reference to a database/table that I created, the 2nd instance does not indicate that the record is locked. Since my code sample/test works with the PUBS database I can only assume that I missing something on the SQL Server side in regards to the database/table that I created.
Any ideas what I am doing wrong or missing?
Or any suggestions of a better way to lock a record and indicate/tell if a record if locked?
Dim rs1 As New ADODB.Recordset
Private Sub Form_Load()
Dim ConnString As String
Dim cn As New ADODB.Connection
In the world of SQL servers, the concept of "locking a record to edit it" really does not exist. Using so-called "updatable query views" is really not a good idea. In many installations it does not work or is not allowed.
SQL servers don't enjoy having locks around and work hard to avoid them. Instead of using "pessimistic" locking (i.e. "I assume someone will edit this record while I'm editing it, so I'll lock it"), they use "optimistic" ("I bet that a user won't edit exactly the same record, so I'll check to see if someone did so only at the instant that I go to save these changes"). You can see that optimistic locking avoids the overhead of keeping and checking for lots of locks. Locks only exist for microseconds. But you have to anticipate the possibility that, indeed, your optimism might be misplaced: in that case, the attempt to save-changes will fail.