Testing a Record for Locking Status
Often, you want to determine the locking status of a record before you
attempt an operation with it. By utilizing pessimistic locking and trying to
modify the record, you can determine whether the current row is locked. The
code looks like in following Listing

Listing - Determining Whether a Record Is Locked Before Editing It

Sub TestLocking()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim boolLocked As Boolean

Set cnn = New ADODB.Connection
cnn.ConnectionString = " Provider=sqloledb;" & _
"Data Source=(local);Initial Catalog=pubs;uid=sa;pwd="
cnn.Open

Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
rst.CursorLocation = adUseServer
rst.Open "Authors", Options:=adCmdTable

boolLocked = IsItLocked(rst)
MsgBox boolLocked

End Sub

Function IsItLocked(rstAny As ADODB.Recordset) As Boolean
On Error GoTo IsItLocked_Err
IsItLocked = False

With rstAny
.Update
End With
Exit Function

IsItLocked_Err:
If Err = -2147467259 Then
IsItLocked = True
Exit Function
End If
End Function
The TestLocking routine sends its recordset to the IsItLocked() function,
which receives the recordset as a parameter. It then issues an Update method
on the recordset. If an error occurs, the record is locked. The error
handler sets the return value for the function to True.

Hopefully this will solve your problem

"Bart Steur" <bstnewsxs4all.nl> wrote in message
news:Odb1%23Lw7CHA.2248TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I have an application tah used a SQL Server Database. Multiple users can
run
> that application simultaniously.
>
> When a user starts editing a record in the database in Table ABC, how can
I
> tell another user when he or she is trying to edit the same record that
the
> record already is being edited.
>
> I'm using VB6 SP5 with SQL Server 7.0 with ADO2.7.
>
> Currently I open the recordsset pessimiticly. I hoped that by opening this
> way I would receive an error from the provider telling me that the record
is
> already locked. But that didn't happen.
>
> Can anybody tell me how to solve this.
>
> Thanks.
>
> Bart
>
>