Multi User Issue

[moodhi]

Hi there,
I am sure this would have been answered before but I just could not find an answer anywhere or I have simply not been able to understand various answers!

As per recommendation I have split my database with FE and BE and have set the option "Edited Record" on the client sides.
I have a simple form to allow users to read a record based on the selection criteria and with the usual READ, ADD NEW and UPDATE buttons.
When User1 clicks the UPDATE button it locks the record with a Recordset.edit. User2 attempts to UPDATE and gets an error 3260 (but NO "Write Conflict" dialog box appears, why?). I am therefore having to trap error 3260 to inform the User2 that record is locked etc. which is OK. But when User1 completes the update, User2 then can also do the update which kind of overwrites User1 changes, i.e. no way of forcing User2 to read the changes done by User1!



Please what am I doing wrong?

Secondly what is the best approach of allowing users to do changes on the form? Would it be that - as soon as a User1 starts changes then set Recordset.edit so that in the meantime User2 will get the prompt that the record is locked, or is it better that let User1 complete all changes on the form first and only then do Recordset.edit followed by Recordset.Update when User1 clicks the Update button? But again the point is that User2 may hit the UPDATE button few seconds after User1 and will therefore overwrite User1's changes.

[orange]

Does each user have a copy of the FE on their own PC?

[moodhi]

Yes they do for now (test environment) and the BE is on a shared drive ultimately planning to store on Terminal server.

[orange]

See this link for related info.

[moodhi]

I have read the article and I have tried various scenario e.g. used both pessimistic and optimistic locks but I still don't get "Write Conflict" box.

On One PC User1 reads a record, User2 reads teh same record on second PC. User1 changes the record, i.e. Recordset.Edit followed by Recordset.Update all done. User2 then changes the record (i.e. the one read earlier and still on the form) Recordset.Edit followed by Recordset.Update, no errors, no dialog box, the record updates successfully (i.e. overwrites changes done by User1). User1 reads teh record again and gets changes done by User2, Surprise!!!

MemberCode field on the record is Primary Key field but can't see "Write Conflict" box!

[Micron]

IMHO there is NO way you can force user2 to read anything. Seems that you're concerned about the situation where both users are viewing the same record on a form at the same time. Since you're trapping the error for user2, I think the best you could do would be to offer an option to wait (like Excel does) and initiate a form timer to test the record and report when it's not locked or let them check manually (button click) at their leisure. For the former, I'd probably disable all the controls and re-enable when the record lock is off (to provide the visual clue), or let the user attempt to unlock them via button click. However, this would probably only help if the user navigated to a record that was being edited. If they're both on the same record at the same time, then one begins to edit after the other begins to edit, that's a different scenario. See Edit below about that.

Still, there is no guarantee they will read the record recognize the changes. Nor can you guarantee user2 isn't thwarted by another user's attempt to edit this record before user2 takes a stab at it.

EDIT - had to run off & couldn't finish. Perhaps you could consider using the KeyDown event for each control on the record to test if the record is locked. That should avoid any of the errors that can be raised when a user attempts to edit after another person has started but not completed an edit (I think there are 2 more besides 3260, depending on the scenario). Maybe there are too many controls for this, but it should handle the situation where both users are viewing the same record, then each attempts to edit before the first has saved. All of this begs the question - how often does this happen such that you don't want Access to just handle the situation with the different prompts?

[orange]

You may get some more info here.
or
here and more here

Let us know your progress with a follow up.

[moodhi]

I don't understand why I don't get the "Write Conflict" dialog box containing 'Save Record', 'Copy to Clipboard', and 'Drop Changes' buttons? when and how should I get this dialog box?

[Minty]

You will only get that message when data is changed in the underlying record and the second user tries to make an update to the same record after the record was read locally and saved elsewhere.
Access will handle this automatically on a bound form, so it sounds as if you are using unbound forms, if you are using Recordset.Edit and RecordSet.Update?

[Minty]

Oh and crossposted here http://www.utteraccess.com/forum/ind...ic=2051360&hl=
and here https://www.access-programmers.co.uk...d.php?t=302233

@OP plese have a read here https://www.excelguru.ca/content.php?184

[moodhi]

Yes, that's right I am using unbound form with Recordset.Edit and Recordset.update. As I can recall back in the days of MS Access 2010 I had the same form which was working and I used to get "Write Conflict" box so that user could choose one of the 'Save Record', 'Copy to Clipboard', and 'Drop Changes' buttons but now in Access 2016 I don't get it! which means a user can overwrite changes done by other user. Surely it does not mean that in Access 2016 multi user functionality will only work with Bound forms?

Oh, BTW I can see some messages regarding Cross Posters? I thought the other sites are separate Access Forums sites. If not then I apologies it will not happen again, please accept my apology.

[Minty]

What you think was working previously on an unbound form cannot be correct. The data is static and not live on an unbound form.
Access / any other backend DB would have no knowledge of any changes to data on an unbound form.

The only way to achieve that would be to store and update an "Edited record" field somewhere in the backend, but that is fraught with difficulties when someone crashes out and leave a record "locked"


re : The cross posting notification is merely a politeness to the people that contribute to more than one forum.
No problem with it at all - just let the other users know you have posted elsewhere, and provide links.

[moodhi]

OK, I have finally decided to use Timestamp (date/time) field to check before the update whether it was changed, if it has then prevent user from making changes instead allow them to re-read, see the changes done by other user before making their changes.