Do Not Have Exclusive Access to database error (1 Viewer)

[JazzKitten]

Hi folks
I post this after doing some reading of others' posts about the message that pops up about Exclusive Access.
This is happening to me and my colleagues, with a database that I created and has been split last week into FE and BE. The users all have their own copy of the .accdb FE and the BE resides in a folder on the network that the users don't have permission to open, but I do.
We are all using the latest subscription version of Access, 64 bit, on which the original database was created a couple of months ago.

Reading other people's posts on this, I'm not hugely concerned about the error as long as the data is safe, and all the forms seem to be sending the data to the BE, which is backed up every day. But it is an annoying error that is throwing my entry-level, data-inputting colleagues into thinking it's a major issue, and I spend a lot of time trying to reassure them. So, after reading about other people doing basically the same as me, and still getting the error, do I assume that it's one of those things that Microsoft haven't resolved yet, and that I need to get onto them about it?

I don't think I've done anything the wrong way, but please feel free to tell me otherwise.

The database has about 15 tables each containing a maximum of 150 records, all text and a few number fields. Some of the tables only have 10 records. The data is a classic customer database with names and addresses and products in separate tables using the junction table method to pull products in with customers. I've used lookup fields to create the relationships between the tables.

I've got forms for adding, editing customers and products. Nothing complicated, and I've tried to make it easy for basic users to add and save the data with buttons on the forms doing basic embedded macro actions like SaveRecord and CloseWindow.
I've compacted and repaired regularly, and make sure as I said to back up daily. I read somewhere on this forum that setting the "Compact on Close" option to on can cause this error so I have unchecked it in the options. Not sure why this would cause such an error but I'm just trying to eliminate everything.

Anyone's thoughts and opinions welcome. Also, apologies in advance if I've not given enough information about my database, happy to provide missing information if anything stands out.

Thanks all
JK

 

[theDBguy]

Hi JK. You said users cannot open the network folder of the BE. How did you do that? Because, the missing step I didn't see above is giving all users the necessary permissions to the BE folder.

 

[The_Doc_Man]

