ms-access does not recover the space used when you delete a record.
On the other hand, a very large portion of pc-based database systems have
worked that way for the last 20 years. So, I have to assume you are VERY new
to using databases.
So, what this means is that if you have any code that uses temporary tables,
then you should try and come up with a design that DOES NOT need temporary
tables.
You also want to make sure you are running the service packs and also the
JET updates, as they do fix some bloating problems.
The other well document problem is recordset code that DOES NOT close the
recordset THAT YOUR code opened.
So, for all reocrdset code that opens a recordset, you want to be sure you
force the recordset to close when done.
Lets look at a typical processing loop in code:
For example:
dim rstMyRecords as dao.recordset
dim strMySql as string
strMySql = "select * from tblCustomers where City = 'Edmonton'"
set rstMyRecords = currentdb.OpenRecordSet(strMySql)
do while rstMyRecords.EOF = false
debug.Print rstRecords "Current customer = " & rstMyRecords!CompanyName
' code here to do the record processing
rstMyRecords.MoveNext
loop
rstMyRecords.Close
set rstMyRecords = nothing
Note how after I loop and process all records, I then close the
reocrdset,and then set it to nothing. All of your recordset code should do
the same. Failure to do so can cause JET to NOT clean up, and bloat can
result.
Further, the real solution is go come up with designs that don't use temp
tables, or needless delete of records.
In the cases where you can't come up with some queries, and avoid this
deleting then creating records, then the only real solution is to move out
those temp tables out of the application data into a temp scratch mdb file
(that you can delete after using). Further, using temp tables is really
rotten when you have a multi-user application anyway, and again it rapidity
becomes clear that temp tables should be avoid.
You can also help the situation by simply using a split mdb, and thus the
bloat is limited to EACH USERS pc. With a2000 and later, you can set to
compact on close. Thus, each work station gets the bloat managed by the
compact on close, and the back end data thus grows very slowly. You then of
course compact the back end as part of your weekly, or monthly database
maintains that any competent access user will schedule at timely intervals.
(like very month, or week, depending on your volumes). I have a small
application with about 60 tables, and most tables are small, only about
30,000 records. With 5 users working all day, general file size growth is
BARELY noticeable, and thus I have them compact about ONCE every month.
Sometimes they go for two months. The file used quite heavlity, but since NO
temp tables, or deletng/creating of reocrds occurs, then the data files
grows very slowly, and in fact only as fast as "NEW" actaul data is added.
For some good info and further solutions to dealing with temp tables, and
eliminate file bloat, check out:
http://www.granite.ab.ca/access/bloatfe.htm