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

Compact/Compact on Close

P: n/a
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.

Has anyone else experienced this and is there a fix? Thanks for any help,

Jim F.
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is held
in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every time
you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however slight,
of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by the
administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.

Has anyone else experienced this and is there a fix? Thanks for any help,

Jim F.

Nov 12 '05 #2

P: n/a
Hi,

Thanks for the input Chris. All my databases are FE/BE, however the FE
bloats everytime recordsets are created etc. Some of the files are only
opened once a day, and users are not Access experts, so seems logical to
Compact on Close. (If I put a shortcut, it will never get used...)

In addition, it not only happens then, but also sometimes when compacting
manually, which concerns me. I agree that maybe Compact on Close isnt the
best solution, but if it is in there, it should at least work!

Jim F.

"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every time you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however slight, of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by the administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database while it is open regularly fails, seemingly at random. This is especially annoying as I usually set the Compact on Close setting in Tools/Options to true.

Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.


Nov 12 '05 #3

P: n/a
TC
Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.

When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.

So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.

HTH,
TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every time you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however slight, of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by the administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database while it is open regularly fails, seemingly at random. This is especially annoying as I usually set the Compact on Close setting in Tools/Options to true.

Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.


Nov 12 '05 #4

P: n/a
TC
A database is never compacted when it is open. Even compact on close would
close the database before compacting it.

HTH,
TC
"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.

Has anyone else experienced this and is there a fix? Thanks for any help,

Jim F.

Nov 12 '05 #5

P: n/a
Did you actually test this TC? I did.

Your post sounded to me like mumbo-jumbo mixed with fact, so what should I do?
I tested.

1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888

These figures do of course support your assertion, theoretically. But...SOME
INCREASE.

As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures suggesting
once every 6 months, so let's compromise on once-a-month. James quite rightly
says "but the customer will never do it" (quite rightly that's my experience
too). However, the BE is where most of the changes occur and therefore is in
much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters on
every communication with the customer, no-no don't go that far :-) )

I await you posting your test results to justify your statements, as affects
practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907.286595@teuthos...
Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.

When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.

So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.

HTH,
TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is

held
in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every

time
you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however

slight,
of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by

the
administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
Hi,

I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database while it is open regularly fails, seemingly at random. This is especially annoying as I usually set the Compact on Close setting in Tools/Options to true.

Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.



Nov 12 '05 #6

P: n/a

"James Franklin" <ja************@ntlworld.com> wrote in message

In addition, it not only happens then, but also sometimes when compacting
manually, which concerns me.


Hell, that concerns me too. In some 500 or 600 sites, I get (at a guess) about
95% recovery from asking them to run repair/compact. On the BE (who cares
about the FE, they can reload that from CD)

The other 5%? My figures are guesswork of course. Perhaps once or twice a year
in that many sites, I need to take them aside and stress the importance of
backups and ARCHIVES. After trying all the usual repair facilities including
PK Solutions.

The absolute LAST thing I want them to do is repair/compact unnecessarily. And
certainly they must do it when everyone is out of it (this last should not
apply to an FE of course)

If your FE creates say temp tables (your post suggested it might, and I
sometimes do), Tony Toews has a suggestion to create a temp database for that
reason - temp tables. Personally, I know of the idea and reason for it, just I
never found it a big enough deal to be necessary. What exactly is your
problem? (bloat sizes, after doing what?)

Chris
Nov 12 '05 #7

P: n/a
To suppport TC,
I did some testing along these lines back in MSA97, and found
extensive bloat in the FE. Mind you that mdb was running trough
millions of records each day running reports

Once I discovered CompactOnClose I breathed a huge sigh of relief and
forgot about the issue. Maybe they fixed it in later releases.

Even if the bloat is minor, sooner or later, the users will hit the
limit either through performance degradation or hitting MaxFileSize.

My solution in Pre 200 days was to send out regular "Urgent" updates
to the FE these were usually nothing but a compacted FE, or at most a
few minor changes. these were always installed. To my knowledege, the
Compact Database option I set up in the start menu never was

