Results 1 to 8 of 8
  1. #1
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    MSAccess locked users

    Whenever you open an MSAccess file (*.accdb, *.accde, *.mdb, *.mde) a locking file is created (*.laccdb, *.lde). Theoretically, this file is supposed to help with locking problems and track who is using the MSAccess database.



    But like all other Microsoft files, when more than 3 users open the file, it becomes problematic, especially if 1 of those users connection is suddenly lost.

    The typical resolution to this is to have all users close out of the MSAccess database and then 1 user opens it and compacts/repairs the database. After that user closes out, if the locking *.laccdb file goes away, it's then ok for everyone to get back in.

    The problem though is what the user who got suddenly disconnected was doing. If they were entering data via a form, it's possible the form is also corrupt. In the older days, the table would often get corrupt.

    Here are some possible solutions to resolve a constant locking issue:

    If using MSAcces tables, split the MSAccess file into a backend/frontend if possible. Put the table(s) in the backend MSAccess file and link them into a new MSAccess frontend file. Have the users only open the frontend. That way if the MSAccess frontend file (or a form) becomes corrupt, there's a good chance your main data table is still safe.

    We're not done here though!

    We want some way to 'clone' the frontend and then open the cloned frontend. MSAccess doesn't really do this by itself when you open the MSAccess file. There are a couple of ways to do this.

    1. You can design a *.bat file and use the xcopy command to copy the frontend to the user's desktop computer (ie. hidden folder such as c:\Databases). I would only recommend this method if using a SQL Server linked table and an ODBC connection. The only problem is that you can't utilize the Windows LoginID in the cloning process in a *.bat file so you have to clone the frontend to the user's desktop computer.

    2. You can design a *.vbs script. The advantages of designing a vbs script is that you can grab the Windows LoginID and use that in the cloning process (hence: MyDB.accdb becomes MyDBpakohn.accdb). See https://www.accessforums.net/showthread.php?t=7572 as an example vb script. Note that this is a simple vb script to clone/open an MSAccess file (or any other file). It has no confidential information in it and uses the .net framework like other vb scripts. You can edit the *.vbs file with a simple notepad editor.

    Note that you'll also want to follow simple naming rules for your folders and files. Folders/Files with spaces and non-alpha characters become problematic in any kind of script/code and often require adding special characters to work.

    If using an MSAccess backend, see the attached example and the DataEntry form showing how to design an unbound form.
    If using a SQL Server backend, see the attached example and the module called: "Create ODBC" to automatically handle creating/updating the ODBC.

    Also included in the example is a method to close an MSAccess form after 10 minutes of inactivity, an export routine to export queries easily as excel files (with dates/times), a query reporting and view form to see data in an excel style format (with a timer to close after 10 minutes of inactivity) and a query creating form to allow the user to create/save their own queries (temporarily). There is also a module to remove the MSAccess background and make it a true pop-up style application.

    The excel style MSAccess form in this example lets users filter/query data via the column headings and a query can be designed as a snapshot (non-editable) or General (users can make edits). You can also freeze specific columns in the query. Feel free to use the technique and link your own tables/queries into the interface.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should never allow > 1 person in the same FrontEnd db.
    you SHOULD split the tables from the FE and give EACH person their own copy of the FE.
    This prevents the lockup and corruption.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree 100% with Ranman's comments.
    The point about 3 or more users is completely wrong.

    Oh, and for the record, no lock file is created if a database is opened exclusively
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree 100% with Ranman's comments.
    The point about 3 or more users is completely wrong.
    Ditto. Ditto (had to say it 2x 'cause forum said message was too short).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    The main point of my post is that every user should be in their own front end (even if there are only 2 users!) I wrote 3 users because I believe that is what Microsoft recommends (excluding citrix/vpn remote connections) but I am a very big fan that each user should be in their own frontend. Click the link I posted in my original post for an easy way to do this.

    My apologies if that didn't seem clear.

    The methods I described show how to easily replicate the front-end so every user is in their own front-end.

    The example MSAccess file shows frontend coding techniques to help prevent additional problems (locked records) and help make the frontend code run efficiently. It includes (simple) coding methods to use and some handy data viewing forms for the user to easily query/view their data safely.

    There are also a few handy modules I copy/use in every MSAccess front-end I create (example: common dialog, remove MSAccess background, create/update ODBC and other neat, good to have functions I've created/collected/modified over the last 20+ years (see the code bank on this forum for other examples I've posted.)

    One last note, if you use the holding down the shift key trick when opening the example MSAccess file (to bypass the startup code), you may only see it open in your taskbar so don't keep clicking to open the file. Then just click on it in your taskbar to bring it to the foreground (an MSAccess glitch.)

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You have posted the 3 user comment before and I remember making the same objection then.
    It is NOT what MS recommends nor ,to my knowledge, has it ever been so.
    The way you wrote the first part of the post came across as seriously incorrect misinformation.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by ranman256 View Post
    you should never allow > 1 person in the same FrontEnd db.
    you SHOULD split the tables from the FE and give EACH person their own copy of the FE.
    This prevents the lockup and corruption.
    Can you share any more info on how to best do this? When you say their own copy of the front end, does that mean just copy the actual file to their computer and have them access database through that?

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Pawtang View Post
    Can you share any more info on how to best do this? When you say their own copy of the front end, does that mean just copy the actual file to their computer and have them access database through that?
    Yes - Each user should have a local copy of the front end. The back end data should be on a suitable network location.
    There are many threads about "auto updating a front end" to keep users with the latest copy of the FE Database. Have a search on here.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Locked Exclusively for Network Users
    By RustmanZ in forum Access
    Replies: 6
    Last Post: 08-18-2019, 08:09 AM
  2. XML - MsAccess
    By scopel in forum Access
    Replies: 1
    Last Post: 07-11-2019, 05:05 PM
  3. Replies: 5
    Last Post: 02-15-2018, 03:11 AM
  4. Replies: 2
    Last Post: 11-04-2017, 05:06 PM
  5. Learn MSAccess by playing MSAccess Jeopardy
    By pkstormy in forum Sample Databases
    Replies: 4
    Last Post: 11-17-2016, 07:27 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