Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-04
1
Medium Priority
?
445 Views
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?

Thanks!
0
Comment
Question by:Triality
1 Comment
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 36915122
Hi,

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.

Cheers,

Christian
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

564 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