Results 1 to 5 of 5
  1. #1
    Bennet is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    5

    Question Backend Record Locking

    I have a multi user DB split into a front and back end.



    I've found that when more than one user at a time attempts to append or update a few thousand records to the main backend table at a time, the append queries on both machines will report a success rate of about 99%, but a few hundred records will fail to write 'due to record lock'.

    May I request some information on how best to deal with this?

    To be fair to Access, the test I ran was a demanding one. Both machines attempted to append 90,000 records to the same table simultaneously. This is more data than I expect my users to throw at the backend at any one time, though it is not unlikely that 5 or 6 users might happen to try to append or update 4 or 5 thousand records simultaneously.

    Am I asking for something too demanding? Or do I just need to change some record locking settings somewhere?

    Many thanks.

  2. #2
    Join Date
    May 2010
    Posts
    339
    Is it possible that you have an index on your backend set to "Yes (No Duplicates)" and Access is merely throwing out the dups?

  3. #3
    Bennet is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    5
    The only field set to Yes (No Duplicates) is the primary key autonumber id.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272

  5. #5
    Bennet is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    5
    Thanks for the reply - record-level locking is already selected though.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-10-2011, 03:54 PM
  2. damaged object and record locking problem
    By rivereridanus in forum Access
    Replies: 1
    Last Post: 07-29-2011, 08:45 AM
  3. Record Locking
    By nyAccess in forum Database Design
    Replies: 4
    Last Post: 07-17-2011, 05:40 AM
  4. Record Locking
    By jlclark4 in forum Database Design
    Replies: 10
    Last Post: 06-15-2011, 02:22 PM
  5. locking data in subform after record save
    By Nixx1401 in forum Forms
    Replies: 24
    Last Post: 05-31-2011, 01:48 PM

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