Solved Access Database Locking Issue

[Pat Hartman]

So each user has a "<UserName>_Application name" front end is in the same folder on a server?
I'd suggest having the users in question copy it to their desktop and try running it from there to see if that fixes your issue. SHOULD be a very quick test.

No. The db is monolithic. The tables are currently embedded so the single database is what is shared.

 

[Mark_]

No. The db is monolithic. The tables are currently embedded so the single database is what is shared.

Missed that.

The other line of thinking I'd look at is "How is Machine 1 connected differently than the rest?", and "Where is the application being run (Start in)?"

 

[The_Doc_Man]

If you are looking at 10 copies of the FE file, each one dedicated to and only opened by one specific user, then there is an efficiency consideration but NOT a lock consideration for having a remote FE. IF, on the other hand, you have 10 distinct databases but multiple users could theoretically open the same FE at the same time, you have a potential corruption issue and potentially a much more severe locking issue. Your description is not perfectly clear on that fine point. Front-end files should NEVER be truly shared in the "simultaneously opened" sense of the word, but may be shared in the "everyone gets their own private copy" sense. This is CRUCIAL to Access FE stability.

EDITED BY THE_DOC_MAN: Your answer to Pat clarified this but I missed it. You have multiple monolithic databases. Part of my previous paragraph is still relevant so look it over.

The behavior you describe in post #17 is clearly (at least to me) a permissions problem on (hypothetical) machine #1. From your description, you are in a company that has a formal domain structure. In such a company, you can see something that in a smaller, less formal company is not so common. Each user will have a domain-based login. But in order to connect to the domain, EACH MACHINE will have an ID so that it can connect when it boots up, even before YOU log in. (Has to be that way because if your machine can't connect to the domain to validate your domain login, you can't ever log in.) Therefore, you might have to inquire of your IT people if there is an oddball setting for your machine's domain ID.

Remember my earlier discussion about user FRED, his assigned machine XDEPT003, and his domain ALLDEPT? IF you have this kind of setup, then Fred will have TWO operational logins - XDEPT003 (his machine's ID) and FRED\XDEPT003 (Fred's personal ID). And the key here is that XDEPT003 logged in first. IF for some reason Fred's machine has something different for its permissions than Fred himself has, it is POSSIBLE that the machine permissions are overriding Fred's permissions.

Were you able to use the Security control panel from Selected File (right-click) >> Properties >> Security >> Advanced to get "Effective Permissions"? That should take ALL applicable permissions into account when it reports permissions for the file or folder in question. Compare your #1 machine's effective permissions for the file and its folder with the effective permissions from any other machine.

OR a simpler thought: Using the method of Selected File (right-click) >> Properties >> Security you see a list of identifiers. Select the user's identifier for the person using that machine. Check the permissions for that identifier. NOW from that panel step into >> Advanced >> Effective Permissions (which is a tab frame) and look at the same user identifier to see effective permissions. IF the individual identifier permissions don't match the effective permissions then you have another Access Control Entry somewhere that is blocking you, OR (stated another way) the individual identifier permissions aren't being used because that user matches up with an earlier Access Control Entry (earlier in ACL order).

 

[Pat Hartman]

You have multiple monolithic databases.

I think it is one monolithic database shared among multiple users.

 

[Ajay289]

So each user has a "<UserName>_Application name" front end is in the same folder on a server?
I'd suggest having the users in question copy it to their desktop and try running it from there to see if that fixes your issue. SHOULD be a very quick test.

Thank you for your suggestion; it worked! The access lock file is deleted automatically after it is been removed. when its on the desktop.

However, I've encountered a new issue: I created a BE File and made a copy of the FE on the problem desktop, while keeping the BE file on the server. when the linked table is open in the Access file on this desktop, it locks the table on the server, preventing other Access files from accessing it. Conversely, if the linked table is closed in the problem file, the lock file remains on the server, but other Access files can access it normally.

 

[Mark_]

Thank you for your suggestion; it worked! The access lock file is deleted automatically after it is been removed. when its on the desktop.

However, I've encountered a new issue: I created a BE File and made a copy of the FE on the problem desktop, while keeping the BE file on the server. when the linked table is open in the Access file on this desktop, it locks the table on the server, preventing other Access files from accessing it. Conversely, if the linked table is closed in the problem file, the lock file remains on the server, but other Access files can access it normally.

