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

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!
TrialityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.