Go Premium for a chance to win a PS4. Enter to Win


Record Locks

Posted on 2014-03-09
Medium Priority
Last Modified: 2014-03-16
I have noticed a form property called Record Locks that can be set to "No Locks" In reality does this have any effect when in a multi user environment?

Question by:DatabaseDek
  • 5
  • 3
  • 3
  • +2
LVL 85
ID: 39915750
Yes, they can have an impact.

For example, if you select the "All Records" lock method, then Access will lock all the records in the form's recordsource (including any related tables). It's very restrictive, and is generally not used for day-to-day operations.

"Edited Records" locks the record the user is editing, as well as any other records on the same "page" as that record. A "page" is the location on disk where the data is stored, and depending on various settings, could encompass several records other than the one you're currently editing.

"No Locks" essentially means that any user is free to edit any record, but if two users edit the same record you'll get the "Another user has changed this record blah blah" message.

Generally speaking you'd use the Edited Records or No Locks setting, depending on how your users interact with the system.

Of course, if you're working with data stored in other databases (like a SQL Server database, for example) then the locking may be determined by that database.

Note too that there are record lock settings in Access Options - Client Settings - Advanced, as well as other settings that can impact multiuser databases.
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39915843

The record locks setting controls when a lock is placed.   With "Edited Records", it's placed as soon as the user makes the record dirty (when they make the first change) and is not released until the record is updated.

"No Locks" is a misnomer; it's means no edited record locks, not that there is no locking going on.   In this case, the lock is placed just before the record is updated, then released after that.

These equate to pessimistic and optimistic  locking respectively.

With the first, two users can't over-write each others changes or loose edits they have done, but it can lead to concurrency issues (who can get at what when).

Locks are placed either at the record level or page level depending on how you open the database.

 In JET, record level locking was an add-on, so it doesn't work all that well.

 But using edited records long with page level locking can tie up a lot of records.


Author Comment

ID: 39916326
That's very clear thank you both.

If you wanted the least hassle in a multi user environment would you suffer the "Another user has changed this record" or would you suffer the potential problem of another user starting to edit a record and before saving decides to go to lunch locking everybody out of that record?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 85
ID: 39916526
"Least hassle" would be no locks - as JimD wrote, this is often known as optimistic locking, and is generally the one that causes the least interference with users.

Just be aware that "no locks" doesn't really mean "no lock" ...
LVL 58
ID: 39917299
um, I take the opposite view; I'd use Edited Record Locking (pessimistic) with Record Level locking.

However I would not rely on the record level locking on JET.  What I do typically is leave it set for Edited Record, and then if I have concurrency issues, I pad the records out past half a page.

 Since JET does not allow a record to span a page, this ensures one record per page and thus I have "record level locking" with the added benefit that I can enable/disable this on a table by table basis.

  Generally, I find that I need to do this on only a couple of the main tables in order to avoid problems.

  You can approach it from the other end as well though, which is to use no locks and see if you get a lot of over write conflict dialogs.  Certainly no locks is less overhead on the DB's part.

LVL 40

Assisted Solution

PatHartman earned 1000 total points
ID: 39917614
I use optimistic locking (no locks).  I seem to have users who start an edit and then go to lunch or take a phone call leaving the record (and others in the page) locked until they get back.

There is no good way to release locks once they are acquired.  Nor with ACE is there a good way to find out who is the culprit.  You have to get everyone to exit the application and then delete the .lccdb or. ldb file associated with the BE.  If you can't delete the lock file (which happens way too often), you will need to reboot the server to get rid of the DOS file locks and then you can delete it.

Of course, most of my applications don't have heavy data entry users and there is little chance of real contention for the same record.  To circumvent this problem in one app where there would have been contention, I implemented my own "lock" process.  The users were working off a to do list so it was possible that multiple users would select the "next" item.  So, I added a lockedBy and LockedDT field.  When a user selected a record, I updated the lock columns (which were displayed on the to do list) so that others could see that someone was already working on the record.  I also displayed them on the form and locked it to make it obvious that another update was in process.  When the user left the record, I removed the lock information to free up the record.  I also included update queries that would "free" all records to handle problems caused by lost network connections and power outages.
LVL 58
ID: 39917628
<<If you can't delete the lock file (which happens way too often), you will need to reboot the server to get rid of the DOS file locks and then you can delete it.>>

 Actually, you don't need to reboot.  You just need to close the open file handle.

<<I implemented my own "lock" process. >>

  That's another approach as well.  In fact I wrote up an article on it:


  That often is a better approach because beside the record updating itself, it is sometimes a process based one like the example Pat gave.

LVL 85
ID: 39917656
I guess it depends on what is meant by "less hassle" ...
LVL 58
ID: 39917732
<<I guess it depends on what is meant by "less hassle" ... >>

 Excellent point....any of the approaches carries baggage.   It's one of those things where there really is no right or wrong answer.

  It's good just to know the various techniques and then decide what's best for the situation you have.

LVL 75
ID: 39918253
Unless you implement the ADO technique described Here ... Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60 ... you are not going to get true Row Level Locking in a multi-user database (with front end and back end).  

I've been using this at work in several multi-user app for a couple of years, and it seems to work quite well, especially getting around one user locking a 'page' of records - and user working in a 'nearby' record getting the 'Record locked by another user ...." error.


Author Comment

ID: 39931420
To eliviate the "I'm in the middle of editing a record but the dinner bell has gone so I'm off!" people" would it not be a good idea to simply have a refresh on a timer?

Excellent articles, by the way, Jim
LVL 58
ID: 39931552
<<To eliviate the "I'm in the middle of editing a record but the dinner bell has gone so I'm off!" people" would it not be a good idea to simply have a refresh on a timer?>>

  Many apps do have some type of in-activity timer and undo a record if something is in progress for just that reason (or log out the user).

<<Excellent articles, by the way, Jim >>

 Wish I could find the time to write more.

LVL 40

Expert Comment

ID: 39931571
If you elect to use the timer event you will need to be extremely careful during development.  I have all my timers set to turn off if I log in as myself.  So, when I am testing the timer functionality, I must log in under a different ID.

The reason I do this is because when I am in the process of making changes, I do not want to put any object into design view and modify it while there is a timer event active on another open form.  So, you either have to be completely disciplined and shut down all objects except the one you are editing or make sure there is no timer event running while you are editing.  Corruption is the almost certain result if you make design changes while a timer event is active.

Author Closing Comment

ID: 39932640
Great stuff as usual.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question