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

    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
    8,449
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,351
    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,484
    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).
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    677
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,351
    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 (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

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 - Senior Forums