Results 1 to 12 of 12
  1. #1
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6

    Exclamation Multi User Access Database 2010 couldn't lock file error

    The db is split, BE and FE. I have 3 admins who have no problem accessing the database and 2 users who can access the db but intermittently.



    Users can access the db with no errors when the other 3 admins are not running the file.
    But once the admins are running the db, the 2 users cannot access even from the FE.

    The error message that comes out is usually error 3045 and couldn't lock file.

    I have done the following:
    1. Set the folder permissions of the users to full control to allow read,execute,delete everything.
    2. Had the last owner/pc of the BE lock file (.laccdb) to delete the lock file (but was unsuccessful because the lock file just couldn't be deleted, I believe this is where the problem is). And yes, I've made sure everyone close the db when I'm doing this.

    What am I missing here?

    I read somewhere
    "Users can still be restricted by read only access from the database file permission even if the directory contains write access." from http://www.access-programmers.co.uk/...p/t-80735.html

    Surely there is a better way to rectify this?

    Thanks

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I use this technique:

    https://www.accessforums.net/code-re...sier-7572.html

    and have never ever had a locking issue with multiple users. I have over a 100 users using my app without a problem.

  3. #3
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6
    Thank you. I followed your thread and got the sample script working on my local computer - duplicated the FE with the user's pc login id as it should. Haven't tested it out in the office network though.

    Take note however, that the error message I got from the users are as follows:
    "3045 -Could not use"\\[network address]\database\back-end database.accdb"; file already in use."

    So it is the BE not the FE that is locked. I am not sure the chances of solving the error mentioned above with the sample script in your thread which only tackles the front end db.

    Any other ideas are very much appreciated.

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Is the backend db set to be in a shared mode (look in the options). Also, Does everyone have READ AND WRITE permissions to this FOLDER and the ACCDB?

    You definitely DONT want to use the script on backend dbs.

    (Note it may lock this if the front-end is open and has a form with a table or that table open.)

  5. #5
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6
    Is the backend db set to be in a shared mode (look in the options).
    Yes. The default open mode is set to Shared. Looked it up in File>Options>Client Settings>Advanced.

    Does everyone have READ AND WRITE permissions to this FOLDER and the ACCDB?
    Yes. I have changed the users from read permissions to read and write permissions. Had the network guys to restart the server to make the changes effective as well.

    Still no luck.

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    So if you close the frontend, then you can open the backend without problems. If you open any frontend, the backend then becomes locked? Or does a specific table become locked.

    Since there are locked errors that are similiar but mean different things, could you state the exact wording of the locked error.

  7. #7
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6
    This error message comes out when I open the frontend and opens a form that has tables from the backend:
    3045 -Could not use '\\172.30.2.150\Pilot Database\back-end database.accdb'; file already in use.

  8. #8
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Typically a backend doesn't lock from multiple users in the frontend since it's not directly opened (although it does still create an *.ldb file).

    For permissions, also make sure users have permissions to any related root folders or other folders (that may have a table linked to that folder within the backend itself.) One user without sufficient read and write permissions will lock the backend for everyone. What's perplexing is that the backend seems to lock only when certain users open the frontend (see troubleshooting items below).

    The main goal though is to make sure there's no *.ldb file for the backend when all user's are out of the frontend (as you've discovered). I've had a few rare cases where the *.ldb file had to be deleted (by the network admin) although usually you can't directly delete this file. If you can't get rid of the *.ldb file for the backend when everyone is out of the frontend (even after compacting/repairing), you may have to consider a backup or create a new mdb/accdb backend and import the tables into it (again, make sure to refresh all linked tables in all frontends) although you shouldn't need to do this in your case. If an *.ldb file still exists for the backend and you're absolutely sure everyone is out of the frontend, this is the source of the locking problem. Disregarding all the items below, it sounds like your issue (as per your 1st post) is still a hanging *.ldb file for the backend or possibly one user still doesn't have appropriate permissions (and is still in the app - see #5 below). You need to delete that *.ldb file when all users are out of the frontend and the backend isn't opened. Again, try having the network admin delete it. Usually they have permissions that others don't and can delete it.

    Here's a couple of things to try for troubleshooting if the *.ldb file on the backend re-appears and all users are out of the frontend or the locking problem persists (I tried to list everything I could think of from my experience).

    1. Make sure to refresh all linked tables in all frontends. If one of the frontends doesn't have a refreshed tableset, this could cause problems for other users and lock a backend. You may also want to consider deleting and relinking the tables in all frontends.

    2. Have the user's open the frontend but close out of "ALL" forms that have a table as a recordsource. If all user's can successfully open the frontend (use the shift key to open the frontends), that might indicate that a form or forms which are opening based on the same table and somehow locking the backend mdb itself. The culprit form (or coding for the form) could then be detected by opening certain forms and seeing if another user can open the frontend (again using the shift key). Try designing a simple mdb/accdb with 1 form based on a linked table and then have multiple users open that mdb/accdb and form. If there are no locking problems on the backend, you may want to look at coding/form design in your main frontend.

    3. Look at table relationships in the backend. I've seen cases where an overly-complicated relational schematic doesn't work well with linked tables in a frontend. Check also for orphaned records in tables which broke the referential integrity of the relational schematic (I've only seen this happen once in 20 years but it did cause problems.) Without knowing the complexity of your application, I have to put this in here. You could also link in 1 table at a time into a frontend and have users test opening the frontend (without any forms or coding) until you get a locked error (although this is unlikely and time-consuming). But if you do get a locked error after linking in a certain table, then take a serious look at the relational diagram and table structure (this is then a big red flag). (move this item #3 to bottom of troubleshooting list after #8).

    4. Look for recordsets in code which are opening but not closing (ie. rs.close, set rs = nothing is missing). A recordset opened in code which doesn't have the close coding often plays havoc on things and 'can' lock a backend. You can also open up Task Manager and view the MSAccess process while opening forms. If you see a big jump in the memory and it never stables off, this is usually a sign of a recordset not releasing it's memory or closing. As a habit, try to reuse the same name for your recordsets (ie. rs, rx, ry, rz) but be careful not to open the same recordset when it is already opened. I once had a frontend that continued to grow past 100 megs and kept growing (usually it should stable off between 25 and 75 meg depending on the different variables/recordsets used.) I also try to re-use the same variable names as well. I typically get concerned when approaching 75 megs.

    5. Since it sounds like all users did not have both read and write permissions to the backend, it's also possible that there's a 'hanging' *.ldb file for the backend. When everyone is closed out of the frontend, look to see if there's an *.ldb file for the backend. There shouldn't be. Make sure to compact/repair the backend (after closing the backend, this should also make the *.ldb file go away if everyone is out of the frontend). If the *.ldb still exists after compacting/repairing (and then closing the backend), make sure someone still doesn't have the frontend open. I've seen a few cases where with Citrix, the user still had an MSAccess process running for the frontend even though they didn't have it showing in their taskbar. I've had to have the network admin kill the MSAccess process for that citrix user. But usually the culprit is that someone still has the frontend open somewhere (whether they know it or not.)

    6. Test with opening/closing user's frontend (per user) and then checking to make sure the *.ldb file on the backend goes away when they close the frontend. I've seen some networks where the network was a bit unstable and didn't like to refresh itself properly and caused issues. If possible, try testing the backend on another drive and frontends with tables linked to that backend.

    7. Make sure everyone is mapping to the backend correctly. Ideally, I prefer to not use any spaces, dashes, or non-alpha characters in the path or mdb/accdb filename. Although this shouldn't make a difference, I have seen a few rare cases where it does. When troubleshooting a locked backend, I can often narrow it down to specific users who map to the drive differently than other users.

    8. Multiple MSAccess versions - if some users are using MSAccess 2003 and others 2007 or 2010, test to make sure it's not an issue with MSAccess versions. I have seen cases where users opening an MSAccess file with 2007 or 2010 caused problems with others users opening that same application with 2003 (but typically this is not backend related, only frontends and users sharing the same frontend.)

    9. MDAC versions. If all else fails and you still can't find the problem, look at the MDAC versions on everyone's computer. Again, rare, but I have seen a case where 1 or 2 users had different MDAC versions and this was problematic (but making sure everyone has the latest service pack should take care of this.)

    I hope this helps. Please post back if any of this helps in troubleshooting. I'm curious.
    Last edited by pkstormy; 06-28-2011 at 08:00 AM.

  9. #9
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    One last note on the vb script. This script basically does a copy/paste of a file (but adds the user's loginID and opens the pasted copy with the user's loginID.) The script though probably isn't going to resolve your issue with the backend *.ldb (I apologize for leading you in this direction when it sounds like your issue is with the backend itself.)

    It's designed to overcome the limitation of opening any MSAccess file with more than 3 users on frontends. With MSAccess, an *.ldb file is created when a user opens that MSAccess mdb or accdb. The design of the *.ldb is to keep track of who's currently using the mdb or accdb file. Multiple users sharing the same *.ldb file is suspectable to network issues, permissions problems, or a bad Office/MSAccess installation for a specific user, all which can affect other users when that user opens the application. Hence, the script makes it so each user is essentially in their own mdb/accdb file without conflicts with other users sharing the same *.ldb file (which is usually the culprit of locked issues unless the forms are designed poorly). But the script is for frontends and doesn't really pertain to backend problems since a backend isn't opened directly by the user (indirectly through the linking of tables). If you only have 3 users ever using your application, you may not need to use this script. For me, I have a hundred or so users and make code changes daily so the script is very beneficial for me. I use it even if I only have 3 users since I still gain benefits from using it, especially when managing multiple MSAccess applications.

    You may also notice that the access program works a little more efficiently using the script (the user should use the script each time they want to open the mdb/accdb). If you have routines which often create temp type tables and tend to bloat the frontend, this is another advantage of using the vb script since the user is getting a fresh 'unbloated' copy of a source frontend file. Another alternative is to put a frontend copy on each user's desktop (but this makes it more difficult to keep code updated and will periodically need to be compacted/repaired if there are temp tables.) I mainly designed the vb script so I could easily update code on the shared drive.

    An mde or accde should also be created for all frontends to improve performance. Since I manage multiple MSAccess applications, I typically create a 'Databases Main Menu' mdb which has buttons for each program that launches the appropriate vb script. When I need to make code changes, I make my changes in my source mdb file (on my development drive), create a new mde and copy that mde to the appropriate location (ie. LUName location in the script). All that's left to do is then send an email out telling users to close out of the program and re-open it again (always using the script or button on the Databases Main Menu.) This setup allows me to freely make coding changes at any time while users are in the application and I don't need to make them close out (unless I'm making critical coding/table/field changes). But I rarily need to make users close out of the application so I can make code changes.

    The biggest thing I like about the script though is that I've never had to worry about one user locking another user out of the application which entails hunting down every user to have them close out. I find that users will often 'sit' on a record in a frontend (and will see developers try to overcome this by automatically closing the form - a poor technique). If sharing the same frontend file, the *.ldb file eventually get's confused and locks itself producing the infamous 'locked by another user' error. Network hiccups are also a culprit with *.ldb files. An important note though is that there are multiple 'locked' type errors and it's not always related to the *.ldb file itself. If you're interested, an example showing other 'locked' type errors (due to form/code design) which can get confused with a locking *.ldb file can be found here: https://www.accessforums.net/code-re...tion-7761.html

  10. #10
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6
    Thank you so much for your time pkstormy. At least I have a guide to fix this locking error. Will keep this thread updated when I find something new/solve it!

  11. #11
    lzr1801 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    6
    I followed closely to what you suggest.

    Repair and compacted the backend database
    Used the sample script to create a fresh copy of front end database for each user

    And everything is working normally.
    Thank you!

  12. #12
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Glad to hear that. Thanks for the post!

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

Similar Threads

  1. Replies: 7
    Last Post: 02-22-2013, 04:57 PM
  2. Replies: 12
    Last Post: 06-16-2011, 01:35 PM
  3. make the access file lock
    By naggappan in forum Security
    Replies: 1
    Last Post: 03-21-2011, 07:06 PM
  4. Design multi user database - avoid simultaneity transactions
    By snoopy2003 in forum Database Design
    Replies: 2
    Last Post: 02-27-2011, 12:03 AM
  5. Access 2010 - Multi Currency
    By Jismaeel in forum Access
    Replies: 1
    Last Post: 12-20-2010, 03:56 PM

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