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

[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.

 

[Gasman]

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.

No, mine effectively act as servers, albeit in a home environment.

 

[cheekybuddha]

No, mine effectively act as servers, albeit in a home environment.

I was talking about the OP's usecase, not yours!

 

[Danylo]

Thank You Everyone for contribution and a lot of useful and precious information, I'm happy to say that solution is found. Thank you everyone who pointed to wrong sharing approach, during the test with LAN sharing, the issue disappeared.

 

[jdraw]

"I'm happy to say that solution is found."
Thanks for the update.

 

[The_Doc_Man]

On the other hand, I actually HAVE used NAS for a BE and it worked nicely for a 40-user app, even though it crossed several sub-nets (and so did the 40 users). The keys to its success were that (a) ALL of the sub-nets were in-house so this was a true LAN, just a little bigger than normal; (b) it was a Gigabit Ethernet at a time when 100 Megabit was more common; (c) the NAS supported ALL protocols including SMB. The fact of it being NAS might not matter. The question is whether the device's on-board controller supports SMB requests. If it does, you've got a shot at it working OK. If not, you're done before you even start.

 

[The_Doc_Man]

Basically, most brands that I have seen of NAS generally are a disk with a little extra set of smarts. The configurations vary and you could say that the NAS either has a VERY smart, network-aware controller or it has an ordinary controller and a network-aware microcomputer with a rudimentary operating system. But if you think about network-attached printers, the concept is the same. You don't log in to a network printer, you treat it like a network peer and exchange data with it.

With NAS, you have a disk controller device attached to your network and it responds to protocol-based requests that don't require a direct user login. Connections can be based on encrypted virtual channels for security, but in most cases with the Navy, the devices were inside a firewall-based barrier so outside connections could never get to them. The encrypted versions just encapsulated the user requests and responses to allow data to flow securely and safely.

I have seen smaller versions of NAS in Office Depot and Best Buy. Western Digital and Seagate used to make them in 500 GB, 1 TB, 1.5 TB, and 2 TB sizes the last time I saw them, though that wasn't recent. Basically you install a device driver that does its I/O calls through a network channel and that gets you up and running. So NAS is not necessarily the same as CLOUD storage.

I have not seen versions that recognize whether you are on a LAN or a WAN, but don't claim to have seen every such device.

Having said that, there ARE versions of external NAS disks that DO act like cloud storage on your in-house network. It's all a matter of the protocols that the smart controllers will accept across the network.

 

[cheekybuddha]

So NAS is not necessarily the same as CLOUD storage.

Many NAS (eg QNAP, Synology) offer a lot more than some dumb hard discs on the LAN.

They come with a lot of software which can run VM's, media servers, automated backups and file sync type services like Dropbox/OneDrive etc. They can also serve as a cloud service over the interwebs.

I have a QNAP NAS, which I just use as dumb hard discs on the LAN!

Here's a screenshot of the admin page for mine: