Compact and Repair on Close

[Linda \(RQ\)]

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

 

[John W. Vinson]

is there a reason why it wouldn't be a good idea to
have this checked?

Yes. It's slow; it's never necessary for a frontend and will not run in a
backend (unless the backend is opened directly); it can corrupt the database;
it will interfere with other users attempting to open the database while the
compact is proceeding.

Just compact programmatically at reasonable intervals, when you're sure the
database isn't being or going to be used.

John W. Vinson [MVP]

 

[Ed Metcalfe]

<snip>
it's never necessary for a frontend
<snip>

John,

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.

Whilst I agree with all the other reasons you gave for not using compact on
close (and I never do) it *is* necessary to compact frontend MDBs from time
to time.

Ed Metcalfe.

 

[Douglas J. Steele]

<snip>
it's never necessary for a frontend
<snip>

John,

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.

Whilst I agree with all the other reasons you gave for not using compact
on close (and I never do) it *is* necessary to compact frontend MDBs from
time to time.

That's true, but Compact On Close is seldom (if ever) a good way to
accomplish that.

 

[Linda RQ]

Thanks, Guys....I will leave that box unchecked and do it myself from time
to time...Job Security!

Linda

 

[John W. Vinson]

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.

John W. Vinson [MVP]

 

[Ed Metcalfe]

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

John W. Vinson [MVP]

John,

Why?

Ed Metcalfe.

 

[Douglas J. Steele]

Why?

It's far simpler (and safer) simply to replace the front-end, rather than
compact it.

 

[Linda RQ]

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

John W. Vinson [MVP]

How does it get bloated?

Thanks,
Linda

 

[CES]

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

All,
I to noticed the option to compact and repair on exit with Access 2007 (I'm running Vista)... however be forewarned it just deleted four days worth the work that I did not have a backup of.

Not exactly sure why it did it and I did get a warning message, which unfortunately I didn't pay any attention to. I certainly agree with those that say manually do a compact and repair at least then you'll be more aware of any message you might see, whereas if you do it on exit will just assume that something code failed to close properly.

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. Thanks in advance. - CES

 

[John Spencer]

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
'====================================================

 

[David W. Fenton]

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]

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]

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]

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.

 

[John Spencer]

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
..