LockEdits Property

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Sets or returns a value indicating the type of locking that is in effect while editing.

Settings and Return Values

The setting or return value is a Boolean that indicates the type of locking, as specified in the following table.

Value	Description

True	Default. Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you call the Edit method.
False	Optimistic locking is in effect for editing. The 2K page containing the record is not locked until the Update method is executed.

Remarks You can use the LockEdits property with updatable Recordset objects.

If a page is locked, no other user can edit records on the same page. If you set LockEdits to True and another user already has the page locked, an error occurs when you use the Edit method. Other users can read data from locked pages.

If you set the LockEdits property to False and later use the Update method while another user has the page locked, an error occurs. To see the changes made to your record by another user, use the Move method with 0 as the argument; however, if you do this, you will lose your changes.

When working with Microsoft Jet-connected ODBC data sources, the LockEdits property is always set to False, or optimistic locking. The Microsoft Jet database engine has no control over the locking mechanisms used in external database servers.

Note You can preset the value of LockEdits when you first open the Recordset by setting the lockedits argument of the OpenRecordset method. Setting the lockedits argument to dbPessimistic will set the LockEdits property to True, and setting lockedits to any other value will set the LockEdits property to False.

See Also Bookmark property, Close method, FindFirst, FindLast, FindNext, FindPrevious methods, IsolateODBCTrans property, MoveFirst, MoveLast, MoveNext, MovePrevious methods.

Example

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")
    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") 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") 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

Example (Microsoft Access)

The following example opens a dynaset-type Recordset object and sets its LockEdits property to False (0). This enables optimistic locking so other users can change the database records at any time. Microsoft Access triggers a trappable error if the data changes before you use the Update method.

Sub ShowLockStatus()
    Dim dbs As Database, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
    rst.LockEdits = False
    On Error Goto ErrorLockEdits
    ' Enable editing.
    rst.Edit
    .                            ' Change records.
    .
    .
    rst.Update                ' Try to post changes.

ExitLockEdits:
    rst.Close
    Set dbs = Nothing
    Exit Sub

ErrorLockEdits:
        If Err.Number = 3197 Then
            MsgBox "Data changed by another user."
            Resume Next
        Else
            MsgBox "Some other error."
            Resume ExitLockEdits
        End If
End Sub

Example (Microsoft Excel)

This example locks the Customer recordset in the Nwindex.mdb database before updating the value in the CUSTMR_ID field of the first record with the value in cell A1 on Sheet1. Locking the recordset ensures that no other user can modify the record while it's being updated.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset
Sheets("Sheet1").Cells(1, 1).Value = "ACRIM"
databasePath = Application.Path & "\NWINDEX.MDB"
Set db = DBEngine.Workspaces(0).OpenDatabase(databasePath)
Set rs = db.OpenRecordset("Customer")
valueToAdd = Sheets("Sheet1").Cells(1, 1).Value
rs.LockEdits = False
rs.Edit
rs.fields("CUSTMR_ID").Value = valueToAdd
rs.Update
MsgBox "The new value of CUSTMR_ID is " & rs.fields("CUSTMR_ID").Value
rs.Close
db.Close