By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,363 Members | 3,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,363 IT Pros & Developers. It's quick & easy.

Permissions problem caused by compact

P: n/a
Hello All

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.

Any help would be appreciated.

TIA
Trevor

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Trevor,

I know, these inflating Access databases are a real pain in the ass.
There is no silver bullet solution, but there a few things to think about,

1. Don't store pictures, movies etc. in your databases. This is really no.1
causing bloated databases
2. Split your databases, that is create a data backend and a application
frontend. Install the frontend application on the client machines.
3. Don't change/add/delete database objects like forms and reports too much.

These measures will most likely reduce the need to compact (recreate!)
databases and you might consider to schedule compacting Access databases
using the /compact commandline option.

For instance:
C:\Program Files\Microsoft Office\Office9\MSACCESS.EXE C:\Temp\Test.mdb
/compact

This example will compact the C:\Temp\Test database without even opening it.

Hope this might help you,

Mark Zuijdhoek

"Trevor Hughes" <tr**********@inet.net.nz> schreef in bericht
news:10**************@kyle.snap.net.nz...
Hello All

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.

Any help would be appreciated.

TIA
Trevor

Nov 13 '05 #2

P: n/a
Trevor Hughes <tr**********@inet.net.nz> wrote:
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.


You need to give the users full rights to the directory. Or create a shortcut which
has an admin user on it which will do the compact.

The problem here is that Access compacts to a new file name and, if all goes well,
then deletes the old file and renames the new file to the old file name. Deleting
the old file then loses the permission settings on the old file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #3

P: n/a
Trevor Hughes <tr**********@inet.net.nz> wrote in
news:10**************@kyle.snap.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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.