X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Can An Access Database Be Edited By Multiple Users?    
Toon VC
post Mar 25 2020, 11:51 AM

Posts: 2
Joined: 25-March 20


We work with a large Access database and we want a few users (max. 5 to 6 at the worst) to be able to edit tables at the same time by using forms. We have split the database into front end and back end. The BE is placed on a NAS and each user has an offline copy of the FE on his own desktop.
All users have full permissions and we switched the options (client settings -> Advanced) to the following settings:
Default open mode: Shared
Default record locking: Edited record
Open databases by using record-level locking: yes
Encryption method: use legacy encryption (good for multi-user databases).

In the forms we also changed the property ‘record locks’ to ‘edited record’.

What we want to achieve is that when a user change a certain record in a form, other users can’t change the same record until the changes of the first user are saved. We also want to see a lock icon in the selector bar, as mentioned in this forum:

For the moment we have done several tests, but we are unable to generate any “Writing conflict” when implementing the above, contrary to the numerous reports on the opposite behaviour one can find reported in the internet and the documentation of Access. The first user changes a record and the second user is still able to change it, without any particular notification in the sense of a selector bar icon or a “writing conflict”, and the user that closes the database the latest takes it all (all changes by the first user are overruled).

Can anyone explain me what we are doing wrong?
Go to the top of the page
post Mar 25 2020, 12:14 PM

Posts: 973
Joined: 21-September 14
From: Tampa, Florida USA

I suggest you don't use any record locking. Users will still get a message if a record is open by another user, but still be able to read the unedited record while the other user's form that opened it is dirty. By using the default "No Locks" settiing you will also avoid having to manually resolve genuine deadlock issues and extra coding.

Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
post Mar 25 2020, 01:07 PM

UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill

FYI, cross posted on AWF



David Marten
Go to the top of the page
post Mar 25 2020, 01:09 PM

UA Moderator
Posts: 78,103
Joined: 19-June 07
From: SunnySandyEggo

Hi Toon. Welcome to UtterAccess! welcome2UA.gif

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Mar 25 2020, 01:24 PM

Posts: 973
Joined: 21-September 14
From: Tampa, Florida USA

QUOTE (cheekybuddha)
FYI, cross posted on AWF

Cross posted in AccessWorld

Why bother with all this cross posting info and not just answer the question, like I previously did?
This post has been edited by FrankRuperto: Mar 25 2020, 01:30 PM

Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
post Mar 29 2020, 06:25 AM

UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill

>> Why bother with all this cross posting info and not just answer the question, like I previously did? <<

Several folk here look at more than one forum, Frank.

I'm just giving our members a heads-up that the question might have been already answered elsewhere so they don't feel like they've wasted their time writing a response that's already been offered on another board, and when they could have been helping on another problem.

For the record, I have no issue with anyone posting the same question on different fora, in fact I think it's quite a sensible strategy if you are looking for an answer to your question. It's not a problem for me either that posters don't say that they have also posted on other boards; why would they think it would be necessary (unless it is something they do repeatedly!)? It's not my intention to reprimand or embarrass the OP.

However, it is mildly irritating when you find you've spent half an hour considering and trying to work out a solution to a problem only yo find out that the answer was already given elsewhere, So I try and give everyone a heads-up if I can, and hope others will do the same for me!

[ Do note, others reading, that posting the same question on multiple boards within UtterAccess is against the forum rules and is unlikely to get you an answer any quicker! ]





David Marten
Go to the top of the page
post Mar 29 2020, 02:00 PM

UtterAccess VIP
Posts: 3,062
Joined: 12-April 07
From: Edmonton, Alberta Canada

Your settings look ok. (maybe).

But, lets outline how this works.

This can be confusing.

Unfortunately, how this works and is setup is a source of confusing for the Access community, and it been this way for a long time!

The “over all” settings for the whole project are just that – an overall DEFAULT setting. If you change it, then all NEW FORMS you create will have this edit record lock setting set.

You are on the right track - the settings for a form are how this is done.

The overall settings WHEN you change them, it does NOT effect the forms!!!

If you play or change or mess with these settings? You will see ZERO CHANGE in your application as it stands.

The setting I speak of is this:

File ->options->Client settings

No locks
All records
Edited Record

I VERY MUCH recommend that you do NOT change the above 3 first settings. However, if you want the default for all new forms crated to be Edited Record, then you can change the above first 3 settings.

The first 3 settings DOES NOT change how your application will function!!!!

The 4th option in above Open database by using record level locking? I will address that feature in a bit.

Ok next up:

Form locking and data edits.

As noted, the locking settings are done on a form by form basis.

So the settings you mess with, and HAVE to change are this:

So, now let’s go through how the settings work for a form.

