LockEdits Property

Applies To

Dynaset-Type Recordset Object, Recordset Object, Table-Type Recordset Object.

Description

Sets or returns a value indicating the locking that is in effect during editing.

Settings and Return Values

The setting or return value is a Boolean expression that indicates the type of locking. The data type is Boolean.

The possible settings or return values are:

Setting/Value	Description

True (-1)	(Default) Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you use the Edit method.
False (0)	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 dynaset- and Table-Type 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 Update method while the page is locked by another user, an error occurs. To see the changes made to your record by another user, set the Bookmark property of your Recordset object to itself. This approach results in your changes being lost.

When working with 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.

See Also

Bookmark Property; Close Method; FindFirst, FindLast, FindNext, FindPrevious Methods; IsolateODBCTrans Property; MoveFirst, MoveLast, MoveNext, MovePrevious Methods.

Example

This example opens a Dynaset-Type Recordset and sets its LockEdits property to False. This enables optimistic locking so other users can change the database records at any time. Your application triggers a trappable error if the data changes before you use the Update method.

Sub ShowLockStatus()
Dim dbsNorthwind As Database, rstCustomers As Recordset

Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set rstCustomers = dbsNorthwind.OpenRecordset("Customers", dbOpenDynaset)
rstCustomers.LockEdits = True
OnError Goto ErrorHandler
rstCustomers.Edit    ' Start editing.
    On Error GoTo 0
    rstCustomers.CancelUpdate
    rstCustomers.Close
    dbsNorthwind.Close
    Exit Sub

ErrorHandler:
    If Err = 3197 Then
        Debug.Print "data changed"
        Resume Next
    Else
        Debug.Print "some other error"
        Resume Next
    End If
End Sub

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 Database variable that points to current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset)
    rst.LockEdits = False
    On Error Goto ErrorHandler
    ' Enable editing.
    rst.Edit
    .                    ' Change records.
    .
    .
    rst.Update            ' Try to post changes.
    rst.Close

    Exit Sub
    ErrorHandler:
        If Err.Number = 3197 Then
            MsgBox "Data changed by another user."
            Resume Next
        Else
            MsgBox "Some other error."
        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 modifies the record while it is 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