Compacting Database

[dhstein]

I just compacted my database and the front end shrunk significantly but there
was no change to the tables. So what exactly is taking up all that room in
the front end? The VBA code should stay the same. What gets larger and then
smaller on the front end ? Thanks for any explanation of this.

 

[Douglas J. Steele]

Queries can require temporary storage when run.

 

[Jeff Boyce]

Does that mean your tables are in the back-end? If you open the front-end
and tell Access to compact, it compacts what it's in.

You'd need to open the back-end and run Compact... STOP! before compacting
either one, make yourself a backup of each, just in case...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I just compacted my database and the front end shrunk significantly but there
was no change to the tables. So what exactly is taking up all that room in
the front end? The VBA code should stay the same. What gets larger and then
smaller on the front end ? Thanks for any explanation of this.

Any time you modify the design of a form or report it makes a new copy of the
object; some queries create invisible temp tables; some VBA operations
allocate temp space. The specific details are proprietary and probably nobody
even at Microsoft could keep track of them all.

 

[David W. Fenton]

Queries can require temporary storage when run.

But they don't use the front end for that. Jet stages its temporary
data in temp files in the temp folder.

Queries *do* take up more space once they've been compiled, but a
mere compact does not discard the existing compilation. It only
marks the queries so that they are recompiled the next time they are
run. In any event, I would expect the execution plans to take up
relatively little space (I can't image more than 1 or 2K for each
query).

 

[David W. Fenton]

some queries create invisible temp tables;

This is very similar to Doug Steele's comment, and I don't believe
I've ever heard anything about this before. My observation of Jet is
that temporary *Jet* objects that are needed in the execution of SQL
are created in temp files in the temp directory. This would include
things like an append query, in which the implicit transaction uses
files in the temp folder to collect everything together before then
committing the result to actual data file. That would have zilch to
do with the front end, as the data isn't *in* the front end.