User always appear to connect with exclusive rights

[kblinkhorn]

We have an access DB that has the default security on it so that anyone that connects to it they connect as 'Admin'. The front end of the app is using Access forms and the data is in sql so their are link tables defined. When someone opens the .mdb file, the NAVIGATION form comes up and they can goto where they need to go. The problem were having is if user A, opens the .mdb file it is always trying to connect with exclusive access. If no one else is in the app when user A opens it, she is able to but then if user B or user C and so forth tries to open it when user A was the first one to open it, they are locked out. Now, if user B opens it first users C and so forth can get in with no problem, but User A gets messages that she doesn't have exclusive rights and any changes if made could be lost. What I can't figure out is why User A keeps trying to connect exclusively when she is not even the user that created the application and when user A tries to open it, how do I change her to open it with shared access.

[weekend00]

check access options for userA.

Tools->options->advanced->default open mode: shared

[RuralGuy]

Does everyone have their own local copy of the FrontEnd? They should!
http://www.accessmvp.com/TWickerath/.../multiuser.htm

[kblinkhorn]

checked user A access options and she is set to shared mode. Everyone has a shortcut to the frontend and it works fine with all the other users.

[RuralGuy]

*Everyone* should not be using a shortcut to the same FrontEnd but have their own copy of the FrontEnd on their local machine. There are free utimities to keep these FE's up to date.

[kblinkhorn]

OK. put a copy of .mdb file on User A's computer and still the same result. When she opens it it gives her the message that she does not have exclusive access and if she makes changes to forms, tables etc they may be lost and no one else was in it this time. Any other suggestions? could it be something with the permissions on the sql tables? the messages are coming from access and she has to click ok to 8 of the same message before it allows her to continue.

Thanks for any help you can give.

[RuralGuy]

This *is* Access 2003 right? Linked tables to SQL Server on a central machine?

[pkstormy]

Since you said the backend is SQL Server and the tables are linked (via an ODBC DSN I assume), I wonder if this might be due to the ODBC DSN name being named slightly different on one of the other computers or in another mdb if that also has the same linked tables. If the ODBC DSN name isn't exactly the same name on every computer and for each mdb/mde that has those tables linked into it, you get symptoms as you've described (ie. it works for user A and user B cannot connect without problems but when user A closes out, user B can then connect without problems and vice-versa.) (or users A-C while user D has a problem and vice-versa). For example, if you had 2 mdb's which used the same linked tables and each mdb used an ODBC DSN which was named differently, this would cause problems as well.

Just a thought. I'm curious though, are any ODBC DSN names slightly different on any one of the computers or for any mdb's/mde's which might be using the same linked SQL Server tables? I'd check to make sure each mdb that has the linked tables is using the same ODBC DSN (ie. ODBC Administrator or if using the same linked tables in another mdb, Linked Table Manager). I've also seen a separate 'reporting' type mdb which had the ODBC DSN named slightly different for linking the tables into that mdb which caused problems.

I had this kind of issue once years back with SQL Server linked tables and ODBC which took a while before I discovered that all the ODBC DSN names must be named exactly the same (for any mdb/mde's that have the same linked tables). It gave all kinds of weird 'locking/exclusive' rights type messages on the forms or the linked tables just wouldn't open or opened with problems or data saving messages.

Note: Also make sure the 'save password' checkbox was checked when the tables were linked for any/all mdb's. If you're not sure if it was when you originally linked the tables, you may want to re-link the tables again making sure this checkbox is checked for each mdb that's used (although that wouldn't necessarily cause problems with user A being in it and user B having problems and then it works for user B when user A closes.)

* Also make sure the tables are refreshed in each mdb that has the linked tables, especially if you made any table design changes (one of the drawbacks to having a bunch of separate front-end mdbs and you make table design changes.) It's easy to miss one mdb which has 1 or 2 of the same tables linked into it in another application.

* But if everyone is using the exact same mdb/mde frontend and you're sure there's no other frontends which might have the same tables linked with a different ODBC DSN, I'd then suspect one of the user's ODBC DSN is named slightly different on their computer (check on each user's computer via the ODBC Administrator).

Also check to make sure each computer has the same MDAC version. I had an instance where an older version of the MDAC was on one of the computers which caused problems.

The key will be finding which user is causing the problem.

Here's an ODBC DSN creator I use in my frontends which automatically creates the ODBC DSN automatically for the user with the exact name as what the tables were linked with (I call the getlinkedtables function in my startup form):
https://www.accessforums.net/code-repository/odbc-creator-automatically-7547.html

It's worked like a charm for me throughout the many years and has saved me a lot of running around, manually creating ODBC DSN's for each computer. I've used it religiously for the last 10 years or so. You just have to make sure the user has permissions to create an ODBC DSN on the computer (note: if using Windows Vista or 7, you may have to deal with the UA to enable creating ODBC DSN's).

