Solved Sync/delivery problems multiple users editing the same Access form.

[Danylo]

You previously posted your question on another forum ...

Thank you for pointing that mistake I've made, I fully share your opinion. I just was in hurry to post the issue thread, and due to spreading the attention between the main project and that issue, I may need a little bit longer time to answer on supposed solution, but I really appreciate all the help provided by each member of the community.

 

[Danylo]

STOP ---- You CANNOT share an Access database using Cloud technology. STOP or risk corruption and data loss. Only Windows can support sharing an Access database.

If your users are not all on the same LAN, your options to share are limited to Citrix or Remote Desktop. If your BE does not have tables that contain more than about 5000 rows, you can convert the BE to SharePoint. Some people have reported success using Azure as the BE but others complain that this is much like watching paint dry.

But there is total consensus on the use of cloud technology to share an Access application - DO NOT DO THIS. It is a huge risk.

Thank you for your reply. To be more precise BE is stored on QNAP NAS situated on different building, by itself it's cloud storage solution, on which there is a folder with BE part of the DB, after that each user is connected to that shared folder by IP. About why such approach was used, I cannot explain, since that issue its' my first attempt to work with mentioned DB.

 

[Danylo]

Pat beat me to it, so I will merely concur with her response. Access + Cloud is a disaster waiting to happen. Those places that CLAIM that they allow Access + Cloud to work aren't telling you the whole story.

Here is the short technical answer. Cloud-based storage doesn't use the protocol call Server Message Block (any version thereof), and SMB is the basis for ALL repeat ALL Windows file sharing and printer sharing technology. If the Cloud server won't allow SMB connections, you WILL repeat WILL corrupt your database quickly. Cloud servers usually allow FTP or HTTP variants (which are whole-file sharing methods), but they do not use SMB (which is a partial-file sharing method).

Thank you for your reply. As I mentioned in my previous reply BE is stored on QNAP NAS situated on different building, by itself it's cloud storage solution, on which there is a folder with BE part of the DB, after that each user is connected to that shared folder by IP, and it's done to let users work with DB remotely, but is still don't know a lot about all of that, since its my first attempts to work with such approach and Access.

 

[Danylo]

Mind elaborating why you believe this is a cloud issue?

OP:
Here's my 1.5 cents, when something in Access does not work, which is not uncommon, implement it yourself. Lock your records in a custom way, make a table where you can store which records should be locked and also release the locks using a custom method. That way you have full control. Just make sure to implement tests regularly, so you can verify things are functioning correctly and research concurrent programming if you haven't already.

Thank you for your reply, I share your opinion and solution. During attempts to solve the issue, my colleague and I tried different locks configuration, and it seems to solve part of the problem but we still have issue that 'priority' user doesn't receive the changes made by other users. If there is no simple solution, I believe timestamp as optimistic lock implementation can become a good beginning.

 

[Danylo]

1. It is not uncommon to call a cloud drive a "virtual" drive, which is how the OP described it.

2. Give yourself a full 2 cents on that idea, within limits... the limits being that you have to have a good reason to believe you are running into a true data sync problem that requires unusual locking. NORMALLY the Windows/Access locking scheme is adequate, particularly if you set the record-locking attributes correctly for your queries and forms. But you DID say "when something ... doesn't work" so a custom method of locking IS a possible response in that case.

As I mentioned in the issue description, built-in record-locking is really useful but they don't fully solve the issue.

 

[Minty]

How are your remote users connecting to the Database ? VPN?
If so, they will probably be on a slow connection which will no doubt be a significant cause of your issues.

From a big-picture perspective, I still have to question the database design/business process that requires 3 or more different people to be working on the same record at the same time?
Can you elaborate so we could perhaps make sensible suggestions to avoid the issue in the first place?

 

[Danylo]

@Danylo - Not only did you post the same question on another forum, but you got a lot of answers, including one that described the pitfalls of allowing multiple users to edit the same record at the same time from two different client computers. This should NEVER EVER happen in ANY data management system. The old phrase "too many cooks spoil the broth" comes to mind, as does "too many chiefs, not enough indians." The technical name for the effect is "destructive interference" which is what happens when "the left hand doesn't know what the right hand is doing."

