What determines if someone has exclusive access to a database?

[Alc]

This one has been bugging me for a while but, as it's not causing major problems, has never been top of my 'to do' list: How does an Access database know if a user has exclusive access or not, ad so can or can't make changes to forms, etc.?

I was always under the impression that the lock file was used to detect how many people were connected and I understand how these files get created, updated and deleted. However, there must be something I'm not following as I'm getting a message about not having exclusive access for a locally stored database not used by anyone else.

What could be occurring within the database to 'trick' it into thinking multiple people are logged in?

 

[Ranman256]

you dont get exclusive rights unless you open it that way, OR
if a user is in a form in a NON split database.

is your database split? it should be for multi users.

 

[Alc]

Thanks for the reply.

Sorry, by exclusive access I just meant that I was the only person logged in. I should have said sole access.

This one keeps acting as if someone else is also logged in and won't allow me to make changes. If I log out, then log back in with the shift key held down, I can make changes. Once I open a form, if I try to change anything I get the message that 'User xxxx (my PC name) has the database in a state that doesn't allow changes to be made'.

Yes, it is split. Almost all the databases I'm currently work with are split, but I can open and edit any of the others at will.

 

[The_Doc_Man]

First thing I would look for is to assure that the permissions are correct for the FE, since that is where the forms would be located.

Second question (but equal in effect) is - how do you GET those permissions? Is this a domain environment and are you a member of a group that has MODIFY rights to the BE file - or are they assigned manually per user (which is the wrong way to go)? And are you the owner of the FE file (which would happen automagically if you copy the "master FE file" to your local folder.)

 

[Ranman256]

also, a user can close Access, but some process stays open, thus keeping the locks open.

 

[The_Doc_Man]

Ranman, are you thinking about an application object that Access started, or perhaps a script?

Locks would normally be released by Access if it owns any outside of the file locks on MSACCESS.EXE, the .MDB/.ACCDB file, and the .LDB or whatever other files are part of the app. At that point, any other "interested" processes would inherit ownership of the locks. This even applies to files opened by access if it were reading or writing a file from VBA, because an Exit/Quit implies file closure for all files opened by a process. It goes into what is called "process rundown" during which all opened handles for that process are closed out where possible.

However, application objects are a special case because Access creates a complex in-memory structure to commune with its apps via automation methods (Component Object Model diddling, as it were). The rundown process can't so easily close those files, and I have seen cases where that still-open application object would cause a serious hangup.

I have also seen cases where the incorrect rights were granted to the folder. If the person opening the database has modify rights on the files in that folder but does not have modify rights to the file itself, then what happens is that if the user tries to open the database file, that implies a CREATE on the lock file in the directory, and remember that the directory can in theory have different rights for itself vs. the files it contains. If you can't create the lock, you can't do anything to save data within Access.

 

[Ranman256]

I have exited access, But the lock file stayed.
Then when it reopened, it says another person has exclusive rights. Which is me.

 

[Alc]

Much to my shame, I was pulled onto other tasks after posting this question and was never able to devote time to get to the bottom of it. Now, however, I'm back working in the same db and would like to try to resolve the problem before something more urgent comes up.

In summary:

• I have an Access front end that connects to an Access back end.
• I do the development work in the front end on my C drive then make a copy for each person who's going to be using it. This is the case for each of the twenty or so databases I work with and I'm only getting this problem with one of them (created before I got here, but so were some of the others).
• At the point this problem is occurring, I am 100%, definitely the only person using the database. It's a local copy and early morning. Nobody else is in the office, so even the shared back end file is only being accessed by me.

Problem:
If I open the db then use Alt + F11 to go into the code window, I can edit the code.
As soon as I open a second form (any one), go back into the code window and try to make a change, I get a message

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.

I've tried:

• Opening the db normally,
• Going via the Access program and opening it exclusively
• Holding down shift when opening it and then opening the main form manually.
• Changing which form opens when the db opens
• Commenting out all code that runs when the second form is opened
• Running a compact and repair
• Creating a blank db and importing everything from the original

No use.
Every time I go to a second form and try to make a change, I get the same problem.
The database is working exactly as it should, from a user's perspective, it's just locked to me if I try to make edits, so I need to log out and back in every time I want to change anything.

I could upload a copy of the db here but the data is all confidential and I'm not sure how much use the other objects and code alone would be?

 

[theDBguy]

Wow! It survived for over 3 years like that? When I work on a development copy of a db, it is connected to a development copy of the BE as well, stored on the local drive. I didn't see you mention that you are disconnected from the live BE when you do your design changes. Are you?

 

[Alc]

