Whats the best way for multi-user access without write conflicts? (1 Viewer)

ahmed_optom

Registered User.
Local time
Today, 12:10
Joined
Oct 27, 2016
Messages
93
I have an access FE, SQL BE.

Everything is working well, however as the system is getting bigger and busier we are now getting more and more write conflict issues.

This is where a warning pops up saying there is a write conflict, do you want to save, copy or drop changes. This is obvious hideous to see for a normal user.

What I would like to know, is there a way to design the forms such that there are no visible errors and the most recent update is written? I have searched and whilst there are threads on the topic, there isnt a clear strategy to avoid them.

I have looked at record locks and they dont appear to solve the issue. If i go from no locks to edited, all it does in reality is change the warning message.

What I am thinking is that if I redesign the forms so that there are no bound forms, would that solve the issue? This would be a large task, but I need to fix this.


What should the "proper" design be to avoid these conflicts ?

Thanks in advance
 

strive4peace

AWF VIP
Local time
Today, 07:10
Joined
Apr 3, 2020
Messages
1,003
If you want to handle this yourself, you can make a table with the primary keys to keep track when users are on a record. The form can be set up such that it only shows one record, the one they search for. If that record is already "checked out" then they can't have it. This of course isn't perfect either since a user may leave a record on their screen that they aren't actually editing. Alternately, you could use the form Dirty event to see if someone else is changing the record. On the form AfterUpdate event, the "in use" status could be cleared.
 

zeroaccess

Active member
Local time
Today, 07:10
Joined
Jan 30, 2020
Messages
671
The way I do it:

Records can only be edited by the user that created them, or an Admin. If not user on the record or Admin, Allow Edits is NO.

There are business reasons for this but it also solves the record locking problem. May not work for your particular situation.
 

isladogs

MVP / VIP
Local time
Today, 12:10
Joined
Jan 14, 2017
Messages
18,186
There are also specific design issues to check in the SQL BE.
For example, you will get a write conflict if you try to update a table containing Boolean fields where null values exist.
This can occur with only one user logged in
To prevent this, make sure the fields always have a default value e.g. False (0) and set any existing null values to that default value.
 

Users who are viewing this thread

Top Bottom