Results 1 to 11 of 11
  1. #1
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155

    Record Locking


    I have about 15 users in each table at once. I currently am working on splitting up the database so there are less users in the database at once. What is the approiate recorded locking system I should use?

    Currently they are getting recorded locking errors.

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You shouldn't be using any locking. What you should have is in the options you need to have set the default locking to NO LOCKS and check the box that says to use record level locking. So, what should be happening is OPTIMISTIC LOCKING where it locks only long enough to commit an update.

    And what do you mean split the database up? Do you mean split the database into two parts (backend with tables only that resides on a server and then frontend which has everything else and that a COPY is on EACH USER'S COMPUTER)? That is how it should be set up.

  3. #3
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155
    Yes Bob,

    Originally I did not split it. And the single database was having 70+ users at once. (Using different forms and tables). I recently split the database into 3 mirrored versions. A group of people use Database #1, a group uses Database #2, and a group uses Database #3. I am now going to Split each database to a front and back end and email the mde file to each person for them to use.

    Where is this check box you mentioned for level locking? I went into the form properties. Is it somewhere else?

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    The checkbox and locking options are located here (under Tools > OPTIONS):



  5. #5
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155
    So I checked my settings and this is how it was already set up. If I split front end and back end, do you think it will solve most of my issues (and what it can't solve can be fixed by closing out and coming back in?)

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Normally splitting will help with those issues. However, there can be other things that affect the records. If you have forms which are open at the same time which are bound to the same table, either directly or used in a query which is bound to the form or used in a Combobox or Listbox's Row Source. All of that have potential implications. But the first thing is that it definitely NEEDS to be split with users using a SEPARATE copy of the frontend. That should solve many issues.

  7. #7
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155
    Thank you for your help. Hopefully this does do the trick!

  8. #8
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155
    I was testing the front-end and it takes long to load. Is there a reason for this?

  9. #9
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by jlclark4 View Post
    I was testing the front-end and it takes long to load. Is there a reason for this?
    Sorry to say, it is likely that not-so-good design has contributed to it.

    But you might go through this FAQ to see if you can make some changes which will help:
    http://www.granite.ab.ca/access/performancefaq.htm

  10. #10
    jlclark4 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Dec 2010
    Location
    North Carolina
    Posts
    155
    I am pretty sure its the folder inside a folder inside a folder and the network having to verify rights to the BE.

    Thanks for the info!

  11. #11
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by jlclark4 View Post
    I am pretty sure its the folder inside a folder inside a folder and the network having to verify rights to the BE.

    Thanks for the info!
    One thing that I've noticed that can help is if you link to the tables using Universal Naming Convention (UNC) instead of by drive letter. You can do that by typing out the string that it would be

    \\ServerNameHere\FolderName\Folder2Name\Folder3Nam e\AccessFile.accdb

    and then you copy that and then use the LINKED TABLE MANAGER. You open it up, select the applicable tables that are from that location and then select the checkbox for ALWAYS PROMPT FOR NEW LOCATION and then click OK and then paste that into the bottom area where you would normally see the file name - like this:


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

Similar Threads

  1. locking data in subform after record save
    By Nixx1401 in forum Forms
    Replies: 24
    Last Post: 05-31-2011, 01:48 PM
  2. Replies: 6
    Last Post: 05-04-2011, 06:17 PM
  3. locking it down
    By BAX5127 in forum Access
    Replies: 0
    Last Post: 03-22-2011, 06:51 PM
  4. Locking Down Form
    By jo15765 in forum Forms
    Replies: 5
    Last Post: 11-16-2010, 04:44 PM
  5. Locking fields
    By Icewolf0927 in forum Forms
    Replies: 3
    Last Post: 09-23-2010, 12:01 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