Handling Update Access 2013 web Backend/Access Client Frontend (1 Viewer)

OldMike

New member
Local time
Today, 10:44
Joined
Mar 1, 2015
Messages
5
I have an Access 2013 web backend (azure DB), with front end Access Client linked via ODBC, and I’m trying to handle the potential for conflicting update (i.e. multiple users attempt to update the same record at the same time). However I’m getting inconsistent behaviours and would appreciate advice/guidance.
The action that I want to take if we get a conflict is to retain the first update, abandon the second and inform the second user that they need to re-apply their changes. I’ve tried 3 different tables so far. Each table is initially displayed via a multiple item type form, edits are not permitted on this form. In order to edit a record the user must click on an edit button related to each record entry, this open a pop-up form which permits editing.
The behaviours observed are
Table 1 – Scenario 1
· User A opens a record for update
· User B opens same record for update
· User A updates the record and commits the update by clicking a button on the form
· User B clicks into a field to update (no buttons pressed), however the server throws a 7878 error which identifies that the underlying record has been changed. I handle this in the Form On-error event by closing the edit form without save, taking the user back to the List form and advising them that that they need to re-edit the record to get the current version.


Table 1 – Scenario 2
· User A opens a record for update
· User B opens same record for update
· User B commences changing data
· User A updates the record and commits the update by clicking a button on the form (a 7878 is not thrown because at this stage the underlying data has not changed)
· User B completes their update and commits it by clicking on the button. This causes the server to throw a 7787 error which identifies that the record which User B is attempting to update has already been changed. I handle this initially in the Form On-error event, I also need to some processing in the Save button processing to ensure that control is gracefully returned to the list form.
Both of the above are fine. However two similar tables exhibit different behaviours and I can’t see why.


Table 2 doesn’t seem to encounter a 7878 error in the scenario 1 situation. However if User B continues to process and then clicks the save button, a 7787 is thrown. This is actually OK as the end result is effectively the same. Lack of understanding of what’s going on is my main worry here.


Table 3 doesn’t seem to encounter a 7878 error in scenario 1 either. However if User B continues to process the results are undesireable, with no errors and User B’s changes overwriting User A’s.


I’d appreciate any input – obviously I can provide more detail as required.
 

spikepl

Eledittingent Beliped
Local time
Today, 11:44
Joined
Nov 3, 2010
Messages
6,144
I have no idea. However, when I encounter riddles like this, what I do is copy the bits that work in a known/desired way (your table 1) , verify it still behaves like the original, and then, bit by bit convert it to the offending table - one column/parameter/ value at a time -while checking the behaviour at each step.

Update.

I have an order system with order/orderlines data in form/subform. I never managed to supply comprehensible messages from the locking system to the users, so I opted for simply locking an order for changes, so that the first user can do what he wants, but the next gets the message that the order is locked for changes. I do this by storing a user id in a table with orders numbers, effectively so only orders without a prior user id can be edited. The only issue is what to do in case of a crash - I think I wipe the locking table when the last user logs out and advise them to login and out if orders are locked.
 
Last edited:

OldMike

New member
Local time
Today, 10:44
Joined
Mar 1, 2015
Messages
5
Thanks spikepl, I think that I'll have to take your approach to trying to work out what's going on, i.e. start to rebuild and test the bits that are'nt working in steps to see if I can identify where the issue is.
In the meantime, I'm going to implement an alternate solution by including an update number column on the tables. This will be incremented from within the form each time the table is updated and an after_update macro will then verify that the update number is as expected and throw an error if it isn't.
 

Users who are viewing this thread

Top Bottom