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?

General Declarations:
Dim rs1 As New ADODB.Recordset

Private Sub Form_Load()
Dim ConnString As String
Dim cn As New ADODB.Connection

On Error GoTo ErrLock

'ConnString = "driver={SQL Server};server=;database=TestDB;uid=sa"
ConnString = "driver={SQL Server};server=;database=PUBS;uid=sa"

cn.CursorLocation = adUseServer
cn.ConnectionString = ConnString
cn.Mode = adModeShareExclusive
cn.Open

Dim sql As String
'TestDB SQL
'sql = "SELECT * " & _
' "FROM ClmRecs AS clm " & _
' "WHERE (clm.claim_id = 630);"

'PUBS SQL
sql = "select title_id, title, price, ytd_sales from titles where title_id = 'BU1032'"

Set rs1.ActiveConnection = cn
rs1.Open sql, cn, adOpenKeyset, adLockPessimistic, adCmdText
' Edit to Lock Record
rs1(0).Value = rs1(0).Value

Exit Sub

ErrLock:
If cn.Errors.Item(0).NativeError = 1222 Then ' Lock Timeout
MsgBox "Row is locked by another user"
cn.Errors.Clear
End If
Resume Next
End Sub

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.