My problem is connected to multi-users accessing to one form and editing data on it simultaneously.
The root of the problem is that, when several users are connecting to the DB, the last one who connects obtain a "priority". As a result, whenever one of the users is editing a field in form and save the changes, after what happens synchronization of data stored on the server for each user except the one with "priority". But if prioritized user ( the last connected ) is making a change, everybody will synchronize with his change.
Currently there are 5 active DB users, the database is split and shared, so the tables are stored on virtual driver and everyone have access to it, each user has a local copy of an Access database file that contains links to the tables, along with queries, forms, reports, and other database objects. There are more than 70 tables, and part of them holds over a thousand of records. Commonly, work related to DB focuses on data entry.
As a first step, lock system were implemented, we used lock on "Edited record", which seems to work properly, so no matter whether it's prioritized user or not, as soon as that user start to change data in a form he is locking it, but after that user made the change and saved data if he don't have the "priority" his change won't be applied to prioritized user.
In order to clear up the problem, i will simulate a problem situation: User1, User2, User3 accessing DB in the appropriate sequence. User3 gaining "priority". Every user accessing form of the same PO, and trying to change Supplier field. User2 was the first one to make the change , so he is locking the record. After the change was made and data was saved. Synchronization happens to User1 and he receives changed data about supplier, but User3(prioritized) keeps the old data. Then we repeat the process , but now User3 are making changes. At the end, after synchronization, both User1 and User2 receives newest Supplier data.
As an addition I'm sharing the link which contain the video of the problem:
.Sadly the quality is poor so as an explanation to what's going on: firstly, the record status is changed by 'Prioritized' User2 from "Editing" (blue) to "Validation" (yellow) and saved after what User1 synchronizing the data and receives the change, after that User1 is changing and saving the data but nothing happens on synchronization for User2, so at the end 2 users stay with 2 different record versions.
Also, sometimes when ‘uprioritized’ user ( User1) are making the changes and User2 keeps an old unchanged data, after User2 decide to make synchronization either by button or just exit from the form or the automatic synchronization happens, old unchanged data becomes saved and each user receives it.
So as a conclusion to all of that , MS Access Optimistic Concurrency Control (Optimistic locking), to be more precise Last in Wins approach is not working properly.
After additional research, I found out that on each user entrance .laccdb file responsible for "Microsoft Access Record-Locking Information" each user gains Admin status, may it be the cause of ‘priority’ problem?
Is there a away to solve that issue without implementing custom locking or Optimistic Concurrency Control approach, such as timestamps use?
For additional information and suggested solutions, I've posted same issue on Microsoft forum. I didn't mean to harm anyone's feeling, by doing that, I found each reply very useful, and the only reason for doing that is to gain as much information as possible, since I'm really new to Access and DB overall, and there is so much information to be covered.
P.S. Sadly I can't insert a link, since I've been receiving "inadmissible context" error, thankfully XPS35 mentioned that link.
Solution: The root of the problem is that, when several users are connecting to the DB, the last one who connects obtain a "priority". As a result, whenever one of the users is editing a field in form and save the changes, after what happens synchronization of data stored on the server for each user except the one with "priority". But if prioritized user ( the last connected ) is making a change, everybody will synchronize with his change.
Currently there are 5 active DB users, the database is split and shared, so the tables are stored on virtual driver and everyone have access to it, each user has a local copy of an Access database file that contains links to the tables, along with queries, forms, reports, and other database objects. There are more than 70 tables, and part of them holds over a thousand of records. Commonly, work related to DB focuses on data entry.
As a first step, lock system were implemented, we used lock on "Edited record", which seems to work properly, so no matter whether it's prioritized user or not, as soon as that user start to change data in a form he is locking it, but after that user made the change and saved data if he don't have the "priority" his change won't be applied to prioritized user.
In order to clear up the problem, i will simulate a problem situation: User1, User2, User3 accessing DB in the appropriate sequence. User3 gaining "priority". Every user accessing form of the same PO, and trying to change Supplier field. User2 was the first one to make the change , so he is locking the record. After the change was made and data was saved. Synchronization happens to User1 and he receives changed data about supplier, but User3(prioritized) keeps the old data. Then we repeat the process , but now User3 are making changes. At the end, after synchronization, both User1 and User2 receives newest Supplier data.
As an addition I'm sharing the link which contain the video of the problem:
Also, sometimes when ‘uprioritized’ user ( User1) are making the changes and User2 keeps an old unchanged data, after User2 decide to make synchronization either by button or just exit from the form or the automatic synchronization happens, old unchanged data becomes saved and each user receives it.
So as a conclusion to all of that , MS Access Optimistic Concurrency Control (Optimistic locking), to be more precise Last in Wins approach is not working properly.
After additional research, I found out that on each user entrance .laccdb file responsible for "Microsoft Access Record-Locking Information" each user gains Admin status, may it be the cause of ‘priority’ problem?
Is there a away to solve that issue without implementing custom locking or Optimistic Concurrency Control approach, such as timestamps use?
For additional information and suggested solutions, I've posted same issue on Microsoft forum. I didn't mean to harm anyone's feeling, by doing that, I found each reply very useful, and the only reason for doing that is to gain as much information as possible, since I'm really new to Access and DB overall, and there is so much information to be covered.
P.S. Sadly I can't insert a link, since I've been receiving "inadmissible context" error, thankfully XPS35 mentioned that link.
The root of the problem is a wrong sharing approach (using QNAP NAS to hold BE), during the test with LAN sharing, the issue disappeared.
Last edited:
