Why does an mde increase in size?

[mscertified]

I compiled an mdb to an mde but it is still increasing in size as the users
use it. It does have some local tables but they are static and never get
updated. What would cause this? I thought only the back end should need
compressing.

 

[Larry Linson]

Temporary storage is created and used by Access' own functions, such as
executing Queries, and, it, just as vacated record space, is not recovered
until a compact and repair is executed. One virtue of using a split
database with no updated data in the front end, is that instead of compact
and repair, you can just copy a fresh copy of the front end over the old
one.

Larry Linson
Microsoft Office Access MVP

 

[Brian]

Larry,

Just a follow-up comment/question: where the front end is distributed to
each workstation rather than being stored on a network drive, is there a down
side to automating the compact/repair on close of the MDE to keep it clean &
lean?

 

[Douglas J. Steele]

It's safer (and usually faster) simply to replace the MDE file.

 

[Rick]

Larry,

Just a follow-up comment/question: where the front end is distributed
to each workstation rather than being stored on a network drive, is
there a down side to automating the compact/repair on close of the MDE
to keep it clean & lean?

The Compact on close setting has been known to cause corruption in some
databases.

 

[Rick]

Larry,

Just a follow-up comment/question: where the front end is distributed
to each workstation rather than being stored on a network drive, is
there a down side to automating the compact/repair on close of the MDE
to keep it clean & lean?

The Compact on close setting has been known to cause corruption in some
databases.

 

[David W. Fenton]

Just a follow-up comment/question: where the front end is
distributed to each workstation rather than being stored on a
network drive, is there a down side to automating the
compact/repair on close of the MDE to keep it clean & lean?

COMPACT ON CLOSE is a feature of Access that should never under any
circumstances be used. Half the time it won't do anything useful
(compacting a front end, which doesn't need compaction) and in some
small number of cases, it runs the risk of losing data (because
sometimes databases can be corrupted and the data remain usable as
long as it's not compacted).

 

[David W. Fenton]

I compiled an mdb to an mde but it is still increasing in size as
the users use it.

Do you have any temporary tables in it? If so, they should be
removed to a temp MDB instead.

Also, do you have multiple users opening a single MDE? If so, you
need to give each user an individual copy of the MDE.

 

[Larry Linson]

Just a follow-up comment/question: where the
front end is distributed to each workstation rather
than being stored on a network drive, is there a down
side to automating the compact/repair on close of the
MDE to keep it clean & lean?

The primary reason for not doing Compact on Close is the possiblity of it
resulting in corruption; a secondary reason is that it will almost certainly
take longer than copying a fresh copy of the FE. I don't recommend it, nor
do I recommend doing a Compact-and-Repair unless it is needed.

But, just for the record, I have not personally experienced corruption that
I could determine resulted from a Compact. Enough people have, people whose
ability and truthfulness I trust, that I feel comfortable with my
recommendations above.

Larry Linson
Microsoft Office Access MVP