Access multi user record locks (1 Viewer)

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
If I have a continuous form opened listing all orders and the records are locked or not enabled, there is some code that opens the orders form when a user double clicks on a record in the continuous form,does access lock all those records? In other words if one user has that form opened, can another user edit a record in the orders form if they dbl click in the same continuous form?
Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
33,798
Don't mess with the default locks. Multiple users can have the same form opened at the same time. However, I hope you are not sharing the FE. Each user MUST have his own personal copy of the FE. Only the BE is shared. Also make sure that no one is opening the database in exclusive mode. That will lock others out.
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
Don't mess with the default locks. Multiple users can have the same form opened at the same time. However, I hope you are not sharing the FE. Each user MUST have his own personal copy of the FE. Only the BE is shared. Also make sure that no one is opening the database in exclusive mode. That will lock others out.
Thanks Pat, not sharing FE. Ecommects to BE. I should have said it differently, the records are locked via the form property on the continuous form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
33,798
Set the form locking back to the default setting. Access works best with optimistic locking which is what it is set for.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 28, 2001
Messages
21,222
Pat beat me to it. If Access or that query or form is set to either Optimistic Locks or No Locks, then Windows locking will very likely not give you any issues because in that case, you are unlikely to have both users finish at the same time. The one who finishes second will probably "win" in that case.

The only problem is whether two of your users actually try to edit the same record at the same time. If you REALLY have a case of "left hand not knowing what the right hand is doing" then you would do well to consider a system to lock the record via your own software. If you want to do that, explain your environment and problem well enough so that we can make some useful suggestions.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
33,798
The only applications I've ever had collision issues for are those that have task lists. The solution is to lock the task immediately when the user opens it. Other users can view the task but cannot update it until the first user finishes.

To do this, I use two buttons. one for view and the other for edit. The edit button checks to see if the record is already locked If it isn't, it updates the record with an update query that included criteria -- "Where LockedBy Is Null" This is a final check since the update won't happen if between the time I ran the dLookup() to determine if the record was locked and tried to lock it. Unlikely but better safe than sorry. The View just opens the form without checking the LockedBy column.

The Current event of the form uses LockedBy to set the AllowUpdates property to No if the LockedBy value does not equal the userID of the person accessing the record.

Since disconnects are always possible, an "unlock" process is required in case a locked record gets abandoned due to a glitch.
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
The only applications I've ever had collision issues for are those that have task lists. The solution is to lock the task immediately when the user opens it. Other users can view the task but cannot update it until the first user finishes.

To do this, I use two buttons. one for view and the other for edit. The edit button checks to see if the record is already locked If it isn't, it updates the record with an update query that included criteria -- "Where LockedBy Is Null" This is a final check since the update won't happen if between the time I ran the dLookup() to determine if the record was locked and tried to lock it. Unlikely but better safe than sorry. The View just opens the form without checking the LockedBy column.

The Current event of the form uses LockedBy to set the AllowUpdates property to No if the LockedBy value does not equal the userID of the person accessing the record.

Since disconnects are always possible, an "unlock" process is required in case a locked record gets abandoned due to a glitch.
How would i run an update query from a form control's after update event that updates it's records and it's subform's records.
It seems the records are locked because the form is open on the record I want to update.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
33,798
Just because the records are visible in an open form doesn't mean you can't run an update query to modify them. You will need to .Requery the subform after the update query runs to see the effects though.

The only time this would be a problem is if someone else has dirtied a record you want to update but not yet saved. it.
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
Just because the records are visible in an open form doesn't mean you can't run an update query to modify them. You will need to .Requery the subform after the update query runs to see the effects though.

The only time this would be a problem is if someone else has dirtied a record you want to update but not yet saved. it.
I am dirtying the main form’s record by clicking a bound checkbox to run the query. but not the sub form’s records. Is that what’s doing it?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 28, 2001
Messages
21,222
Don't dirty the record until you really meant to do so. Pat's suggestion involves using an UNBOUND command button (perhaps) that looks at the current record (read-only at first) to get the record ID / prime-key value and then applies

Code:
UPDATE MyTable SET RecordOwner = " & CStr( MyCode ) & " Where MyTable.RecordID = " & CStr( Me.RecordID ) & " AND RecordOwner = 0 ; "

Something SIMILAR to this will do the kind of locking Pat is describing. This kind of single-record update (because only one record ID is named) used the RecordOwner field as a double-barreled flag. If it is a code for each user and is zero at the moment, nobody owns it so the UPDATE's Where clause lets it through. But if someone (including yourself) owns that record, the UPDATE's Where clause blocks further action. When you are done with the record, the operation to release it is simply

Code:
UPDATE MyTable SET RecordOwner =  0 Where MyTable.RecordID = " & CStr( Me.RecordID ) & " AND RecordOwner = " & CStr( MyCode ) & "  ;"

(Just swap the zero and your ID code from the first update.)
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
Don't dirty the record until you really meant to do so. Pat's suggestion involves using an UNBOUND command button (perhaps) that looks at the current record (read-only at first) to get the record ID / prime-key value and then applies

Code:
UPDATE MyTable SET RecordOwner = " & CStr( MyCode ) & " Where MyTable.RecordID = " & CStr( Me.RecordID ) & " AND RecordOwner = 0 ; "

Something SIMILAR to this will do the kind of locking Pat is describing. This kind of single-record update (because only one record ID is named) used the RecordOwner field as a double-barreled flag. If it is a code for each user and is zero at the moment, nobody owns it so the UPDATE's Where clause lets it through. But if someone (including yourself) owns that record, the UPDATE's Where clause blocks further action. When you are done with the record, the operation to release it is simply

Code:
UPDATE MyTable SET RecordOwner =  0 Where MyTable.RecordID = " & CStr( Me.RecordID ) & " AND RecordOwner = " & CStr( MyCode ) & "  ;"

(Just swap the zero and your ID code from the first update.)
Thanks, but this is a bound checkbox, I think I will write a function to perform this.
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
Set the form locking back to the default setting. Access works best with optimistic locking which is what it is set for.
Do you mean run the code then set it back or do you mean DONT LOCK THE FORM EVER STEVE!:) Sometimes I’m a little slow.
 

slharman1

Member
Local time
Today, 07:25
Joined
Mar 8, 2021
Messages
379
Pat beat me to it. If Access or that query or form is set to either Optimistic Locks or No Locks, then Windows locking will very likely not give you any issues because in that case, you are unlikely to have both users finish at the same time. The one who finishes second will probably "win" in that case.

The only problem is whether two of your users actually try to edit the same record at the same time. If you REALLY have a case of "left hand not knowing what the right hand is doing" then you would do well to consider a system to lock the record via your own software. If you want to do that, explain your environment and problem well enough so that we can make some useful suggestions.
Ok. Got it. Don’t lock the records ever. Let access do the locking.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:25
Joined
Feb 19, 2002
Messages
33,798
Yes. Do some testing to cause a collision so you can print out the error message the second person gets so you can instruct your users what to do. User1 - open formA
User2 open formA
User1 modify formA
User2 modify formA
User2 save formA
User1 save formA ---- User 1 gets a confusing error message with three options.

If you are showing the recordSelector on the form, it turns to a pencil when the form is dirty. The other user should see a circle with a line through it.

Actual collisions are very rare in the real world bcause most of the time when a user goes to update a record, it is because of some outside influence. That is why the common task list is the most likely situation to cause a collision. Otherwise, people are working on different records at the same time rather than the same record.
 

Users who are viewing this thread

Top Bottom