Generally, is there a rule of thumb, where after a certain size,
it is advisable to compact a database?
It's more a percentage issue. Extremely small front ends like yours
can grow a lot during normal production use, though more than a 4x
or 5x increase would bother me. Small front ends (c. 5MBs) can
double in size without major problems. Medium-sized front ends
(10-20MBs) oughtn't grow by more than 50% under normal usage. Large
front ends should grow only by very small percentages.
In other words, the smaller the front end, the greater a percentage
of total size you can get from bloat.
For back ends, you shouldn't even worry about the size -- you should
just be backing up and compacting on a regular schedule. Whether
that's every night or once a week or twice a week depends on how
much data is being entered into your database. The key point is that
it should be done regularly, on a schedule, without human
intervention required to make it happen.
For example, my Access database is (when compacted) 450KB, it has
grown to 15MB, in some cases. Most of my problems have gone away,
when I have compacted it, though others remain, due to my coding
of the macros - gradually being sorted out.
It entirely depends on *why* it's growing. If it's bloating while
you're programming, this is no big deal -- you should compact as
frequently as you like, as well as occasionally decompiling.
If it's bloating in production use, then you've probably got design
errors somewhere along the line that are causing churn in the front
end. Sharing a front end is a very good way to make it bloat --
don't do it. Instead, give all users their own private copies of the
front end. Storing temp tables in a front end is another -- don't do
it. Store temp tables in a separate temp MDB in the same folder as
your front end with tablelinks to the temp MDB. Distributing it in
an uncompiled state is yet another way to make it bloat, especially
if it has problems that prevent it from compiling fully. You should
always fully compile your front end before distributing it to users,
and in the best scenario, you should distribute an MDE and not an
MDB. MDEs can't decompile under any circumstances and they also have
better recovery from unhandled errors (in an MDB, all variables in
memory are reset after a code break; in an MDE, they are retained).
So, the answer is:
It depends!
