Compact and Repair on Close

ON THE OTHER HAND

I've been using Automatic compact for a couple of years on some of my
databases (Access 2000) and have had no (discovered) problems. The
reason for the compact and repair is that a couple of processes I use in
this particular database build and change stored queries. For some
reason, this causes some minor bloating in the front-end database that
is handled by the compact on close.

All the data is in an MS SQL backend except for a couple of temporary
tables in a temporary database.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Linda (RQ) wrote:
Hi,

I was looking around in the options and see a checkbox to compact and repair
the database on close. This is not checked by default. It seems like it
would be a good idea, is there a reason why it wouldn't be a good idea to
have this checked?

Thanks,
Linda

"Ed Metcalfe" wrote in
:

Since Access 2000 frontend Access databases that contain no local
tables (only linked tables) do seem to bloat over time and can
grow up to several hundred megabytes in size.

A properly-designed Access front end will bloat somewhat over its
compacted size, but only up to a certain point, and then no more.
It's only badly designed front ends that continue to bloat.

Using an MDE will take care of most of the bloat if you don't
include any temp tables in the front end.

My clients *never* compact their front ends.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Linda RQ" wrote in
:

"John W. Vinson" wrote in
message ...
On Sun, 25 Mar 2007 06:28:46 +0100, "Ed Metcalfe"
wrote:

Since Access 2000 frontend Access databases that contain no local
tables (only linked tables) do seem to bloat over time and can
grow up to several hundred megabytes in size.

If that happens, I'd suggest just replacing it with a fresh copy.

How does it get bloated?

Three things make a freshly compacted front end grow over time:

1. gradual compilation of all queries, recordsources and rowsources
(compacting discards all of these).

2. code that decompiles for some reason and then must be recompiled
at runtime (this is a design error -- properly written code will not
decompile, and you should deliver your front end in a fully compiled
state).

3. temp data written to and deleted from the front end. This is also
a design error -- temp data should be stored in a separate MDB that
is completely disposable and recreated in code or copied from an
empty template when needed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

CES wrote in
:

The real problem with compact and repair is that it doesn't save a
backup copy of the database it just overwrites the previous copy.

And that you aren't given the option of skipping it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

John Spencer wrote in
:

I've been using Automatic compact for a couple of years on some of
my databases (Access 2000) and have had no (discovered) problems.
The reason for the compact and repair is that a couple of
processes I use in this particular database build and change
stored queries. For some reason, this causes some minor bloating
in the front-end database that is handled by the compact on close.

I would recommend not editing stored QueryDefs in code. In my 11
years of regular Access development, I've encountered only one case
where I was forced to use a stored QueryDef and change and save it
at runtime.

I would call the decision to edit saved QueryDefs at runtime a
design error.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

You are entitled to your opinion.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"David W. Fenton" wrote in message
. 1...
John Spencer wrote in
:

I've been using Automatic compact for a couple of years on some of
my databases (Access 2000) and have had no (discovered) problems.
The reason for the compact and repair is that a couple of
processes I use in this particular database build and change
stored queries. For some reason, this causes some minor bloating
in the front-end database that is handled by the compact on close.

I would recommend not editing stored QueryDefs in code. In my 11
years of regular Access development, I've encountered only one case
where I was forced to use a stored QueryDef and change and save it
at runtime.

I would call the decision to edit saved QueryDefs at runtime a
design error.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/