If you do nothing (or have the form set to none for locking)


User #1 starts editing record #1

User #2 starts editing record #1

User #1 done – exit. Record saved.

User #2 – still working away on record #1

Now, when user 2 attempts to save/exit, they will get the dreaded message that the record has been modified by another user.

Ok, now, if we want to actually lock the record for user #1, and PREVENT user #2 from starting to edit and work on record #1?

You HAVE to turn on that locking for the GIVEN form. This is a form by form setting!

Ok so this setting:

Open up the forms property sheet - you see the options above, and change the record locking to edited record.

Ok, now you will get this:

User #1 starts editing record #1

User #2 starts (attempts) to edit Record #1


If they type on the keyboard it will go beep for each key pressed. They cannot START an edit.

If the record selector is displayed, then they will see this:

Note that this is the ONLY indication that the record is locked by another user.

The lock icon WILL NOT show until such time they attempt to edit the record. In normal cases, the record selector will show “dirty” record.

If the lock icon shows the any keypress on the keyboard will go "beep" and you can't type anything into the form.

So the record is locked an in use by another user, they get the record lock icon.

If two users are looking at the same record and NEITHER have started editing?

No problem. The instant the first user starts to edit, then the “dirty record” icon will appear in the record selector. At this point, no other user can edit the record. (Their keyboard will beep for each keypress, and the lock icon will appear).

However, the 2nd user does nothing, they DO NOT get any indication the record is locked. They must attempt to edit to get/see the lock icon.

So, you can’t just flip on record locking for the whole application – it will NOT change how your application will work.

You MUST set this on a form by form basis.

Introduction of the true row lock feature.

Using and turning on the true row lock feature is OPTIONAL and often not required. After all, from access 2.0, 95, 97, and the default from 2000 onwards had this feature turned off by default and MOST developers did not care, did not know or even change this setting. We talking a period of 10+ years where this issue was for the most part ignored.

However, while the true row lock setting is not required in most cases?

Well, if the database has rather small records, then you MIGHT find in some cases that:

User #1 starts editing record #1
(Form locks the records – no one else can edit)
(Lock icon will appear to other users)

User #2 now attempts to edit record number #2

If the records in the database are rather small, then both records may well on the same data page.

And this will cause user #2 to see the lock icon described above. But the user is on a DIFFERENT record. You more often see this issue in sub forms or forms that edit small records. (However if the main form record is locked, then you often ok, and ZERO need to lock the sub form exists here (since users can’t edit the same main form record anyway – can they???)).

ONLY ****IF**** if you start to receive this locking problem often, or more often then you like?

Well, THEN you can consider turning on the [x] row locking feature. Often systems can run for years without much of an issue or problem.

So, you do NOT have to turn on the “use row locking feature” to have forms lock a record.

However, if you start to see too many “locks” between users WHEN in fact they are editing a different record?

Well, the you can bite the bullet here, and turn on that true row locking feature.

What does this feature actually do?

What the feature does is in fact expand all records you edit to the size of one database page. And Access (JET/ACE) DOES have true page locking at a very low level. Since all records are now expanded to one full database page size, then the result is true row locking in a “fake way” since all edits now expand the record to the size of one database page.

However there is a performance cost, and I tend to call this:

Please turn on the Access bloating feature! (Since that is the downside – huge increase in bloating and file growth).

And again:
All data edits you use, EVEN without row locking turned on (forms setting) will use this “expansion” trick to achieve row locking (but it don’t matter if you use Edit record locking or not (that you now know is set in the form)– it is a on or off feature for ALL YOUR edits regardless). This is a system wide setting. And worse it is an Access setting, not an application setting.

So you can well use and turn on locking on a form by form basis and ZERO behaviors will occur to the JET/ACE database engine. And this includes bloating issues.

I hope this clears up how setting a form to lock an edit record in Access.

You can freely change this setting (edit locks at the form level), and it will not affect database bloat nor will the forms setting change any other behaviors in your access application (in this context).

So, I would turn on (for the forms you need) the locking (edited record). If operations are seeing too many “locks” from users working on different records then you can turn on the true row locking feature. It has a big cost in terms of file bloat, but it does result in true row locking and will eliminate the “false” positives for locked records.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Mar 29 2020, 03:03 PM

UtterAccess VIP
Posts: 7,342
Joined: 30-June 11

Great post Albert! hat_tip.gif

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
Toon VC
post Apr 1 2020, 03:24 AM

Posts: 2
Joined: 25-March 20

Thank you for all your valuable replies.
Unfortunately, until now, we didn't manage to get the lock icon in the selector bar.
We will try to run some further tests and I will keep you posted if we find a solution for our problem.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    28th May 2020 - 10:41 AM