Guido said:
I have written a programme, that inserts new data into a database table. But
the size of the mdb increases. I can change the size by calling "Compress
and Repair" from the menu. How can I do this automatically in my programme
(source code)? Is there a query that can do it?
No, you can not Compact the running database. When you use
the menu item to do this, Access closes the database,
compacts it and then reopens it. You certainly can not do
that and have the code resume running.
OTOH, you should have split your application so the data is
in a "back end" mdb file. In this scenario, it is possible
,under exactly the right conditions, for the front end code
to compact the back end. I strongly recommend against this
idea though. Since a compact rewrites every byte in the
file, one possible outcome a potential disaster so making a
backup copy of the file prior to compacting should be an
integral step in the process.
How much bloat are you seeing from the insert operation? If
it's only a few megabytes per day, you can put off the
compact until the weekly maintenance procedure is performed.
If it's 100s of megabytes for every insert operation, then
you need to examine the update procedure carefully to see if
there is a better way to minimize the bloat.
