Table lock doesn't get removed after user closes database and logs out

Posted on 2011-10-04
Last Modified: 2012-08-13
All -

I am running MS Access 2007 on Windows Server 2008 R2.  We have a terminal services setup and have 5 employees logging into and running analyses using MS Access.  They will often create a second database and link our pricing database as a table.  Sometimes the pricing database gets locked and then nobody can update the tables.  

When I try to compact the database I get an error that says:
"You attempted to open a database that is already opened by user 'Admin' on machine 'XXXXXX'.  Try again when the database is available."

Even after making sure all users have closed their databases and logged off, the issue is still there.  When I open the .laccdb file and see the file owner I can see who it says has the file open.  However, when I open task manager I am not seeing them running any applications and they are logged off.

Also, when I try to delete the .laccdb file it says "The action can't be completed because the file is open in another program."

Any ideas on this one?

Question by:Triality
    1 Comment
    LVL 24

    Accepted Solution


    as far as I understood your scenario you have a SQL Server which hosts the database, a "pricing database" which is an Access database having linked the tables of SQL Server and some users accessing this Access file using a second Access database to link the tables of the first one?

    First thing I see is that you try to use the task manager, did you use the checkbox "Show processes from all users" - and do you have the needed admin rights on the terminal server to see all these processes? As a terminal server hosts multiple sessions independent of each other you would normally only see your own processes and all processes of other users which are accessing your session. If they run their tasks on their own sessions you would need admin rights on the server to see all processes of other sessions, too. Moreover if a terminal session is not logged off but simply broken (for example by closing a laptop und carrying it away from the network) the terminal session stays opened for at least 30 minutes (default setting) before it automatically closes. During this time the user could reopen it again and all running tasks would still run. It's a long time ago that I administered a Windows server but as far as I remember there are special tools for terminal servers in the admin console.
    As it is nearly impossible to teach users to first log off before they leave the network I would give them the needed restricted access directly to the SQL Server to link the tables (or better: views/stored procedures) so they don't need a link to an Access file which unfortunately can get in such problems with multiple user access.
    Another workaround could be (because you only have few users) to simply copy the frontend (the pricing database) to each user's personal folder in their sessions. As they only need it to link the tables for creation of own databases they can experiment as much as they need. If someone leaves a connection open then it doesn't disturb any of the other frontends.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now