Trevor Hughes <tr**********@i net.net.nz> wrote in
news:10******** ******@kyle.sna p.net.nz:
I have a database (Access 2000, running on Win 2000), which
suffers from bloat over a period of time. In order to solve the
problem I set the option to compact on exit. This however has
caused a problem. The permissions of the mdb file which are set
to Everyone-Full control, are reset when the database is
compacted. The end result is the the users get a message saying
Access cannot locate the database. I can run it with Admin
rights, but normal users cannot.
It seems that my only option is to not "compact on exit".
Has anyone come accross a solution to this problem.
Users should never be compacting the database. Period.
That's an administrative function because:
1. it could fail, and somebody needs to recover from it.
2. it's not something that should need to be done very often in a
properly designed database.
First off, if you have the data tables and the forms and reports all
in the same MDB file, you've got the wrong architecture for
multi-user setups. That setup can lead to bad bloating, corruption
and weird behavior for end users. The only proper multi-user setup
is to have the tables in a back end MDB on a shared server, and each
user opening a local copy of the forms/reports, etc. (i.e., the
front end) on each workstation.
Second, with a split architecture, you don't really need to compact
the front end. If it's bloating enough to be a problem (either size
or performance), then there's something dreadfully wrong with the
design of your front end.
If you're experiencing lots of bloat, it's probably due to the fact
that you seem to have all your users opening the same MDB. Get rid
of that error and you'll probably no longer have bloat.
Compact on close is a waste of time, and, I would say, dangerous. It
cannot be bypassed and sometimes you want to close Access without
compacting, say if suspect something is corrupted in it. If you
compact, you may lose data that would be otherwise recoverable.
Now, none of this is to say that back end data files don't need to
be compacted occasionally. But it's not something that should need
to be done often, only as a maintenance task. When you do it depends
on how the MDB is used. If it's got lots and lots of additions and
has non-randomly distributed primary key values in most of its
tables (such as AutoNumbers) and there are large numbers of records
(100s of thousands), it might be helpful to compact the back end
once a day (to rewrite the index pages and the data pages, which
will be rewritten in primary key order). This can be an automated
process that is done at night when everyone is out of the office.
If you don't have that kind of heavy appends or tables that large,
you can easily get by with a weekly compact.
Of course, any compact cycle of your precious data file should
include the making of a backup copy *before* the compact.
That's precisely why compact on close is bad, because you don't end
up with a backup.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