Allowing multiple users the ability to modify the same record at the same time is a disaster waiting to happen because the "priority" that YOU describe is basically a way of saying "who gets the lock first?" - and it is a decision that occurs at network speeds because your 5 users are all remote with respect to the file server holding your file. The file server that "owns" the back-end file is also the server that owns the file locks because that is the way that Windows works. So all decisions are made in the back-end server.

Everything will be measured in milliseconds because that is how long it takes a system to resolve network lock disputes. From your description of the work flow, it might as well be random as to who is on first. To expect order out of this chaos is unrealistic without a formal "pre-lock" and "post-lock" pair of actions to guarantee exclusive access to a particular record. Which opens up ANOTHER can of worms as now you have THREE transactions that have to occur - the lock, the update, and the unlock. This is just multiplying trouble because if you have gone this far, you now have to worry not only about the original change but also the lock and unlock transactions having issues. This design cascades quickly.

Oddly enough, in this thread and in the other thread, you never clarified exactly what you meant by a virtual drive or virtual driver. So let's ask right now... are you talking about a cloud-based server for your back-end files?

And finally, you are new to us, so you might not know this - but we consider it impolite to post a question which has already been asked and answered on another forum. We call this cross-posting. It is a way to waste the time of people who volunteer to help others out of the kindness of their hearts. The proper way to handle this would be to state your problem but ALSO state that you asked in another forum but so far have not gotten any usable answers. Then post a link to the thread in the other forum so we can see what has been suggested and maybe offer something else than what you've hear before.

I understand that using multi-user simultaneous approach in Access is a really bad idea, since regarding the information I've gained in the last days, access not really a suitable solution for multi-user simultaneous work no matter if there is locking system or not. But I'm still confused about what may cause such simple approach as Last in Wins as Optimistic locking solution to work incorrectly.

Regarding posting on different forums and not mentioning that, as I tried to explain, it was unfortunate mistake, and I'm sorry about that. I didn't mean to harm anyone's feeling, the only reason why I decided to repost the issue on the forum is to gain as much useful information from different communities as possible, since I'm really new to Access and DB overall, and there is so many information to be covered.

 

[ebs17]

I understand that using multi-user approach in Access is a really bad idea, since regarding the information I've gained in the last days, access not really a suitable solution for multi-user environment no matter if there is locking system or not.

Then you didn't understand anything. Multi-user operation on a database and 20 users starting at exactly the same record at the same time are very different things.

 

[Danylo]

Easy, Because Jet and ACE have no problem sharing an Access BE on a LAN and controlling whose update prevails. If Jet and ACE (Access has actually nothing to do with this) couldn't control serializing updates, they would be pretty poor database engines and would have died years ago. Also, the op kept talking about synchronizing and that is simply not something that happens when sharing a BE on the LAN. There is only ONE data source and that is the shared BE on the LAN. There is no reason to synchronize anything.

If your processes are updating multiple tables and all updates must be completed or all must be rolled back, you can put Access into what is commonly called a "deadly embrace" if your processes don't always lock the tables in the same order. Short of that, Access, left to its own devices - optimistic locking - does not attempt lock a record until the user goes to save it. The conflict occurs if two users read the same record and both try to update it. The first user to submit the update, achieves the lock regardless of who read the record first and his data gets saved. Other users, will get a confusing error message with three options when/if they try to save. There are other locking options. The one that control freaks love is "pessimistic" locking. That method locks the record when the user reads it. Yep, that sure solves the problem as userA reads a record and heads out for a two hour lunch locking up the record and keeping everyone else out.

Regarding sharing a BE on the LAN, thank you I will try that solution, and will share the result.
Considering locking, both optimistic and pessimistic approaches were tried and none of them fully solved the issue. During Optimistic Locking, 'priority' user never receives conflicting error message since he never receives updates made by other users, same issue with pessimistic locking.

 

[Danylo]

Danylo,

I hope that the responses in this thread and in the Microsoft Community thread have answered your request. It seems you moved to AWF today hoping to get different results.
As Peter said in post #2, it is considered proper netiquette to advise readers that you have posted in multiple forums.
I believe a review of your business process that is requiring simultaneous update to a record is in order.

Good luck with your project and belated welcome to this forum.

Thank you, the only reason for that was to gain as much useful information from different communities as possible, even in current thread we can see replies from a members of AWF with such a different opinion.

 

[Danylo]

