Access - can't save objects, says I need exclusive rights to the file

kim campion
kim campion used Ask the Experts™
on
I have a client I'm helping with a database.  There are about 12 people who use this file.  The file is split into front end and back end.  All employees access the same front end.  They are trying to create a new query or report, but are given the error message "Microsoft Office Access can't save design changes or save to a new database object because another user has the file open.  To save your design changes or to save to a new object, you must have exclusive access to the file".  

Nobody is opening the file with exclusive rights, and to create a simple query, they don't need this.  Any help is appreciated.  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
All employees access the same front end.
This is the reason why you're getting that error. Access manages a lockfile, and if there is more than one user in that lockfile Access won't allow changes.

Further, multiple users should NEVER share the same FE File (but they should all connect to the same BE file). The chances for corruption (data or otherwise) is much greater with multiple users sharing the same file, and it's just not good deployment practice.

To fix this, create copies of the FE and install a copy directly on each machine (either in the Documents folder, or the user's AppData/Roaming folder or AppData/Local folder).

User should then be able to open their own personal copies of the FE and create those queries.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
There are about 12 people who use this file

Then, by no means, should the individual user tamper with this. If they need to create ad-hoc reports, create a simple app connected with read-only access to the backend tables, and pass each user a copy. If a user creates a report to share, have one person to organize updating the common frontend after some kind of approval.

A frontend can be distributed automatically by launching a script. An example is here (can be reduced somewhat for your purpose):

Deploy and update a Microsoft Access application in a Citrix environment

/gustav
John TsioumprisSoftware & Systems Engineer
Commented:
Recently i had bizzare locks due to Antivirus...disable and check again...
Also how about transferring the application to your Computer and relink the BE to folder on your computer...does the error goes away..?
Jason clarkDBA Freelancer
Commented:
there may be various reason by which the error occur. Have a look here explains the cause of this problem and solution:
https://support.microsoft.com/en-us/help/824278/you-must-use-an-exclusive-lock-to-save-design-changes-to-database-objects-in-access-2003
John TsioumprisSoftware & Systems Engineer

Commented:
Not enough feeback...solutions provided were based on assumptions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial