Compace on close?

[Guest]

Hello,

I was wondering if it was a good idea to activate that. What are the
arguments pros and cons?
On the pro side I think shrinking the database side can only be good. A
negative effect might be that this will cause the database to shut more
slowly once it grows bigger.

I'd appreciate some guidance on this subject.

Thanks

 

[Granny Spitz via AccessMonster.com]

What are the
arguments pros and cons?

It's ok for a single user database because you can have exclusive access. It
will fail if multiple users are connected to it and one user exits. You'll
get a series of files, db1.mdb, db2.mdb, db3.mdb etc., one for each failure.


Most of the time it's going to be effective only in the back end of a
multiuser database, but you can't activate the compact on close function in
the backend from the front end. So the practicality of it is that you have
to wait for everyone else to exit from their front ends, then open and close
the back end via the Access interface.

 

[Guest]

Ah, ok, that's good to know.
But from a purely technical point of view - do you get any performance
improvement by compacting?
And in general, is compacting something that should be done on a somewhat
regular basis?

 

[Granny Spitz via AccessMonster.com]

But from a purely technical point of view - do you get any performance
improvement by compacting?

Absolutely. Records that may have been scattered throughout the file are
reordered by the primary key when compacted (making queries sorted on the
primary key faster), table statistics are updated and queries are compiled
(often making them faster). And accessing a smaller data file is faster than
accessing a larger data file.

And in general, is compacting something that should be done on a somewhat
regular basis?

Yes, if you update and delete records a lot you'll need to compact more often.
If it's mostly just appending new records it's less often.

 

[Guest]

I don't like compact on close for various reasons.

1. I've seen people basically pull the plug on their computers when it
wouldn't shut down quickly enough due to Access doing compacting. Of course
this was on badly designed databases that were not split.

2. On many of my my databases I have a new copy of the FE copied to the
user's PCs when they log in so no compacting needed.

3. Compacting is somewhat of a futile pursuit as the database usually just
grows back to the size needed to do sorts and other internal stuff. If the
database is severely bloating, the root cause needs to be fixed and not just
the symptoms.

4. I have seen databases corrupted by compacting. I even had one where it
was reproducible! Turned out that there was a problem with the Microsoft
Office installation. No need to tempt fate for such little gain in compacting.

5. A properly split database should not need compacting as the tables are
not in the mdb file being closed. You are compacting the FE.

I manually compact and repair my databases about once a month or so using
the JetComp utility which makes a backup.

 

[Guest]

Thanks a lot to both of you, your replies sure were informative.