Unable to generate any writing conflict in a multiple user environment (1 Viewer)

Wim ES

New member
Local time
Today, 13:42
Joined
Mar 24, 2020
Messages
5
Hello to anyone out there,

Most people seem to be struggling with writing conflicts in multiple user access applications, but our issue is just the opposite.
We work with a large 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 had 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 wanted to achieve is that when a user changes a certain record in a form, other users can’t change the same record until the changes of the first user are saved. This would mean that we would see a lock icon in the selector bar, as mentioned in this forum:

https://stackoverflow.com/questions...ing-a-record-in-multi-user-access-environment

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).
If there would be anyone out there who managed to overcome a similar issue, it would be great if you could share your experience.

Thanks in advance,
Wim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:42
Joined
Oct 29, 2018
Messages
21,357
Hi Wim. That's interesting. I just gave it a try, and got the locked icon as expected. This also means I got the warning message about not being able to save any changes because a previous edit was made, and I have to option to overwrite it or discard my changes.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 28, 2001
Messages
26,996
The way I once did this was to lock the record via a home-grown software lock. Added a back-end table with person ID and a date/time stamp and a table ID and a record ID, which needed to be the prime key to make this work. When a person opened a record, I tested the lock table. If that record in that table was not busy, I wrote those four things in the table - Person, Table, Field, Date/Time. When a person navigated away from the record, I erased the record if no changes were made, or LEFT the record if it was updated.

When someone else opened a record, either the lock table didn't have a lock or it did. Obviously, if it doesn't have a lock, no problem to go forward. However, if there WAS a lock entry, I had to compare the lock date/time to the current date/time to see how old it was.

For YOU to do this, you would then need to establish a rule that says "Locks older than x time units can be erased." What you do if the 2nd person to reach the record is within the lock's lifespan is up to you, but that might include a pop-up of a writing conflict followed by forcing the form's recordset to do a .MoveNext or .MovePrev or some other record operation.

Just a thought about one way to do it.
 

Wim ES

New member
Local time
Today, 13:42
Joined
Mar 24, 2020
Messages
5
Thank you for your valuable input.
I guess, in the end, we don't have to be unhappy with the current absence of writing conflicts, despite the unexpected behaviour we noted so far.
I would understand the way forward for our database is in lock management through code.
We will go for a test in that direction and I will hopefully be able to share some experiences on that some time soon.
Thanks again,

Wim.
 

onur_can

Active member
Local time
Today, 05:42
Joined
Oct 4, 2015
Messages
180
Is the option to open databases with record-level locking checked in the tab with client settings in access options?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:42
Joined
Feb 19, 2002
Messages
42,970
Record locking works correctly. There are no outstanding bugs that I am aware of. What is an NAS? If that is a normal share on a LAN, you shouldn't be having a problem if it is some kind of cloud device, you WILL have a problem.

Are you certain that the first user didn't actually save his record?

If your forms don't already show RecordSelectors, set the property to yes temporarily. Have person 1 open a form and modify a record but don't save it or move to any other record. Just stop there. The record selector should show a pencil. Have person 2 open the same form to the same record. The record selector should show a circle with a line through it. I think person 1 should be able to save without raising an error but person 2 should get the confusing, 3-choice message. If person 2 saves first, pierson 1 should get the confusing message.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 28, 2001
Messages
26,996
Pat Hartman said:
What is an NAS?

Pat, an NAS is "Network Attached Storage." It varies as to how the network is attached, but it is like having an in-house cloud. It can be attached via Ethernet but is more often attached via a separate fiber-channel interface. So it is a special type of I/F card. We had these in the Navy and they can be really good in an Access environment.

What you need to know is that NAS can be treated like a local drive which happens to not reside inside the PC chassis. They are often as fast or faster than the HDD inside your PC. On my mainframe, our NAS was running at 8 Mbit transfer rates whereas the in-box disks were only 2 Mbits. I loved them for the capacity, speed, and flexibility. An NAS can even act like an external RAID controller if you want. They typically handle RAID 0, RAID 1, RAID 5, and some combinations. Oh, one more thing... they ain't cheap.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:42
Joined
Feb 19, 2002
Messages
42,970
We KNOW you can't have an Access BE in the cloud. Will an NAS work the same way as the cloud or like a mapped drive? If it doesn't work like a LAN mapped drive, then you cannot use it to hold the BE for sharing.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:42
Joined
Jan 20, 2009
Messages
12,849
Will an NAS work the same way as the cloud or like a mapped drive?

