Compact and Repair

[G Gerard]

Hello

The more I use an application ( an mdb) created using MSAccess I notice
that the Byte size of the application keeps on increasing. Once in a while I
do a Compact and Repair Database (under Tools in MSAccess) on the
application and the size then returns to normal.

What is causing the mdb to keep on increasing in size?

Is it a good idea to do a Compact and Repair Database every time the
application is closed so to keep the size of the mdb to a minimum?

If Yes what is the best way to do a Compact and Repair Database using code?

Thanks

G Gerard

[pietlinden]

Sounds like you're creating temporary objects in your database.
That'll make it grow. You can set it to Compact and Repair on close,
which should keep it small. If you're creating temporary objects , you
might want to look at Tony Toews' site

http://www.granite.ab.ca/access/bloatfe.htm

[Larry Linson]

"G Gerard" wrote

What is causing the mdb to keep on increasing in size?

Access does not return to the system the disk storage of objects that have
been deleted or replaced until you do Compact and Repair.

Is it a good idea to do a Compact and Repair Database
every time the application is closed so to keep the size
of the mdb to a minimum?

It's not necessary, generally, to compact every time you open your DB. It is
a good idea to split the DB into application (front-end: queries, forms,
reports, macros, and modules) and data (back-end: tables, relationships, and
data).

When you do, and can tell us which is growing, then someone may be able to
suggest ways in which you might be able to determine the cause and possibly
avoid some of the bloating. And, which you will need to Compact and Repair
most frequently.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"G Gerard" <gg*****@nbnet.nb.cawrote in
news:tt*******************@ursa-nb00s0.nbnet.nb.ca:

Is it a good idea to do a Compact and Repair Database every time
the application is closed so to keep the size of the mdb to a
minimum?

Access 2000 and later provides the "Compact on close" option.

I would recommend that it NEVER BE USED.

First off, if your app is properly structured, you're opening only
the front end. Front ends may bloat a bit, but since they have no
data in them, the bloat is finite, and so there's no compelling
reason to ever compact a front end (except to update the table stats
that are cached in the table links; that is, if you add 100K records
to a 10-record table in the back end, it would be a good idea to
compact the front end to update its metadata about that back-end
table; this metadata is used in optimizing queries and for other
purposes, such as helping the Rushmore data retrieval technology
decide how to retrieve data).

And COMPACT ON CLOSE is downright *dangerous* when opening a back
end because sometimes a compact to a damaged MDB will lose data that
is still accessible before the compact.

So, I would say:

Always keep COMPACT ON CLOSE turned off.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/