(Who said I'm no good at social engineering :)>

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

On Wed, 3 Dec 2003 21:36:25 +1300, "Chris Mills"
<ph*********@cleardotnet.nz> wrote:
Did you actually test this TC? I did.

Your post sounded to me like mumbo-jumbo mixed with fact, so what should I do?
I tested.

1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888

These figures do of course support your assertion, theoretically. But...SOME
INCREASE.

As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures suggesting
once every 6 months, so let's compromise on once-a-month. James quite rightly
says "but the customer will never do it" (quite rightly that's my experience
too). However, the BE is where most of the changes occur and therefore is in
much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters on
every communication with the customer, no-no don't go that far :-) )

I await you posting your test results to justify your statements, as affects
practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907.286595@teuthos...
Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.

When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.

So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.

HTH,
TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
> Whyever would you set "Compact-on-close"? This is about the most useless
> option MS ever invented.
>
> "Compact-on-close" what? It can only compact the FE.
>
> All sensible software uses an FE/BE arrangement, where the main data is

held
> in the BE database. Therefore, it's the BE which needs compacting.
> (Front-End/Back-End)
>
> Why would you compact-on-close, presumably multiple times a day, every

time
> you close the database? Compacting needs doing typically once a month or
> whatever. Every time you compact, there is additional danger, however

slight,
> of corrupting the database.
>
> Do it properly and create a separate shortcut to compact the database, by

the
> administrator when everyone is out of it, and only as necessary.
>
> Chris
>
> "James Franklin" <ja************@ntlworld.com> wrote in message
> news:hb******************@newsfep1-win.server.ntli.net...
> > Hi,
> >
> > I have a number of databases in A2K, written on different machines with
> > different installations of Office. I have found that compacting a

database
> > while it is open regularly fails, seemingly at random. This is

especially
> > annoying as I usually set the Compact on Close setting in Tools/Options

to
> > true.
> >
> > Has anyone else experienced this and is there a fix? Thanks for any

help,
> >
> > Jim F.
> >
> >
>
>



Nov 12 '05 #8

P: n/a
TC

"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:O8**************@tk2msftngp13.phx.gbl...
Did you actually test this TC? I did.

Your post sounded to me like mumbo-jumbo mixed with fact, so what should I do? I tested.

1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888

These figures do of course support your assertion, theoretically. But...SOME INCREASE.

As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures suggesting once every 6 months, so let's compromise on once-a-month. James quite rightly says "but the customer will never do it" (quite rightly that's my experience too). However, the BE is where most of the changes occur and therefore is in much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters on every communication with the customer, no-no don't go that far :-) )

I await you posting your test results to justify your statements, as affects practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907.286595@teuthos...
Compaction of the FE >does< make sense. Normally, the queries will be in the FE. When each query is compiled, Jet uses performance statistics from the BE tables do determine (& save) an optimal "execution plan" for the FE query. This plan is stored in the FE, not the BE.

When the BE is compacted, the performance statistics are recomputed. (That is one of the benefits of doing a compact). However, in a FE/BE situation, the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.

So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.

HTH,
TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
Whyever would you set "Compact-on-close"? This is about the most useless option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is
held
in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every

time
you close the database? Compacting needs doing typically once a month
or whatever. Every time you compact, there is additional danger, however

slight,
of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by the
administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
> Hi,
>
> I have a number of databases in A2K, written on different machines
with > different installations of Office. I have found that compacting a

database
> while it is open regularly fails, seemingly at random. This is

especially
> annoying as I usually set the Compact on Close setting in

Tools/Options to
> true.
>
> Has anyone else experienced this and is there a fix? Thanks for any

help,
>
> Jim F.
>
>



Nov 12 '05 #9

P: n/a
TC

"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:O8**************@tk2msftngp13.phx.gbl...

(snip)
Your post sounded to me like mumbo-jumbo mixed with fact,
My post was 100% fact.

I await you posting your test results to justify your statements, as affects practical purposes, TC.
Sorry, ain't gonna happen. You can believe it, or not, as you choose.

TC



"TC" <a@b.c.d> wrote in message news:1070333907.286595@teuthos...
Compaction of the FE >does< make sense. Normally, the queries will be in the FE. When each query is compiled, Jet uses performance statistics from the BE tables do determine (& save) an optimal "execution plan" for the FE query. This plan is stored in the FE, not the BE.

When the BE is compacted, the performance statistics are recomputed. (That is one of the benefits of doing a compact). However, in a FE/BE situation, the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.

So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.

HTH,
TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
Whyever would you set "Compact-on-close"? This is about the most useless option MS ever invented.

"Compact-on-close" what? It can only compact the FE.

All sensible software uses an FE/BE arrangement, where the main data is
held
in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)

Why would you compact-on-close, presumably multiple times a day, every

time
you close the database? Compacting needs doing typically once a month
or whatever. Every time you compact, there is additional danger, however

slight,
of corrupting the database.

