I have been trying to get to the bottom of some bloating that I have in
a frontend database. I started deleting objects a few at a time and
have now deleted all objects and custom menu bars. The database is just
a shell but the size is still 5 Mb after a compact and repair. If I
create an mde, the file size is about half a meg - roughly the size I
would expect the empty mdb to be. I can't understand why the empty mdb
won't compact down to a much smaller size.

Any help is appreciated.

"Wayne" <cqdigital (AT) volcanomail (DOT) com> wrote

This is a "feature" of recent versions of Access. The solution is to
periodically create a new database and import all the objects from the fat
one.

Thanks for the reply. I did as you suggested and found that the
following happened. The new mdb was slower to open forms that have
lots of controls and also slower to run some complex queries. Yes, I
did compile it. Seems strange that this would happen. I thought that
everything would be nice and clean after importing into the new
database. However if I created an mde from the new database it runs
fine.

Upon further experimentation I have found the following. If I compact
the database (which is Access 2002 format) using Access 2002, the
smallest it will compact to is 11.7 MB which is way too big for this
database. If I compact it using Access 2003 the database compacts down
to 7 MB which, going on past experience, is about right for the amount
of objects it contains.

Do the 2 versions of Access use a different method to compact? Why
won't Access 2002 do the job properly?

Any help is appreciated.

"Wayne" <cqdigital (AT) volcanomail (DOT) com> wrote

I have no idea, but it's certainly my experience that in both Access 2002
and Access 2003 "front end" databases (i.e. those containing forms and
reports, not tables) develop "uncompactable" bloat over time i.e. compacting
becomes less effective over time, hence the need to create a new database
periodically. This didn't occur with Access 97 (I don't know about 2000, I
skipped that one) and it doesn't occur with back-end databases i.e. those
containing only tables.

Regarding your database getting slower after you recreated it, it isn't just
code that needs compiling for optimum performance, it's also queries. For
maximum performance, each query should be run once before you deploy,
although I doubt most of us would notice the difference.

I'm not sure on what basis you are concluding what the correct size is for a
database, and I would also suggest that the difference between 7Mb and
11.7Mb is not worth worrying about (unless you are somehow deploying the
thing over the internet).

Thanks again for the reply Baz. It just seemed strange that Access
2003 would compact it so much "tighter" than Access 2002.

I am basing the size on other very similar databases that I have built
with almost exactly the same objects and code. This database is a
rehash of one of them to fit slightly different requirements. A few of
the forms and reports have been changed a bit, but that's about it.