How to compact a database using macro

Question

I have a big MS Access after run my queries. So what I need is to delete all the tables wich are created, compact the database to shrink the file and then run the queries. What I need is to create and run a macro to erase some tables and then, compact. As far I open this file a lot, I cannot the option to compact the file everytime exit my app.

Answer 1

You should truly consider using a temporary database to house your temporary tables and the likes, see: http://www.granite.ab.ca/access/temptables.htm

You could also look at https://www.experts-exchange.com/questions/26271493/RUNNING-A-MACRO-TO-COMPACT-AND-REPAIR-MS-ACCESS-DB-IN-2007.html which presents 2 different approaches to handling this: (i) setup an upper file size limit to activate/deactivate the compact on close property so it only executes when required (ii) raw VBA code to perform a compact that you can call as required (using a button, macro, ...). 

There's always the Runcommand, see http://access.mvps.org/access/RunCommand/codeex/4.htm but it uses SendKeys which is far from ideal!