A NAS is simply a specialised networked computer with what is effectively a really big disk composed of managed disk arrays. They use any of the connection technologies used on a LAN.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 28, 2001
Messages
26,996
Pat, an NAS Disk assigned to Windows servers takes a drive letter and maps to a partitioned device interfaced via fiber-channel. The NAS controller is actually a storage management computer, usually UNIX-like, that identifies partitions of big drives. They do automatic striping and mirroring and other tricks of the storage trade. What they publish looks and acts just like an ordinary local disk except for using that funky fiber interface. But the only way you would know it is to do a deep-dive into the Windows Device Manager to see what the connections look like.

They are better even than a LAN-based drive, speed-wise, unless you are on Gigabit Ethernet, in which case LAN drives MIGHT be comparable in speed. We never had one that was configured like a wi-fi cloud setup. I worked with them for maybe 8 to 10 years before I retired. They absolutely ARE compatible with Access and pretty darned fast besides. The Navy had a big medical system for reservist personnel hosted at our site using NAS disks, which tells you that they had plenty of confidence in the disks. That system was an Access FE and an SQL Server BE for which the data for the BE was purely on NAS drives.

Initial configuration is a pain in the toches, mostly due to working with literally hundreds of partitions, but daily operation is practically invisible to you. Not only that, but the smart controller has the ability to do distant data replication for warm-standby capabilities AND can tie in to high-capacity tape cartridge backups. We were using one of those NAS systems when Hurricane Isaac forced us to evacuate as a safety precaution. We were running from our remote servers in San Diego in less than two hours after the command to finalize switch-over, and that included reboots and remote-access operations to the console through our MilNet connections.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:42
Joined
Jan 20, 2009
Messages
12,849
Pat, an NAS Disk assigned to Windows servers takes a drive letter and maps to a partitioned device interfaced via fiber-channel. The NAS controller is actually a storage management computer, usually UNIX-like, that identifies partitions of big drives.

I think you might be mixing up NAS with SAN (Storage Area Network). SANs are assigned to a a server network and their traffic is separate from the LAN. They are optimised for speed and often use optic fibre connections.

NAS is connected to the LAN.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 28, 2001
Messages
26,996
Whoops, NAS and SAN - must be having a senior mirror day. But I've worked with NAS and Access as well. As long as the LAN isn't going through a firewall that blocks SMB protocols, this works OK too.
 

Wim ES

New member
Local time
Today, 13:42
Joined
Mar 24, 2020
Messages
5
Dear all,
Thanks for your feedback and the interest you have taken.
We have acquired a Synology NAS DS118 some time ago. A small investment with great performance.
Will check if the SMB and firewall settings for you and revert.
Many regards,

Wim
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:42
Joined
Feb 19, 2002
Messages
42,970
Did you do the test I suggested? Could you see that the record was locked? If not, the NAS will NOT work for you. You will have to go with a SQL Server (or other RDBMS) BE if you don't have a LAN drive you can use for the BE.
 

Wim ES

New member
Local time
Today, 13:42
Joined
Mar 24, 2020
Messages
5
Sorry for making you wait, PAT, but I have been doing a few more time consuming tests before getting back to you.
First of all, our NAS is a mapped drive, it has SMB enabled, but NO firewall is enabled. I was not really expecting issues with that, because this mapped drive is perfectly being shared by different users on the network without particular access issues.
And so I have to disappoint you that our original finding is still very much a mystery to me.
On the other hand, I have started to migrate some table from the merged access database (front and back end all together) to a web based MySQL server and (maybe not so) surprisingly and without any coding, I received a 'Write conflict' notification when trying to change a record in an access form (based on this table) with 2 separate users.
We may therefore decide now to move further with this migration to allow also for external web based interaction with our database.
Do remain interested in the solution to the original issue as this would be a great temporary solution if no coding would be needed, but in the longer term we are going to change the way forward.
Thank you for all your help and suggestions.

Wim
 

Users who are viewing this thread

Top Bottom