Another test for troubleshooting to narrow down if it's related to SQL Server/permissions is to clone the front-end (perhaps after re-linking all the tables, again, making sure the 'save password' is checked when linking) and give each of the users their own front-end to use and see if they have the same problems. If so, it's most likely related to SQL Server/ODBC. It could be a permission type problem then on SQL Server for the user although typically this doesn't cause problems for other users.

Do you know if you're using SQL Server authentication or Windows authentication for the linked SQL Server tables? When you say everyone is connecting via 'admin' I'm assuming you're using SQL Server authentication.

The last possibility I can think of is form design but I'm guessing that you've already troubleshoot that possibility and it would most likely not be user type specific or related with the symptoms you described. This could be tested by having any 2 users open the same record. SQL Server does a great job of managing who's in first/out last for the same record and if the form is designed incorrectly or there's specific recordset opening/closing, it would produce a lock type message which can easily be confused. If you're using stored procedures or triggers of any kind on SQL Server, this would also become apparent. As a rule of thumb, I never allow 2 forms to open based on the same table and both forms can update the recordset (but you'd then most likely get a 'data has been changed by another user' or 'data locked by another user' type error (and any changes would be lost) if this was the case and it wouldn't really be based upon user A having problems versus user B, C and D using it.)

Another possibility but unlikely is that the *.ldb file is getting locked by a specific user. Check to make sure all users have permissions to the folder where the mdb resides. If one specific user is locking the *.ldb file (which could be due to permissions to the folder or a bad network connection or other reasons such as a user 'sitting' in the mdb for a period of time), this would then 'lock' the mdb/ldb file and if another user tried to then open the mdb, it would say that the mdb itself is "locked by another" user when 'any' other user tries to open the mdb. Again, testing with different front-ends for each user would troubleshoot if this is the problem. But this seems unlikely given your posting and symptoms.

I hope this helps and isn't too much info. If you can relate any 'specific' type popup error messages you may be getting (if any to include the exact wording of the message), that would help us troubleshoot further. Locking type errors/problems are often confused with different issues and you really need to relate the specific message(s) you may be getting. What you've described as the problem is a little mixed and I've tried to address possible scenarios of each.

[kblinkhorn]

I checked and the users ODBC DSN name is the same as everyone elses except for its all caps and I cannot change it. User A's ODBC DSN name is "VETS" where everyone elses is "Vets" I wouldn't think that would matter.

Now after doing some more testing, I am finding that User A is always getting the message even if no one else has the application open. The exact message is "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." She has to click on this message 8 times and then the Navigation form appears and she can work with no problem. Everyone is using the same .mdb file and no one else gets this message when they open it even when others are in it.

[RuralGuy]

That sure sounds like a Permissions issue on the common PC to me, but I could be wrong...

[pkstormy]

I agree Allan.

[kblinkhorn]

Just to clarify, when you say the common PC do you mean the one that the .mdb file is on? If so, I have checked the permissions for the file and "everyone" has full access to it. I will make sure her MDAC is the same.

[RuralGuy]

OK. put a copy of .mdb file on User A's computer and still the same result. When she opens it it gives her the message that she does not have exclusive access and if she makes changes to forms, tables etc they may be lost and no one else was in it this time. Any other suggestions? could it be something with the permissions on the sql tables? the messages are coming from access and she has to click ok to 8 of the same message before it allows her to continue.

Thanks for any help you can give.

Are you sure she is opening the local copy and not the Shortcut to the common .mdb?

Just to clarify, when you say the common PC do you mean the one that the .mdb file is on?

The common PC would be the server in this case.

[pkstormy]

kblinkhorn,

You can also take a look at this method for opening your frontend:

https://www.accessforums.net/code-re...sier-7572.html

I use this method all the time and it has always worked well for me, especially using this on a frontend with SQL Server linked tables.

It would avoid any problems with exclusive type rights for a user.

[tallone]

Just had today the same problem for the first time after years and years without troubles.

4 PC (all XP and Office 2000) in the network sharing the same database hosted in the "server" PC, everything worked fine until this morning.

I solved it restating the properties/protection/advanced/proprietary/ to the "server/administrator" (listed as an option at the bottom of that window).

All PCs' Access was setted to "open in share mode".

Don't know how it happened, but one user suddenly became proprietary of that file instead of the default "server/administrator".

Let me now if it works with you too.