Record Locking in VB6

[Sprowler]

Hello everyone! Please help!

I have a VB6 app connected to an Access97 mdb file. The mdb is set to shared use. I want my VB6 app to allow multi-user access, but when a record is in Edit mode Error 3260 occurs saying the record is locked by another user if I try to edit the same record. I have trapped the error code, but there is a delay till VB returns the trapped code into a msgbox. Is there a better (and quicker way) to do tell the user that the record is currently locked, and (less important) is it possible to trap the username who has locked the record?

Your help is greatly appreciated.

 

[vb5prgrmr]

ADO or DAO? If dao look up the recordset's lockedit property. If ado then check out the recordset's locktype property.

You can parse out the Err.Description (in some occasions) to retrieve the machine name that has it locked.

Good Luck

 

[Sprowler]

Thanks for your help vb5prgrmr. I'm using DAO and I have tried setting lockedits to true, but it seems to lock the whole database, i.e. when I try to edit another record I still get the error message. I am setting the lockedits on the datacontrols recordset, is this the reason, i.e. am I setting a lock on the entire recordset? Do I need to connect with Database and Recordset objects instead? I know this is very lazy, but I'm new to VB6, so a code example would be very much appreciated.

Thanks again.

 

[vb5prgrmr]

The procedure that you should use is to ...

1. check if record is locked and if not lock it
2. do your business with record
3. release locks and record (you may need to do a movenext if using the edit method)

Good Luck

 

[Sprowler]

Thanks again for your help. What isn't clear to me is how to do 1 and 3. When I use LockEdits then it locks the entire recordset. How can I just lock/unlock the current record to allow other users to edit other records in the recordset? If you can provide or point me to a code example I'd very very grateful.

 

[vb5prgrmr]

From Help ...
[tt]
This example demonstrates pessimistic locking by setting the LockEdits property to True, and then demonstrates optimistic locking by setting the LockEdits property to False. It also demonstrates what kind of error handling is required in a multiuser database environment in order to modify a field. The PessimisticLock and OptimisticLock functions are required for this procedure to run.

Sub LockEditsX()

Dim dbsNorthwind As Database
Dim rstCustomers As Recordset
Dim strOldName As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
Set rstCustomers = _
dbsNorthwind.OpenRecordset("Customers", _
dbOpenDynaset)

With rstCustomers
' Store original data.
strOldName = !CompanyName

If MsgBox("Pessimistic locking demonstration...", _
vbOKCancel) = vbOK Then

' Attempt to modify data with pessimistic locking

' in effect.
If PessimisticLock(rstCustomers, !CompanyName, _
"Acme Foods&quot Then
MsgBox "Record successfully edited."

' Restore original data...
.Edit
!CompanyName = strOldName
.Update
End If

End If

If MsgBox("Optimistic locking demonstration...", _
vbOKCancel) = vbOK Then

' Attempt to modify data with optimistic locking
' in effect.
If OptimisticLock(rstCustomers, !CompanyName, _

"Acme Foods&quot Then
MsgBox "Record successfully edited."

' Restore original data...
.Edit
!CompanyName = strOldName
.Update
End If

End If

.Close
End With

dbsNorthwind.Close

End Sub

Function PessimisticLock(rstTemp As Recordset, _
fldTemp As Field, strNew As String) As Boolean

dim ErrLoop as Error

PessimisticLock = True

With rstTemp
.LockEdits = True


' When you set LockEdits to True, you trap for errors
' when you call the Edit method.
On Error GoTo Err_Lock
.Edit
On Error GoTo 0

' If the Edit is still in progress, then no errors
' were triggered; you may modify the data.
If .EditMode = dbEditInProgress Then
fldTemp = strNew
.Update
.Bookmark = .LastModified
Else
' Retrieve current record to see changes made by
' other user.
.Move 0

End If

End With

Exit Function

Err_Lock:

If DBEngine.Errors.Count > 0 Then
' Enumerate the Errors collection.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
PessimisticLock = False
End If

Resume Next

End Function

Function OptimisticLock(rstTemp As Recordset, _
fldTemp As Field, strNew As String) As Boolean

dim ErrLoop as Error

OptimisticLock = True

With rstTemp
.LockEdits = False
.Edit
fldTemp = strNew

' When you set LockEdits to False, you trap for errors
' when you call the Update method.
On Error GoTo Err_Lock
.Update
On Error GoTo 0

' If there is no Edit in progress, then no errors were
' triggered; you may modify the data.
If .EditMode = dbEditNone Then
' Move current record pointer to the most recently
' modified record.

.Bookmark = .LastModified
Else
.CancelUpdate
' Retrieve current record to see changes made by
' other user.
.Move 0
End If

End With

Exit Function

Err_Lock:

If DBEngine.Errors.Count > 0 Then
' Enumerate the Errors collection.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
OptimisticLock = False
End If

Resume Next

End Function
[/tt]

Good Luck

 

[Sprowler]

Thanks for your time vb5prgrmr. I did find this and will try it. I think my problem is is that i am using Page locking which is locking more than just the current record. If there is no way round this, I can only think that I need to open a new recordset with just the record to be edited in it to carry out the edit, so that I don't affect other records in the recordset(?)

Your help is greatly appreciated.