Compact database using vba

[chonming]

I would like to know whether below situation can be done. If yes, how?

I have a function to run some updates to the database. When the update
process reaches certain stage, the database size had pumped up to 2GB. Update
process will stop and I will need to compact the database before continue the
process. I hope there is a solution where I can write some code in vba in
order to, at certain point, automatically compact my database and after that
continue with the rest of the update process.

 

[Douglas J. Steele]

While it is possible to compact a database through VBA, have you considered
using a temporary database while you're running the updates? Tony Toews has
an example at http://www.granite.ab.ca/access/temptables.htm

Failing that, presumably your application is split into a front-end
(containing the queries, forms, reports, macros and modules), linked to the
back-end (containing the tables and relations). As long as the back-end
isn't actively being used (you can tell by whether or not the ldb file
exists), you can use the CompactDatabase method of the DBEngine object.