@JazzKitten, the rule for access via Access is that you must have "Modify" permissions (that's the high-level name) on any folder where an Access front-end or back-end resides, and you also need "Modify" on any files within that folder for which there is even a chance that the Access app will touch them (unless they are being opened in a way that allows read-only).

The "Exclusive Access" error occurs because of file locking and Access data locking. When you are opening either the FE file or the BE file, that will open a .LDB file in the same folder as the file you opened, and that file will have the same name portion as the file you opened. However, if you do not have the ability to create a file in that folder, you cannot open the shared-locking file (i.e. the .LDB file). When that happens, the only way that Access can protect your data is to open in Exclusive mode. But if your people don't even have READ/WRITE access, it can't do that either. At which point you get the error you noted.

The good news from this? Easy fix, mechanically. The bad news? If granting permissions is an issue, you need to come back to talk to us about your real needs.

 

[JazzKitten]

@theDBguy thanks for the reply. What permissions to the users need to the network folder of the BE? I thought one reason for splitting the DB would be so that users didn't accidentally access the data, and perhaps incorrectly I thought a protected network folder would be the safest place to put it. I have full permissions on the folder, and I put the BE there.
If I need to give the users permissions to the BE folder then I will do this. Thanks for your advice!
JK

 

[JazzKitten]

@JazzKitten, the rule for access via Access is that you must have "Modify" permissions (that's the high-level name) on any folder where an Access front-end or back-end resides, and you also need "Modify" on any files within that folder for which there is even a chance that the Access app will touch them (unless they are being opened in a way that allows read-only).

The "Exclusive Access" error occurs because of file locking and Access data locking. When you are opening either the FE file or the BE file, that will open a .LDB file in the same folder as the file you opened, and that file will have the same name portion as the file you opened. However, if you do not have the ability to create a file in that folder, you cannot open the shared-locking file (i.e. the .LDB file). When that happens, the only way that Access can protect your data is to open in Exclusive mode. But if your people don't even have READ/WRITE access, it can't do that either. At which point you get the error you noted.

The good news from this? Easy fix, mechanically. The bad news? If granting permissions is an issue, you need to come back to talk to us about your real needs.

Thank you for your reply, which came in as I was responding to @theDBguy !
I will read properly...
JK

 

[theDBguy]

@theDBguy thanks for the reply. What permissions to the users need to the network folder of the BE? I thought one reason for splitting the DB would be so that users didn't accidentally access the data, and perhaps incorrectly I thought a protected network folder would be the safest place to put it. I have full permissions on the folder, and I put the BE there.
If I need to give the users permissions to the BE folder then I will do this. Thanks for your advice!
JK

Hi JK. Users need read/write/modify/delete permissions to the folder. If you don't want them to "browse" to the folder location, don't give them traverse permission. Hope that helps...

 

[The_Doc_Man]

I named "Modify" as the high-level permission required for an Access FE/BE file. If you right-click the file to see Properties, then look at Security (file permissions), you would first see a short list of choices, of which Modify is just above Full Control in the list. If you clicked the "Advanced" button, you would see a total of 22 permission names. You don't need to go that far, though. "Modify" permission is like a "pre-set" that when you select it, you set or clear the advanced permissions you need. But remember that you need it on the FOLDER as well as on the files.

That is because Modify on the FOLDER means you can create or delete the .LDB file when needed. (The first user in creates it; the last user out deletes it.)

 

[JazzKitten]

Thank you both - I will get the network administrator to check those permissions tomorrow.
I really appreciate your advice @The_Doc_Man and @theDBguy

Also, this is a great forum! I don't think any of the other forums I'm in have such good relevant advice, and given so quickly! Thanks for tolerating my relative naivety with this.

JK

 

[theDBguy]

Thank you both - I will get the network administrator to check those permissions tomorrow.
I really appreciate your advice @The_Doc_Man and @theDBguy

Also, this is a great forum! I don't think any of the other forums I'm in have such good relevant advice, and given so quickly! Thanks for tolerating my relative naivety with this.

JK

Hi JK. You're very welcome. @The_Doc_Man and I were happy to assist.

Good luck with your project.

 

[Isaac]

It still seems a little murky, though, on what the problem(s) currently are.
If your users didn't have access to that folder where the BE resides, then your statement

and all the forms seem to be sending the data to the BE, which is backed up every day

..... wouldn't be true. In fact, nobody would be able to do just about anything meaningful to the data: most relevant in this case, editing it.
And, it seems doubtful that if they didn't have permissions to that folder, the "You do not have exclusive access" error would be the thing popping up. When I see that it makes me wonder if you have code that is modifying a table on the fly or something.

Anyway, will be curious about the results .. I could be wrong, just scratching my head on the theory that users don't have modify access to the folder, and yet the "forms seem to be sending the data to the BE".

 

[The_Doc_Man]

Yes, @Isaac, that was a spot of curiosity for me as well. But I answered assuming that something is still possible.

@JazzKitten - If you can get those permissions set up, great! If your network admin is a bit reluctant to open up that folder, try this:

In each user's folder on their private workstation, the copy of the FE is private to that user so it can be / should be owned by the person. For the FE, there will be no permission issues.

The BE in the shared folder can be owned by you if allowed, which gives you special privileges under the Windows file system. But If you have a true local domain, then you can have the domain/network admin create a group identifier. The network admin can add what is called an Access Control List (ACL) that grants MODIFY permissions to the group for the folder and the files it contains. (This is actually very easy because the admin grants the group identifier to the folder with MODIFY privileges and allows "inheritance" to occur, all in a single action.) You next identify the necessary users to the admin, who can make them members of the group.

If so, you can have this ACL that first grants FULL ACCESS to SYSTEM and to ADMINISTRATOR, whatever is the site standard for the OPERATOR group and BACKUP group, at least MODIFY for OWNER, MODIFY for this specially created user group, and whatever is your site's standard for the AUTHENTICATED USERS group and the EVERYBODY group. (Even if you don't know what this means, your admin will.)

In the Navy Enterprise Data Center/New Orleans where I worked, we had "DENY ALL" for the EVERYBODY group but it was always the LAST entry in the ACL. The idea is that you have all of those other groups first and if someone not in ANY of those groups tries to get in, you have that DENY to block the way. That isolates your directory to be usable by and visible to only selected and pre-identified users. This permissions scheme was approved by the U.S. Department of Defense for computers in a SECRET environment so it should work for most commercial companies.