Access won't create "temporary" tables on its own. The developer is
responsible for these, if they exist. Generally, when you have this type of
bloat and shrinkage all occurring within a single day, it is likely that it
is because your users are inserting and deleteing lots (I mean LOTS) of
records into a "temporary" table, and then deleting those records. This is
common in many databases where the developer felt they needed to move data
to a "temporary" table before generating a report, or before massaging that
data to present it in a report.
Unfortunately, there is no "easy" way to detect which tables are being used
for this purpose, other than reviewing the code.
The first thing I would do is look through all of your saved queries
(specifically look for Maketable, Delete, and Append queries). These will
give you an indication as to which table are being written to and deleted
from (causing the bloat and shrink phenomonon you are encountering).
Next, you might want to search the VB code window for the term "INSERT" or
"DELETE" in relation to SQL strings that are being written dynamically, and
which are writing/deleteing large numbers of records from one or more
tables.
Once you identify the tables that are causing your bloat, then you may be
able to remove them from the backend, and put them in a temporary database
that is local to each user (see my reference to Tony's website below).
HTH
Dale
