Compact On Close Question

[Wayne]

Does "Compact On Close" do a "Compact and Repair" or just a compact.
Is a compact necessary (or at least a good idea) on a regular basis,
say weekly, for a database that has several hundred records per week
added to it?

TIA.

[Trevor Best]

Wayne wrote:

Does "Compact On Close" do a "Compact and Repair" or just a compact.
Is a compact necessary (or at least a good idea) on a regular basis,
say weekly, for a database that has several hundred records per week
added to it?

Compact and repair is in the same operation now unlike it was many years
ago so I would assume that it does a repair as well but don't worry, it
doesn't "repair" it like Access 2.0 used to do <g>.

Compacting has two effects that I can think off the top of my head:
1. It gets rid of deleted space in the database thereby (hopefully)
shrinking it.
2. It deletes the execution plan of saved queries so they'll get
recompiled the next time they're run.

Both are a good thing, the overhead of compiling an execution plan for a
query is small and you won't notice it without some sort of benchmark
and some very small fractions of seconds. As data changes, the original
execution plan of a query may not be the best one but Access will use it
if its been saved there.

I've not had to compact a database for a long time as I've only used SQL
Server back ends in recent years and the front ends are in StarTeam and
in constant development regularly requiring deletion and getting a whole
new version out of source control so I can't have any frame of reference
for regular compacting but think of it like washing the car, if it's
dirty, clean it. If it's been parked in the garage all week then washing
it won't harm it but it's a bit pointless at that time.

Oh, nearly forgot, there is a regular compact I do, the one in XP where
MS are sorry for the inconvenience and it compacts it for me.

--
1f u c4n r34d th1s u r34lly
n33d t0 g37 l41d

[Wayne]

Thanks for the helpful info Trevor.

[David W. Fenton]

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** *************** @news.zen.co.uk :

Compact and repair is in the same operation now unlike it was many
years ago so I would assume that it does a repair as well but
don't worry, it doesn't "repair" it like Access 2.0 used to do
<g>.

If I'm remembering correctly, MichKa told us that with Jet 3.5 SP2,
every compact checked to see if a repair was necessary and then ran
it if it was necessary. It did *not* run it if there was nothing to
repair, as under certain circumstances, a repair could actually
*damage* a perfectly valid database.

"Repair" is obsolete. Don't use it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Trevor Best <no****@besty.o rg.uk> wrote in
news:42******** *************** @news.zen.co.uk :

Compacting has two effects that I can think off the top of my
head: 1. It gets rid of deleted space in the database thereby
(hopefully) shrinking it.
2. It deletes the execution plan of saved queries so they'll get
recompiled the next time they're run.

It also rewrites tables with a PK in PK order.

And it also cleans up the data pages of indexes, putting those in
order.

Both of these things can improve performance, especially with large
tables.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc