When sharing an Access database, you should have the data in one file (the back end) and the queries, reports, forms and modules in a separate database (the front end).
Give each user their own copy of the front end, linked to the shared back end.
Keep a master copy of the front end file, tucked away in a safe place. If you need to update the front end, such as adding new features to a form, make the changes in the master copy of the front end file (make a backup copy first!)
To distribute the revised front end to all users, you can use a utility like Tony Toews’ Auto FE Updater. I use this utility for most of the databases that I’ve installed at client sites, and it works beautifully.
[Update: This utility no longer has a free version]
The utility adds a shortcut to the user’s desktop, which they double-click to open the database. If the master copy of the front end has been updated, the utility downloads a copy of the revised version.
Remember to Compile
The only problem that I’ve had was with one user, who has Access 2000 installed. I’m using Access 2003 to update the master file, and all the other users have Access 2002 or 2003 installed.
Sometimes, after I’d updated the master file, that user couldn’t open the new version. Error messages would appear, and the VBA code looked like pieces were missing. Very strange.
When I made the latest change to the database, I compiled the code before closing the file.
It dawned on me that maybe I’d occasionally forgotten to do this, and that could be causing the problem.
I checked the previous version, and it wasn’t compiled. When I uploaded the new compiled version, that user was able to open it with no problem.
That’s not definitive proof that compiling will prevent future problems, but I’m keeping my fingers crossed.
____________________
The utility is not so free any more.
Just thought you should know.
Thanks rhett, I’ve updated the article.