Do it properly and create a separate shortcut to compact the database, by the
administrator when everyone is out of it, and only as necessary.

Chris

"James Franklin" <ja************@ntlworld.com> wrote in message
news:hb******************@newsfep1-win.server.ntli.net...
> Hi,
>
> I have a number of databases in A2K, written on different machines
with > different installations of Office. I have found that compacting a

database
> while it is open regularly fails, seemingly at random. This is

especially
> annoying as I usually set the Compact on Close setting in

Tools/Options to
> true.
>
> Has anyone else experienced this and is there a fix? Thanks for any

help,
>
> Jim F.
>
>



Nov 12 '05 #10

P: n/a
<sigh>
Nov 12 '05 #11

P: n/a
TC
What can I say, Chris? I provide some facts, & you reply that my post is
"mumbo jumbo".

Hardly likely that I would want to continue the conversation, no?

TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:#t**************@TK2MSFTNGP12.phx.gbl...
<sigh>

Nov 12 '05 #12

P: n/a
I see you're on the back foot, my having done some actual tests and you
apparently not prepared to, but never mind I regard your observations (facts)
as very valuable so I'll back off a bit.

Here's what I get on "compact-on-close" (remember that's what the subject is)

CLOSE The Program:
-w/o compact-on-close: <1sec
-with compact-on-close: >30secs (just to CLOSE, <39mB FE already compacted>)

I acknowledged your "facts" just not the mumbo-jumbo of it's applicability or
relevance-in-practice.

If your FE bloats in size because of say temp tables, see Tony Toews site or
ask him about doing temp tables in a separate temp BE database. 600+ sites are
using my s/w without compact-on-close, OTOH 599 of them are probably not
operational (no-no, I meant to say that firgure is misleading, I only have 2
production softwares <what IS the plural> and a 1/2 dozen individual ones).
They bloat, slowly or fastly. They bloat because of imaging and this-or-that
and every other thing. But the BE ALWAYS bloats much faster than the FE, and
it is the BE which more often and likely needs compacting. And you consider
compact-on-close of the FE to be important or desirable? Sheesh, get a life.
Post some actuals.

Chris

"TC" <a@b.c.d> wrote in message news:1070845286.49249@teuthos...
What can I say, Chris? I provide some facts, & you reply that my post is
"mumbo jumbo".

Nov 12 '05 #13

P: n/a
TC
Chris, I take offence at your tone to me throughout this thread. So this is
my last post on the subject.

I stepped in to the thread to give a reason why compacting the back end can
improve >performance<. You replied with some tests that focussed on the back
end >size<. I thought at the time, "wtf is that to do with what I posted?".
But I was too polite to say that to you.

You said that your tests contradicted the "mumbo jumbo" that I posted. You
challenged me to provide my own results. I was offended by the "mumbo
jumbo" - I knew that your tests were irrelevent to what I posted (but I was
still too polite to say that to you) - and I knew that what I said was
correct, so there was no reason for me to do any testing myself. So I
declined to do so.

Now, in your last reply, you think that I am "on the back foot". Fine. Watch
my back feet as I am ..... OUTTA HERE.

TC
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:#l**************@TK2MSFTNGP11.phx.gbl...
I see you're on the back foot, my having done some actual tests and you
apparently not prepared to, but never mind I regard your observations (facts) as very valuable so I'll back off a bit.

Here's what I get on "compact-on-close" (remember that's what the subject is)
CLOSE The Program:
-w/o compact-on-close: <1sec
-with compact-on-close: >30secs (just to CLOSE, <39mB FE already compacted>)
I acknowledged your "facts" just not the mumbo-jumbo of it's applicability or relevance-in-practice.

If your FE bloats in size because of say temp tables, see Tony Toews site or ask him about doing temp tables in a separate temp BE database. 600+ sites are using my s/w without compact-on-close, OTOH 599 of them are probably not
operational (no-no, I meant to say that firgure is misleading, I only have 2 production softwares <what IS the plural> and a 1/2 dozen individual ones). They bloat, slowly or fastly. They bloat because of imaging and this-or-that and every other thing. But the BE ALWAYS bloats much faster than the FE, and it is the BE which more often and likely needs compacting. And you consider compact-on-close of the FE to be important or desirable? Sheesh, get a life. Post some actuals.

Chris

"TC" <a@b.c.d> wrote in message news:1070845286.49249@teuthos...
What can I say, Chris? I provide some facts, & you reply that my post is
"mumbo jumbo".


Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.