none
Does DAO Do A Good Job of Record Locking using ODBC to SQL Server? RRS feed

  • Question

  • Hello:

    I have a client who has a mixed Access environment... mostly Access 2010 but with some 2016.  If I define my tables on SQL Server and then provide ODBC links from Access to the SQL Server tables, does DAO do a good job of record locking in case multiple users are accessing the same record?

    I want to avoid ADO.

    Any issues with this?

    Regards,



    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, August 7, 2018 10:03 PM

Answers

  • Unfortunately, you don’t get any record locking at all.

    The “default” in access is no locking (optimistic), and thus if two people edit the same record, the 2nd user gets the dreaded “record been changed by another user).

    So, as normal course of operation, you don’t get any row locking.

    If some type of locking is required, then one could cobble together some routines I suppose – I did this with a access back end some years ago – but the same approach would work quite well with a sql back end (my design worked well, since I tend to NEVER allow record navigation on a “main” form say like an invoice form, or a customer form).

    So you could build a lock table. I used a table with:

    LockItem:  “tableName” + “*” + pk

    Workstation Name:

    NetworkLogon:

    So the lock item column had a unique index on it, and thus tblcustomers*146 would effective lock the PK id in tblCustomers for me.

    I did the above since I wanted a “user” friendly message. I could thus lock any form by adding a simple one line call in the forms on-open event. If the lock was not obtained, then the form load was canceled (but it displayed a message who had the record in use and a timer – so if the other user exited the record, then the code would continue, or the user hit cancel.

    I also re-cycled records in the lock table as to prevent bloat. So I simply “cleared” out the lock item row to null on the forms close event. So any form could adopt “locking” with two extra lines of code.

    So I think you would quite much have to roll your own in this regards.

    Regards,

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

    Edmonton, Alberta Canada



    • Edited by Albert D. Kallal Wednesday, August 8, 2018 2:15 AM
    • Marked as answer by RichLocus Wednesday, August 8, 2018 5:15 AM
    Wednesday, August 8, 2018 1:39 AM

All replies

  • Unfortunately, you don’t get any record locking at all.

    The “default” in access is no locking (optimistic), and thus if two people edit the same record, the 2nd user gets the dreaded “record been changed by another user).

    So, as normal course of operation, you don’t get any row locking.

    If some type of locking is required, then one could cobble together some routines I suppose – I did this with a access back end some years ago – but the same approach would work quite well with a sql back end (my design worked well, since I tend to NEVER allow record navigation on a “main” form say like an invoice form, or a customer form).

    So you could build a lock table. I used a table with:

    LockItem:  “tableName” + “*” + pk

    Workstation Name:

    NetworkLogon:

    So the lock item column had a unique index on it, and thus tblcustomers*146 would effective lock the PK id in tblCustomers for me.

    I did the above since I wanted a “user” friendly message. I could thus lock any form by adding a simple one line call in the forms on-open event. If the lock was not obtained, then the form load was canceled (but it displayed a message who had the record in use and a timer – so if the other user exited the record, then the code would continue, or the user hit cancel.

    I also re-cycled records in the lock table as to prevent bloat. So I simply “cleared” out the lock item row to null on the forms close event. So any form could adopt “locking” with two extra lines of code.

    So I think you would quite much have to roll your own in this regards.

    Regards,

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

    Edmonton, Alberta Canada



    • Edited by Albert D. Kallal Wednesday, August 8, 2018 2:15 AM
    • Marked as answer by RichLocus Wednesday, August 8, 2018 5:15 AM
    Wednesday, August 8, 2018 1:39 AM
  • Albert:

    Thanks for the detailed reply.  If I do an ODBC to SQL Server, will I still get the dreaded message that another user has updated the record?  That may be good enough.

    I know that message comes up when two users are linked to another Access database that contains the tables, but do you believe that it will still provide the same warning using DAO to an ODBC linked SQL Server where two users update the same record on the SQL Server?

    Thanks again,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, August 8, 2018 5:17 AM
  • Yes, you get that same old message. So that part works as before.

     So if you not in the past used form "record locking", then you NOT see any difference when using SQL server.

    When you use a access back end, you can in the forms setting change the locking to "edited" record. (I should point out this setting overrides the "global" setting in Access to use record locks. (in fact that global setting simply "change" the default for any new form you create, but does not effect ones already created). Short story: the edited record "Lock" feature is a form by form option and setting.

    When you turn on locking for "edited" record (it is a form by from setting in the form's property sheet, data tab, edited record). When you set this to "edited" record. Then if you are editing a record and another user attempts to edit that record, their keyboard will go "Beep" and they cannot modify the record (so no message about some other user having changed things will occur). And if you have the record selector showing in that form, then the "pencil" that shows editing will flip over to a circle with a slash across. It will look like this:

    (so the "other" users will see the lock icon as per above (but they ONLY see it if they attempt to start editing the record - not just viewing it).

    However, if in the past you never used this feature, then when you jump to a sql back end, you do NOT have the above edited record option. As a result, things will work exactly like they did with Access back end (assuming you never used edited record lock feature). So users will simply get that "The record been changed by another user".

    So there is not much a difference when you never used "edited" record locks in the past. However, when using SQL server, you simply don't have this choice, and you have to live with the long time default (of no locking) and that message about the record having been changed by another user.

    Regards,

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

    Edmonton, Alberta Canada



    Wednesday, August 8, 2018 8:01 AM
  • Albert:

    Thanks for the expert advice.  You saved me a lot of time experimenting with this.

    Regards,

    Rich Locus


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, August 8, 2018 10:25 PM