Wow! It survived for over 3 years like that? When I work on a development copy of a db, it is connected to a development copy of the BE as well, stored on the local drive. I didn't see you mention that you are disconnected from the live BE when you do your design changes. Are you?

Thanks for the response.
In order:
Yes, it's survived that long. As I say, from the users' perspective nothing is wrong. It's only when changes need to be made. Any on the last few years have been small, so I made them and moved on to the next thing. It's only now that I have to make more significant changes and find constantly opening and closing the db to test each change is a pain.

It varies. If I'm also changing tables in the back end , I make a local copy. If I want to check that reports run correctly, I leave it connected to the live back end.

 

[theDBguy]

So, are you saying the problem occurs whether you're connected to the live BE or the test BE? So, if you create a demo version of your FE and BE for us to test, would it show the same problem?

 

[Alc]

So, are you saying the problem occurs whether you're connected to the live BE or the test BE? So, if you create a demo version of your FE and BE for us to test, would it show the same problem?

I would assume so, yes.
Looking at the db now, I think I may be able to recreate it with a very stripped down version and some nonsense data.
What form is preferred for uploading? Zip files?

 

[theDBguy]

Yes, zipped up please. Hope we can help you...


PS. If there's a particular situation when the error occurs, please include step-by-step instructions to duplicate the issue. Thanks.

 

[Alc]

Yes, zipped up please. Hope we can help you...

I'll get on that now (before I get pulled onto something else).
Many thanks.

 

[theDBguy]

I'll get on that now (before I get pulled onto something else).
Many thanks.

Hi. We've crossed posts. Please see the "edit" in my previous post. Just needed to remind you to include steps for duplicating the problem, if necessary, to help us help you. Cheers!

 

[Deleted member 147267]

Did you ever try altering design on another pc?


This one is a stretch, but it happened to me with a db uploaded by someone who was posting about an unrelated issue. The db code made an ADO connection to itself on startup by way of a startup form; which is just weird. Thus to Access, it appeared that the database was opened by someone else. As soon as I stopped the startup form, no problem.

If you logged onto another pc and it doesn't behave differently, you can rule out hardware - assuming you've been using the same one for 3 years anyway. You could also have someone else log in on yours if they have the same folder/file permissions. If no issue under their login, it points to your Windows profile. These notions are based on the understanding that the issue is as repeatable as you seem to be saying; i.e. it happens every time as you've described it.

 

[Alc]

Did you ever try altering design on another pc?


This one is a stretch, but it happened to me with a db uploaded by someone who was posting about an unrelated issue. The db code made an ADO connection to itself on startup by way of a startup form; which is just weird. Thus to Access, it appeared that the database was opened by someone else. As soon as I stopped the startup form, no problem.

If you logged onto another pc and it doesn't behave differently, you can rule out hardware - assuming you've been using the same one for 3 years anyway. You could also have someone else log in on yours if they have the same folder/file permissions. If no issue under their login, it points to your Windows profile. These notions are based on the understanding that the issue is as repeatable as you seem to be saying; i.e. it happens every time as you've described it.

Nice idea, but the company replaces our laptops every year, so the one I'm using now is different from the one the db was built on and the one where I found the initial problem.
Hopefully, once I've made a cut down version, one of the big brains here will spot something that dullards like me miss.

 

[Deleted member 147267]

Still, I would advocate someone else logging in on yours if a posted db doesn't turn up anything. Or maybe even before going to the trouble of building one you can post.

 

[Alc]

Still, I would advocate someone else logging in on yours if a posted db doesn't turn up anything. Or maybe even before going to the trouble of building one you can post.

Just tried it, same result for that user.

 

[Alc]

Okay, I created a very cut down version with gibberish data, so I could upload it. Ignore any errors outside those generated by the following steps.

The steps to recreate the problem:
1. I open the db
2. Alt + F11 and make any change to the code. No problems.
3. Click on <Show Toolbar>
4. Alt + F11 and make any change to the code. Still no problems.
5. Click on <Uncovered Items>
6. Alt + F11 and make any change to the code. Still no problems.
7. From the ‘Analyst’ drop down, select ‘RB’
8. From the ‘Time and Exceedance’ drop down, select ‘All open items’
9. You should see one record displayed in the top subform
10. In the bottom right subform, type a 12 digit value into the QIC field e.g. 1234567890-12
11. Tab out of that field
12. The VB code errors. If you try to make any changes to it, you get the error message “Run time error 3734: The database has been placed in a state by user ‘Admin’ on machine ‘{my PC name}’ that prevents it from being opened or locked”

Edit:

Part Hartman pointed out that the original file uploaded contained errors. These have been corrected and a new version has been uploaded to replace the original.