Link to home
Start Free TrialLog in
Avatar of Eileen Murphy
Eileen MurphyFlag for United States of America

asked on

"You do not have exclusive access to database at this time"

Getting "You do not have exclusive access to database at this time" error upon opening Access 2016 application

Attempting to implement a DNS-less connection to a NEW SQL server and the front-end is locked from editing. I'm not sure what to check.

I rebooted the new server and killed any SPID's I saw that might have had things locked up -- no luck.

Before I create another new front-end and start over is there anything I can check beyond the usual?

Compacted/Repaired

Decompiled/Recompiled

Deleted all linked SQL tables

IF I OPEN THE APP WITH THE SHIFT KEY I AM ABLE TO EDIT FORMS -- still not sure what is going on.

Looking for some direction.

Thanks all!!
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

This generally means that two users have the same FE file open and one has opened it in exclusive mode.

First thing I would do is close your application.  Then check the Task Manager to determine whether you have any rogue instances of Access open in the Task Manager.  If so, then end those processes and try again.

If that does not resolve the problem, close your application and look for the laccdb file associated with it.  If it is still visible in the same path that the application is on, then attempt to delete it.  If Windows allows you to delete it, then you should be able to try your applicaiton again.  If Windows will not allow you to delete it, it is an indication that someone has the file open.  Check that it isn't you (note above) and then check to see if any other users have it open.  After all users have closed the file, you should be able to get into it and make changes.

Dale
.ldb file leftover!
Avatar of Eileen Murphy

ASKER

no .ldb file --

no other instances of Access running (with me as a user) -- I work on a network server logged in as myself -- and there is only one instance of my login -- I checked that too.

I also killed all instances of the SPID on the new SQL server I am connected to -- again -- here I am the ONLY user connect to this server -- it is brand new and no one else has any access to it.

I created a new  front-end app/db and am the only one in it. I think it's something to do with the new dsn-less connections to the SQL Server...
try using my add-in and see how that works for you.
Since you can work with the Shift key probably something in the code is creating unnecessary connections
Dale I shall try that
If I use the LTM add-in how does that work when I deploy the applications if the add-in in inaccessible to the user's copy?
Is there some way I am setting the connection to "exclusive" access when I use a DNS=less connection? Or is there something I should do up on the SQL Server to prevent the error I continue to get with being unable to make changes after I open the application, make the connections and then proceed to do some development work? It seems once I establish the connections I am no longer able to make changes to the application. Very strange...
I am under the impression that something in the code is done probably more than once...maybe should take it one step at a time...just link few tables like the way you used...check is it working ?...if not...something you missed...if it works...try the code one table at a time.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you shed some light on whether the user and password is/are required in the string? This client uses Windows Authentication but at one point used SQL Server Authentication - so on the old server there are dozens of users identified with credentials but there are also many who are not -- and since we use Windows Auth - I figured that was why.... On the NEW SQL Server there are users listed because that was copied from the old server... I'm just wondering about the issue I'm having -- not being able to go into design mode once the dsn-less connections are made -- and whether or not this might be why.... it's so weird.

I am still looking at the other alternatives -- but the linking is working great -- just the exclusive lock on the FRONT-END is confusing to me.

P.S. Once the DNS-less connections are made -- do they need to me made each time the user opens the application?? -Or can I perform the linking on the server and let them use their short-cuts to a batch file to copy the front-end down??
I'm not a SQL Server expert, by any means, but when the database was migrated, you indicate the list of users was copied from the old server.  Did these individual retain the same permissions they had to objects on the old server?  Were they assigned to database roles on the old server? are they still assigned to those roles on the new server.

These issues should not have anything to do with the "exclusive access to the database" issue you are experiencing, that is generally caused by two instances of the same database (front-end) file being opened.  I don't mean the same FE being run on different computers, I'm talking about two versions of the same file being simultantaeously open.
Except I created an entirely new app from a blank one -- I'm going to get to the bottom of this -- still working on it... Doesn't make any sense.

The SQL settings for all the users are the same -- I checked -- I don't think that matters though since there are users who have worked for them for quite a while who have no SQL permissions at all -- and since we use Windows authentication - I think that's obsolete. At least I hope I'm right about that.
no, SQL permissions are not obsolete.

Even when you use Windows Authentication, you must still assign users permission to use each object.  This is best done via one or more database roles and then assigning each user to a role.

Are you certain you don't have a rogue copy of the Access database open in your task manager?  
1.  Close all instances of Access running on your computer.  
2.  Open Task Manager and look in the processes tab for instances of MSAccess.exe
3.  If there are any open copies of Access running, click on them and then select "End Process"
Good morning! I wonder how the newer employees -- who have no SQL credentials at all are able to use the applications????

I have exclusive access to the servers last night -- killed all instances of Access -- rebooted both the existing SQL and the new SQL Servers -- Rebooted the File Server where the apps are stored before being copied to the user's local drives...

I have admin rights on all their machines so I was able to do all of these things and am still experiencing the locks. It has to do with the connections I'm opening. I am somehow not closing them?? I will keep you posted as I fool around with this today -- and welcome any and all suggestions from the Experts.

I am working on changing things -- have one app using Dale's LTM -- another using Pat's code from an old application she worked on which I assisted with and still have copies of.... I can't thank you guys enough for all your help on this.
Thanks to all who helped me! Migration to new SQL Server is complete and was successful.