It sounds like you do not have your database split. You should.
If there are multiple people sharing the same copy on a network, you will
need to expect corruption at some point in time. You are also suffering from
poor performance and you are loading needless traffic on the database. And,
of course, as you are seeing, maintenance becomes a real problem.
Here is what you should do:
1. Make a backup copy of your database.
2. Use the database splitter to split the database. (Tools, Database
Utilities, Database Splitter). When it asks were to put the data, navigate
to a shared folder all users have read/write/delete permissions.
This process will create two mdb files. One will have the original name and
the one you put on the server will have _be appended to it. For example,
your current application is named MyGreatApp.mdb. Now you will have
MyGreatApp.mdb and MyGreatApp_be.mdb. These two parts are commonly referred
to as the Front End (fe) and the Back End (be).
The fe will contain all your application objects (forms, reports, macros,
queries, code modules). The be will contain only tables and relationships.
As I stated previously, the be should be on your server and each user should
have their own copy of the fe on their computer. The fe gets its data from
the be using Linked tables. Each TableDef in the fe has a Connect property
the tells it where the be table is. That is, it has the full path to the be
file.
Now, this brings into focus an issue you may have. When you do the split,
it automatically creates the Connect property for each table using Drive Map
paths based on the Drive Mapping on your computer. For example you have:
\\ServerName\AccessApps mapped on your computer as G:\. If all users have
the same drive mapping, this is not an issue, but if each user does their own
mapping, they may not be using G:\, they may be using S:\. In this case,
when they open the fe, they will get an error because the fe can't find the
data on S:\. The solution to this is to use UNC mapping rather than Drive
Mapping. That is done like you identify your drive maps and network
connections. So open the linked table manager from the fe, check Always
Prompt for New Locatoin, select all your linked tables, and enter the path as
a UNC Path, for example: \\ServerName\AccessApps\. It will relink the tables
using the UNC path in each table's Connect property. Now it doesn't matter
what drive each user has mapped to where, the fe will be able to find the be.
Now when you need to distribute mods to the fe, all you need to do is change
the links from your development be to the production be and deliver a copy of
the fe to each user. There are several ways to do this. One is to notify
the users to copy the new version from a specific location onto their
computer. A bit better way is to write a .bat file that does the copy for
them and have them run the bat file. There is also a technique of automatic
updating.
An easy automatic update is to create a one record, one field table in the
back end and an identicaly table in the front end. All it needs is a version
identifier. In the Open event of your startup form, compare the values of
the two versions. If the version in the fe is < the version in the be, then
use the Shell command to run a bat file that first copies the new version
into place then starts the application. then do a Docmd.Quit. For example:
If DLookup("[Version]", "tlbClientVersion") < DLookup("[Version]",
"tblServerVersion") Then
MsgBox "Click Okay to Update Your Application", vbInformation,
"Required Update"
Shell("VersionUpdate.bat", vbHidden)
Docmd.Quit
End If
And, there is this auto fe updater that is a bit more sophisticated:
http://www.granite.ab.ca/access/autofe.htm
Good Luck.
