The Linux Page

MS-Access Record Locking

The MS-Access system has a limited way to lock records...

This is done within what is called the Recordset object. That object has a field called LockType.

Available lock types are Optimistic, Pessimistic, Table. The last one prevents all access from anyone else (read/write lock). Pessimistic locks let other users read the locked data, but not write (write-only lock). Optimistic locks do nothing (in other words, no locking.)

The following is a synopsis for this locking feature:

  Dim rcd As Recordset ' This does not in MS-Access 2007 because the DAO, etc. return a RecordSet27
  rcd.LockType = adLockPessimistic

This is nice, but when you want to open a new recordset using the OpenRecordset() function, whatever you set your recordset variable to is totally ignored. Actually, the OpenRecordset() accepts parameters that define the locking method at that point.

The method is described here:

So for instance, if you want to deny write permission to others, you use dbDenyWrite as the 3rd parameter.

And the 4th parameter defines the lock type, whether you're an optimistic or a pessimistic.