Results 1 to 10 of 10
  1. #1
    ddd is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Italy
    Posts
    6

    Question Record locking: Update Query VS editing record in table

    Hi,

    our company database is divided in Frontend / Backend.
    Backend is on a network drive. The backend is rather large, 5 files are used with almost 1.5 GB total space, about 80 tables. The table tblOrders is in the largest file (about 800MB) has about 800k records and is duly indexed / normalized. The field 'sel' is not indexed.
    Each PC has it's own frontend application installed. The application is normally used by several users (about 10 on average). Table tblOrders is often used by the users.
    The frontend has been developed by me over many years. Before in Office 2000 and starting this year Office 2019.
    OS: Windows 10
    Office 2019 Pro

    In Access Options, Client Settings are:
    Open databases by using record-level locking True
    Default open Shared
    Record Lock Edited records
    OLE/DDE timeout (sec) 30
    Active DDE update True
    Encryption Method Use legacy encryption (good for reverse compatibility and multi-user databases)
    (sorry if some of the options are not exactly as the English version, am translating from Italian)

    Am having the following issue if I try to run this query:
    Code:
    UPDATE tblOrders SET tblOrders.Sel = False WHERE tblOrders.Sel = True
    From time to time the update fails due to concurrency violations on some records. Meaning that if for instance 10 record have sel=true 1 or 2 may not be set to false.


    This does not happen every time but quite frequently (several times during the day).
    We have checked and no user is keeping open the failing records in a form.

    But when i open the table directly (from frontend: F11 > Tables) i can change the record just fine.

    My understanding was that if a record is locked then that applies both to queries and direct editing. So I was wrong. But what is going on here?
    I did some search online but I keep on finding the same usual notions about concurrency and how to setup your database to manage it. Nothing that shows a different behaviour Queries VS direct editing. Can you shed some light on this?

    P.S. It's planned to bring the database backend over to SQL Server but this wont happen for another 3/4 months. I have to solve this issue while we ferry across to the new engine

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the sel value? is that something the user has populated as a selection of records?

    If so, multiple users may be 'selecting' the same records which is causing the issue. Would be better to do this another way, perhaps populate a temporary table in the FE with the PK of the selected records or to populate a vba array with the PK's which can then be passed to the query filtering on sel.

    If you want the temporary table in the BE, include a username field as well so each user has their own unique set of records to avoid clashes

  3. #3
    ddd is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2009
    Location
    Italy
    Posts
    6
    sel is boolean. Yes, users select the records by flagging sel to true. This is done with a button that launches VBA code that executes a query (using docmd.RunSQL).

    If so, multiple users may be 'selecting' the same records which is causing the issue.
    Due to the workflow it's is very unlikely two users would be selecting the same record at the same time. I would expect a issue that may raise once in a blue moon, not multiple times in a day.
    If selection does not happened at the same time then should not raise any issues, right?

    I will clarify the workflow as it may be helpful.
    A) An order is inserted > a record is created in tblOrders. sel default is false
    B) An order is selected > tblOrders.sel is set to true using a docmd.RunSQL
    C) All selected orders are deselected > tblOrders.sel=false using
    Code:
    docmd.RunSQL("UPDATE tblOrders SET tblOrders.Sel = False WHERE tblOrders.Sel = True")


    both B and C sometimes fail because of concurrency violation. But the record is editable if the table is opened.
    Now, there are several solutions to avoid the problem. But I would like to know the reason of the difference in behaviour. Does the locking system apply ro queries and not direct editing sometimes? under which conditions? Seems offly odd to me.


  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Due to the workflow it's is very unlikely two users would be selecting the same record at the same time.
    depends on when it is unset

    user a sets record 1 sel to true
    user b sets record 2 sel to true
    user a runs the update query - you will get an issue if user b is still on record 2
    But the record is editable if the table is opened.
    perhaps by that time, user b has moved off the record
    Does the locking system apply ro queries and not direct editing sometimes?
    no

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    One other thing to try is to set default record locking to no locks (you have edited record) and tick open databases by using record level locking

  6. #6
    ddd is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2009
    Location
    Italy
    Posts
    6
    Quote Originally Posted by Ajax View Post
    perhaps by that time, user b has moved off the record
    That's not what is happening. Am sorry if I was not clear in my first post.
    On further tests / running into problems we have determined that SOME records can't be updated by query. But they can be updated when editing the record directly in the table.
    This happens consistently for said records.

    For example if we do the following, given record X in tblOrders,
    we run docmd.RunSQL(...) to update it. It fails due to concurrency violation.
    we open table tblOrders in the Client. Go to record X. Edit it manually. It works.
    we run docmd.RunSQL(...) again. It fails again due to concurrency violation.
    we open table tblOrders in the Client. Go to record X. Edit it manually. It works.
    and so on...

    The query also fails due to concurrency violation if run from the query builder.


    P.S: Am going to copy the current production db in order to have a copy to do more testing on in a controlled way. Hopefully will be able to provide further details.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    did you try my suggestion in post #5?

    And what is the actual error message you are getting?

  8. #8
    ddd is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2009
    Location
    Italy
    Posts
    6
    Quote Originally Posted by Ajax View Post
    One other thing to try is to set default record locking to no locks (you have edited record) and tick open databases by using record level locking
    I have just changed this setting, restarted Access and did some tries. Unfortunately at the same time two PCs were restarted, making testing less clear.
    Anyway now updates can be done also by query. Horay!
    But this is true also on PCs which have Access Client Settings still configured as in post number 1.
    So my understanding is that restarting the PCs unlocked the problematic records. It's a temporary solution though. Soon the problem will manifest itself again.
    Am also puzzled by the behaviour of the locking system. If X record was somehow locked why the different outcome of updating queries vs direct editing?

    So you suggest to have 'default record locking' to 'no locks' and 'open databases by using record level locking' ticked?
    Tomorrow I can change the settings of all PCs in the company (about 25). Luckily it's a setting pertinent to Access, not the single user or Access file.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So you suggest to have 'default record locking' to 'no locks' and 'open databases by using record level locking' ticked?
    I've never had to mess with these and never had a problem
    why the different outcome of updating queries vs direct editing?
    short answer is I don't know - perhaps your error message which I asked for you to provide will give a clue

  10. #10
    ddd is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2009
    Location
    Italy
    Posts
    6
    Quote Originally Posted by Ajax View Post
    short answer is I don't know - perhaps your error message which I asked for you to provide will give a clue
    As soon as the problem will manifest itself I'll make a screenshot and add a translation. It's the usual error message box that reports how many records failed to update due to different causes (PK violations, concurrency locks, etc).
    Thank you for the suggestions!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-23-2018, 06:56 AM
  2. Replies: 3
    Last Post: 04-18-2018, 09:02 PM
  3. Replies: 5
    Last Post: 12-03-2013, 02:06 PM
  4. Replies: 3
    Last Post: 11-01-2013, 10:16 AM
  5. Lock Record for Editing on a Linked table
    By khalid in forum Programming
    Replies: 3
    Last Post: 06-14-2011, 08:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums