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