Stop Access warning message

[TomJ58]

Hi all,

I have an Access 2010 db on a shared network drive. All users accessing the db are running Windows 7 OS and have a licensed corporate version of Access 2010. The advanced options "Shared", "Edited record" and "Open dabases by using record-level locking" are turned on. Additionally, the locking time limits are set to their default levels. If a user selects the save icon after making additions or updates, the following waring message appears:

Costar Job Database can't save design changes or save to a new database object because another user has the file open. To save you design changes or to save to a new object, you must have exclusive access to the file.

Since I developed the db, I am the only one that makes design or object changes. I know that users don't need to select the save icon to update the db, but I don't want them to "freak out" if they select save and see the message.
Is there any way to prevent the message from being displayed?

 

[Beetle]

Does each user have their own copy of the front end on their machine or in their local profile, or is everyone sharing the same copy on the network? Hopefully the former. If the latter then you are just asking for problems/corruption.

 

[TomJ58]

They are all sharing the same network copy of the db.

 

[Beetle]

Then that's the problem. Each user needs to have their own copy. Having multiple users share one file is one of the most common causes of corruption, along with other problems like the one you are experiencing.

Just to be clear, when I say their own copy I am talking about the front end only of course. The application should be split so that there is one copy of the back end (tables and relationships) on the network server and multiple (one for each user) copies of the front end (queries, forms, reports, modules) stored locally on each users machine.

 

[TomJ58]

Then how are the front end and back end linked?

 

[Beetle]

The link itself is created by the Database Splitter that is built into Access. You can find information on splitting here. Once the split is complete, and the back end is on the network, then the file you are left with is essentially your master design copy of the front end. You should immediately make at least one backup of this file. This is not the file you should distribute to your users. You should use this file to make a separate MDE/ACCDE (depending on which version of Access you are using). An MDE/ACCDE is a restricted copy of the file where there is no access to the design side or any of the code. The MDE/ACCDE copy is what you will distribute to your users. If you are one of those users, in addition to being the designer, then I recommend you make a MDE/ACCDE copy for yourself as well for regular access to the database. The master file should be used only for design changes.

When design changes are needed you make a copy of the master file, make any design changes necessary, test your changes, and once you are satisfied that it is ready for distribution you make another MDE/ACCDE of the new file and distribute that to your users. At this point you could either rename and keep the original master file - if you think you may need to revert back to it at some point - or delete it.

As far as distribution goes, that depends somewhat on your particular circumstances. If your user base is computer savvy enough you can just email them the new copy, or place it a network location they can access, and tell them to copy it down to their documents folder and replace their old copy. On the other hand, if you're like me and some of the people you work with have no clue when it comes to understanding a basic file system or network structure, then you have a couple of choices. If you have just a few users in a small area then it's not that difficult to just go to their machine and copy down the new file. If your user base is a bit larger or more spread out, then there are some automatic front end updating utilities out there, one of which you can find here.

 

[TomJ58]

Thanks for the info. This is my first foray into designing a multi-user db and only my second Access db project. I have learned alot about Access from this forum.