databasedev.co.uk - database solutions and downloads for microsoft access

Multi-User Application – Record Locking

Record locking occurs in two environments, the Microsoft Access Interface and the ADO Recordset Object. We have already established that record locking is in effect mostly in network shares. But what is record locking exactly? Well it is a mechanism that locks records when they are being edited by a particular user so that other users cannot edit them.

Access Interface

To set the record locking options in the Interface simply go to the Advanced Tab in the Options dialog box and set the options under the Default Record Locking group box:

As you can see from the image above there are three types of record locking options:

• No Locks - When a user has saved the changes to a record that has been edited, then and only then does Microsoft Access lock it. The record is therefore not locked while the user is actually editing it. The No Locks option is best used when there is a slim chance of more than one user editing the same record. This method of locking is also referred to as optimistic locking.
• All Records - As the name suggest, this option locks up the entire table while records are being edited. Of all the options, this one is the safest for obvious reasons. The disadvantage of this method of record locking is that multiple users will be unable to access the database for long periods of time.
• Edit Record - This option is the most logical and I think the most commonly used in a multi-user environment. The Edit record option ensures that Access locks a record the moment a user begins the editing process and then the lock is released when the user saves or otherwise abandon the edits. This method of locking is also referred to as pessimistic locking.

Like many of the other settings on the Advanced tab of the Options dialog box, the Default Record Locking setting applies to the Access application as a whole, and not to the individual database application that is opened.

When a user tries to edit a locked record the following message is displayed:

A user can either save the changes (even if the particular record has been changed by another user), copy them to the Windows clipboard or abandon the changes.

ADO Recordset Object

When setting the LockType property of the ADO Recordset, you use one of the following values:

• adLockReadOnly to lock the entire recordset
• adLockPessimistic to lock a record while it is being edited
• adLockOptimistic to lock a record while it is being saved
• adLockBatchOptimistic to lock out users while records are updated in batch mode

Once we’ve decided on what type of locking we want to use in our code, we simply specify it as a parameter to the Open method of the recordset object or assign it to the recordset object’s LockType property before opening the recordset. Take a look at the following example code which shows how to use the LockType property as explained:

Dim dbcon As ADODB.Connection
Dim recrdst As ADODB.Recordset
Set recrdst = New ADODB.Recordset
recrdst.CursorType = adOpenKeyset
recrdst.LockType = adLockOptimistic
Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\generic.mdb;"

recrdst.Open "SELECT * FROM names", dbcon
Set Me.Recordset = recrdst

recrdst.Close
dbcon.Close
Set recrdst = Nothing
Set con = Nothing

End Sub

In the code above, the LockType is set before the recordset is opened. The code below shows how to specify the LockType as a parameter to the open method:

Dim dbcon As ADODB.Connection
    Dim recrdst As ADODB.Recordset 
    Set recrdst = New ADODB.Recordset
    recrdst.CursorType = adOpenKeyset
    recrdst.LockType = adLockOptimistic
    Set dbcon = New ADODB.Connection
   dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\generic.mdb;"
       
    recrdst.Open "SELECT * FROM names", dbcon ,adOpenKeyset, adLockOptimistic
    Set Me.Recordset = recrdst

    recrdst.Close
    dbcon.Close
    Set recrdst = Nothing
    Set con = Nothing

End Sub