locked
How To Handle Concurrence Problem With Multiple Simultaneous Users? RRS feed

  • Question

  • Backend on server
    Frontends on local machines
    Complicated application with many bound forms and sub forms.

    If two users have the same sub form open and one changes record A and another record B how do you reflect the changes to both users?  How do you handle the situation where both users start to edit the same record?


    http://www.saberman.com

    Sunday, September 17, 2017 10:10 PM

All replies

  • The changes are reflected as soon as they are committed.  Therefore, once the user moves to another record, if the second use opens that record they will see the changes immediately.

    As for handling multiple users editing the same record, this is handled by the record locking seeing you choose to use.  Done people also go and add a column that they set once a users Access a record to Mark it as being edited so other users cannot and display a message.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, September 17, 2017 10:39 PM
  • Well, you could turn on record locking for the given form. That way only one user can ever edit the same record. The default you NOW have allows both to start editing – but the second user will then receive an error message that the record been changed by someone else.

    If you turn on record locking (this setting is done for each form – the overall application setting for locking ONLY effects the default that you will see/get for new forms you create – does not affect nor change existing forms).

    If a user tries to edit a record that is “in-use’ by someone else then for a standard form, or even a continues sub form, then they will see a “record” lock icon appear in the record selector as per this screen shot:

    So you really don’t have to do anything if you decide to use the built in locking setting/feature found in the property sheet for each form.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Sunday, September 17, 2017 10:55 PM
  • Hi,

    By any chance have you seen this recent discussion at UtterAccess?

    Hope it helps...

    Sunday, September 17, 2017 11:18 PM
  • >The changes are reflected as soon as they are committed.
    Not if you use a datasheet via.  If the first ten records are displayed in a sub form and another user changes the fifth record the change is not reflected until the sub form is requeried (which causes the form to flash).

    >this is handled by the record locking seeing you choose to use.
    Unfortunately, record level locking is not available if you password protect the database.  If you have it selected and then apply a password Access turns off record level locking.  You will get a popup if you edit a record and another user changes the data in the same record before you save your changes.  The option will be to abandon your changes or to replace the other users version of the record.


    http://www.saberman.com

    Monday, September 18, 2017 7:37 PM
  • >Well, you could turn on record locking for the given form.
    Checked Open database using record level locking.  Saved, opened exclusive, applied password:


    http://www.saberman.com

    Monday, September 18, 2017 8:06 PM
  • >By any chance have you seen this recent discussion at UtterAccess?

    Interesting but a simpler solution would be have a button that runs an update query to set the user name and lock on the first record that is not locked.  That would select the next call back record to be processed.

    In any event it would be a bit difficult to implement in a database with many tables, forms and sub forms.



    http://www.saberman.com

    Monday, September 18, 2017 8:12 PM
  • Well, as I stated that setting does NOT effect existing forms. You could simply open the form, and turn on record locking. the result will be "page" locking as opposed to true row locking, but it tends to suffice in most cases.

    So you can STILL turn on record locking for that form. I was however not aware that if the database is encrypted, then that row locking feature can't be used (new to me). However, as I stated, JUST changing that feature will NOT enable nor "engage" the locking feature anyway - you STILL have to turn on record locking on a form by form basis. So that setting is NOT required to try and test record locking.

    And do you really need the database encrypted anyway?

    As noted, if you do turn on the record locking in a given form WITHOUT the above "row lock" feature, then records are still locked - but you be using "page" locking, and this in some cases can case a lock on a near by record to occur that "can" effect other users. I suggest you try it and see how it goes.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada


    Monday, September 18, 2017 9:20 PM
  • >And do you really need the database encrypted anyway?
    Applying a password encrypts the database and it is necessary for this application.

    > I suggest you try it and see how it goes.
    It is page locking.  More than one record will be locked.


    http://www.saberman.com

    Monday, September 18, 2017 9:36 PM
  • True – page locking can cause “close” records to be locked with this feature. However, Access applications were run this way for about 10 years before the actual “row” locking feature appeared.

    And in most cases you don’t need the locking feature for a sub form (and sub form records tend to be the smaller ones). The reason for this is that the main record would in theory be locked, so users can’t edit that main record, and thus “less” likely to edit sub form records.

    You can actually “determine” this issue NOW based on your how often users NOW are receiving the dreaded message:

    “The record been changed by another user”.

    If prior to turning on record locking you RARE get the above message, then you likely can use or try enabling record locking in the form and see how this goes.

    If you rare received the above message, then turning on record locks in a form would quite much eliminate the above message and you likely can run the application with page locking.

    So a lot of Access applications for years only had page locking, and it worked quite well. How “good” or “bad” this issue is will much depend on the application and the number of users.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Monday, September 18, 2017 9:42 PM
  • > Access applications were run this way for about 10 years before the actual “row” locking feature appeared.
    I suspect that many of them were either single user applications that were not split or split applications that usually had one user at a time running them.

    >The reason for this is that the main record would in theory be locked
    Not sure why that would be the case.  Many of the forms have combo boxes to select a subset of records.  The sub form selects records based on the selection in the main form.

    >“The record been changed by another user”.
    That is the current state. 

    >the number of users.
    The problem exists even if there are only two users in the application. 


    http://www.saberman.com

    Monday, September 18, 2017 10:02 PM
  • I think based on what you have been detailing you'd have to perform a requery using the timer event or perhaps using the form's current event.  Your users will have to live with the flash if they want the functionality.

    Also, depending on the form setup, you may not need to request the form in its entirety, and perhaps you could get away with limiting the requerying to individual controls, but only you can judge that aspect


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, September 18, 2017 10:40 PM
  • They are currently living with the flash.  A lot of code was added to keep track of which user was in which sub form as the requering is only needed if another user changes a record in the same form.

    The requering is done at the sub form level.


    http://www.saberman.com

    Monday, September 18, 2017 10:55 PM
  • >many of them were either single user applications that were not split

    Splitting would not effect this issue.

    And row locking would not be required for single user apps.

    All I saying is that a “lot” (well in fact all of the multi-user applications worked this way until the row lock feature was added to Access). So that’s all Access had from 92 (Access 2.0) to Access 97. The row lock feature appeared for Access 2000.

    I should point out that this feature comes with a significant bloat penalty. This is because Access still really does not have true row locking – what the feature does is ALWAYS expand a record to the size of one “page”, and thus in a roundabout cheating way, the result is row locking.

    As noted, your issue here is the encrypted database – row locking not available.

    >Many of the forms have combo boxes to select a subset of records

    Sure that makes sense. However, the “vast” majority of forms + sub forms don’t work this way. All I saying again is that from a developer point of view, setting locks for the main form can be a useful compromise. Since the “main” record tends to be larger, then the probability of a collisions lock between two users is FAR less than most sub forms.

    Sub forms usually have smaller records – those smaller records is where the true row locking feature REALLY helps. And if you lock the main form, then it often prevents other users from making changes – and thus tends to keep such users away from making changes to the related records in the sub form (again, not always the case – but often enough to be considered a useful design compromise). The art of development is really weighing in the upsides of design choices based on what tools you had. So we back then did not have much of a choice, and thus took that into considering when building applications.

    So it really a question of what kind of compromises you as a developer are willing to accept (or can accept – often the requirements are dictated to you). Certainly some applications will preclude that these compromises are reasonable in some cases, and not in others.

    So a lot of multi-user applications did run with locking set in forms – but it was based on page locking since that’s all we had at one time.

    It’s too bad that we can’t place a password on a database without encryption (that’s how Access was pre-2007). Your requirement of password and thus in turn encryption certainly is tying your hands here in being able to use row locking.

    In one of my applications I required a user friendly box to tell who else is editing a given record – so I rolled my own locking routines – they worked quite well. I used a lock table, and thus did not have to modify the 160 forms and 50+ tables. I only had to add a single routine to the forms on-open event, and the forms close event. But my designs RARE allow record navigation on the main form, so this approach worked well in my case – it would not work for applications that allow main form record navigation (which I avoid for performance reasons anyway).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, September 19, 2017 4:30 AM
  • > Your requirement of password and thus in turn encryption certainly is tying your hands here in being able to use row locking.
    It is the client's requirement that the front end have a password.  There is actually an option in Access to us legacy encryption which does not kill record level locking but, once again, the client wants to use the stronger current encryption.

    http://www.saberman.com

    Tuesday, September 19, 2017 5:33 AM
  • Hi saberman,

    >>Checked Open database using record level locking.  Saved, opened exclusive, applied password:

    Could you tell us what's your encryption method setting?

    In my test, I could reproduce this issue when I'm using default encryption. After changing to using legacy encryption, I did not get the error when trying to reproduce the issue. So I suggest you try to change the setting and do the test again.


    Best Regards,

    Terry

    Thursday, September 21, 2017 2:45 AM
  • >So I suggest you try to change the setting and do the test again.

    Already suggested that to the client.  They are not willing to use legacy encryption for two reasons:

    1. They want the higher security.

    2. They are concerned that the next release or patch of Access will eliminate legacy encryption.


    http://www.saberman.com

    Thursday, September 21, 2017 3:51 AM
  • Hi saberman,

    It is sad this is not accepted by client. I am afraid there is no better way to resolve flash since we need to requery.

    For this issue, I would suggest you submit a feeback and hope Concurrence Problem could be resolved in next version.

    https://access.uservoice.com/

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 22, 2017 9:22 AM