Well, I just noticed that there was a link to the conversation on a different forum so I read it. I can't tell what you are talking about with who has "priority" and "synchronizing" Are you trying to use an Access application using cloud technologies or is everyone on the same LAN? As Albert pointed out, it is an extremely rare situation where multiple users would ever need to update the same record at the same time. What triggers these simultaneous updates? How many concurrent users do you have? No RDBMS can allow multiple concurrent updates. All updates are serialized. The first update wins.

I'm asking these questions because Jet and ACE do not have any problem determining which update wins regardless of whether you leave the default locking plan or choose a different one. The update that loses, generates an error message with three confusing choices when you use Optimistic locking and multiple updates are prevented if you choose a different locking option so there is no confusion.

If you use ANY locking method except the default which is Optimistic locking, you run the risk of a careless user locking out every other user. But if you use Optimistic locking, you need to instruct your users to alway select the discard my changes option to the update conflict question. If they can't be trusted to do this, then you need to add code to trap that error and provide the discard answer using code.

Also, if the bE is NOT Jet or ACE, you have no control over the locking plan from Access since it is not impacted by your settings in the Access FE or BE.

Hope all my previous answers, fully cover mentioned questions.

 

[Danylo]

How are your remote users connecting to the Database ? VPN?
If so, they will probably be on a slow connection which will no doubt be a significant cause of your issues.

From a big-picture perspective, I still have to question the database design/business process that requires 3 or more different people to be working on the same record at the same time?
Can you elaborate so we could perhaps make sensible suggestions to avoid the issue in the first place?

Thank you for your reply.
Users are not using VPN, BE is stored on QNAP NAS situated on different building, by itself it's cloud storage solution, on which there is a folder with BE part of the DB, after that each user is connected to that shared folder by IP. I'm not really sure, whether I explained it correctly since, I'm really new to all of that.

 

[Minty]

So the NAS box is effectively cloud storage then, over a WAN?
If so, I'm amazed it works at all, or that you don't suffer from corruption issues.

Access uses the SMB protocol to connect to Access BE data, and that isn't designed to work over a typical WAN set-up.

 

[Danylo]

Then you didn't understand anything. Multi-user operation on a database and 20 users starting at exactly the same record at the same time are very different things.

I'm not pretending to know a lot about Access, as I mentioned, I have few days experience with Access, but ideas like that : "
Access has been designed for single user use. It experiences severe performance issues when multiple people try to access it through the network simultaneously. Though the technical limit is 255 concurrent users, it becomes terribly slow even with 5-10 users." is very common. And I meant multi-user simultaneous approach.

 

[Danylo]

So the NAS box is effectively cloud storage then, over a WAN?
If so, I'm amazed it works at all, or that you don't suffer from corruption issues.

Access uses the SMB protocol to connect to Access BE data, and that isn't designed to work over a typical WAN set-up.

Currently, I've started the test in which, BE will be shared over LAN , hope it will solve the issue.

 

[Minty]

"Access has been designed for single user use. It experiences severe performance issues when multiple people try to access it through the network simultaneously. Though the technical limit is 255 concurrent users, it becomes terribly slow even with 5-10 users." is very common.

I don't know who wrote that but it's simply not true, Access can easily handle 20 - 30 users simultaneously when on a good local high-speed network if it has been sensibly designed.

 

[Gasman]

So the NAS box is effectively cloud storage then, over a WAN?
If so, I'm amazed it works at all, or that you don't suffer from corruption issues.

Access uses the SMB protocol to connect to Access BE data, and that isn't designed to work over a typical WAN set-up.

I took that to mean a NAS box, but on the same network?
I have 2 NAS boxes in my house which I use as central repositries for my data.

 

[Danylo]

I don't know who wrote that but it's simply not true, Access can easily handle 20 - 30 users simultaneously when on a good local high-speed network if it has been sensibly designed.

Regarding this dispute, I consider it unnecessary to continue it, as I've said I have no experience with Access, and I'm here to find the help and gain information from people who have much more knowledges about Access and databases.

 

[Danylo]

I took that to mean a NAS box, but on the same network?
I have 2 NAS boxes in my house which I use as central repositries for my data.

QNAP NAS situated on different building and on different network

 

[cheekybuddha]

I took that to mean a NAS box, but on the same network?
I have 2 NAS boxes in my house which I use as central repositries for my data.

You can use a NAS box sometimes to act like Dropbox/OneDrive etc. Now the 'Sync' in the topic title makes more sense.

If this is the use case, then it's definitely a non-starter.