Is this true regardless of who is using Machine 1?
Goes back to post 23. Is there a hardware difference between Machine 1 and the rest?
Is the network set up different for Machine 1? i.e. Machine 1 isn't connecting wirelessly while all other machines are hard wired?

 

[The_Doc_Man]

However, I've encountered a new issue: I created a BE File and made a copy of the FE on the problem desktop, while keeping the BE file on the server. when the linked table is open in the Access file on this desktop, it locks the table on the server, preventing other Access files from accessing it. Conversely, if the linked table is closed in the problem file, the lock file remains on the server, but other Access files can access it normally.

This is still a permissions problem. The question for THIS scenario is if the Machine 1 user exits, then another user from another machine comes in, works, and exits, does THAT delete the lock file? If it does, then the problem is ABSOLUTELY a permissions problem, and PROBABLY having to do with the folder holding the BE file. The "preventing others from using it" is probably because the machine cannot properly open the file in shared mode and so HAS to open it EXCLUSIVE mode to prevent corruption.

My earlier recommendation to verify that your Effective Permissions match the permissions granted by your Security Identifier (SID) still remains as a valid test, except now you have to validate it against only the BE file.

Is there a hardware difference between Machine 1 and the rest?

Mark, it's a valid question, but if the machine appears to run correctly (other than this locking problem), it is nearly impossible for the hardware of the machine to make a difference. Either you CAN run Windows without crashing it or you can't. If it can initiate the lock file, then it is working with the file system. The fact that the file can be manually deleted FROM THAT MACHINE (revealed in an earlier post) is evidence that the hardware is unlikely to be a culprit.

 

[Ajay289]

Thank you once again for your valuable suggestions. I am currently discussing the upgrade of our office version from 2016 to Microsoft 365, which will provide us with the latest version of Access. If the error persists after the update, I will definitely look into the permissions you mentioned.

I have gathered many great ideas on how to enhance our current Access database based on all the suggestions received. I appreciate your input and will keep you updated once the upgrade is complete. I plan to conduct a few test runs afterward to ensure everything is functioning smoothly.

Thanks for all the support! I will be back again with more questions, and I look forward to your continued assistance.

 

[Pat Hartman]

Thank you once again for your valuable suggestions. I am currently discussing the upgrade of our office version from 2016 to Microsoft 365, which will provide us with the latest version of Access. If the error persists after the update, I will definitely look into the permissions you mentioned.

this has nothing to do with the version of office.

Make absolutely, positively certain that no user is opening the database exclusively. Baring that, as the others have said, this is a permissions issue.

If the user has delete permissions, the lock file goes away when/if he is the last user to close the database.

Also, you seem surprised that the lock file for the BE was created in the same folder as the BE on the server. If it weren't, there would be no way for it to be used to control access to the BE file.

If you don't have a copy of the tool that shows who has a database open, search here to find it or someone please upload a copy. Otherwise, I'll do it in a couple of hours when I get home. I think @isladogs has a copy on his website.

Checking the users in the lock file may shed some light on the locking issue.

 

[Mark_]

Mark, it's a valid question, but if the machine appears to run correctly (other than this locking problem), it is nearly impossible for the hardware of the machine to make a difference. Either you CAN run Windows without crashing it or you can't. If it can initiate the lock file, then it is working with the file system. The fact that the file can be manually deleted FROM THAT MACHINE (revealed in an earlier post) is evidence that the hardware is unlikely to be a culprit.

Very true that it SHOULDN'T matter. Just doing due diligence.

 

[The_Doc_Man]

Pat brought up a point that I will explain to help in understanding what is going on.

Windows uses something called "Distributed Lock Management" - which in simple terms means this: If machine A hosts file AX, then machine A is responsible for managing that file. When you perform a network action, there is a services listener task on your target machine, probably called SvcHost.EXE, that interfaces with the file system to handle file requests for all hosted/shared files on that machine. File locking is managed over the network. File twiddling is managed across the network too. File permissions are ALSO evaluated in the context of the file's host.

Which means that the Selected file (right-click) >> Properties >> Security investigations should ALSO be run from the machine we have been calling Machine #1 to get valid comparisons. If you ask those questions from another machine, it is possible you won't see the right results because in that context, YOU are also a network user with respect to Machine #1. I should have thought of that earlier, but your file placement had me confused a bit.

 

[Ajay289]

Hi guys I have an update, I have Updated the office to 365 and the locking issue resolved. Thank you for the suggestion everyone