It turns out at one time you could have more then one person doing design
work on the same file at the same time
(access 97 was the last version that allowed this).
From a2000 and on-ward, you cant make design changes when other people are
using the database.
It turns out that as a general rule, we don't actually miss this ability to
have "multiple" people making design changes, and there is a solution to
this problem (see later in this post).
Also, one should not build a table while people are trying to using the
table to have dinner on (a leg might fall off, and everyone looses their
dinner). Or, one should not work on a car while it is being used.
And, working on the code and design of a production database is a formula
for disaster. You need to test and try your changes before letting them out
into the wild (into the hands of your users).
So, as a developer using ms-access, I do in fact need a way to develop, fix
bugs..and work on the "next" great version while the current users happy
work away using the existing application.
When you use a application, usually the application part is separate from
the data part (you run word, and open documents. Or, you run Excel, and open
documents). What about an applications that you make? Again, you need to
split the application part, and make it separate from the data part. That
means you can work on, and make changes to the application part all the
while users are working with the data. To achieve this setup in ms-access we
use what is called a split database. (there is even a database splitter
wizard for you to do this under tools->database utilities->database
splitter).
What the splitter does is takes your existing application and splits it into
two parts.
The application part (generally refereed to as the FRONT END (FE), and
The data only part (generally referred to as the BACK END (BE).
The above thus lets you distribute and give each user a front end, and they
all thus can share the back end.
In the mean while, you can now work on a separate copy of the FE. When all
you new changes etc are done, tested, and you are sure it is safe to
use..you then re-distribute this new front end to each users work station.
So, you can safely work on the next great version of the software..and users
are not effected. (how do you work on the system now when users are using
it?)
And, if the application is not multiple user...you still gain the benefit of
being able to simply copy a new front end to the user without overwriting
the data part..since now that is a separate part.
The above certainly does not let two users made design changes...but it
certainly lets you make design changes to a copy of the application part
which you can then later copy/send to each user without loosing changes to
the data part.
What is even more fab is that you can also work on a copy of the back end
BE, and test routines that update data, delete data etc. If the test don't
go well, you simply just grab another copy of the BE..and test some more.
Right before y ou put the new FE into production, you have to re-link this
FE to the production BE.
One more note:
If you are running a muilt-user situation, then you REALLY need to run a
split database, as ms-access does not reliably work with multiple users if
you do not split (and, since you are splitting..then each users should get a
mde for the FE).
You can read up on splitting here:
http://www.granite.ab.ca/access/splitapp.htm