Compact Access database from VBA

Question

Hi,

Is there a way when I am running VBA in a database to compact the database during the run.

E.g., start vba code which runs some queries that create some large tables and then database compacts and then run some more queries creating more large tables and compact again etc.

Thanks!

Answer 1

Sure, it's possible.  On my website is a small Access database which illustrates how(CompactDatabase.mdb).  But as Dirk mentioned, there is the problem of starting the process where you left off.  I think that problem is solveable, but I don't have a ready made example.  It could be incorporated into the CompactDatabase process, though.

However, it would probably be simpler if you just programmatically created a temporary database (or more than one if necessary), created and manipulated your data, then destroyed the temp database.  I have another sample that illustrates this that is used for importing large amounts of data (ImportToTempDatabase.mdb), but the idea could be extended to your situation.

--Roger Carlson MVP

Answer 2

Hi,

Is there a way when I am running VBA in a database to compact the database during the run.

E.g., start vba code which runs some queries that create some large tables and then database compacts and then run some more queries creating more large tables and compact again etc.

In Access 2000 - 2003, it was possible to compact the current database from code within it by invoking a command bar function;  see this link: http://www.mvps.org/access/general/gen0041.htm .  However, I don't know whether or not that still works in Access 2007, since we have the ribbon instead of command bars.  Try it and see.  If it does, great;  if not, you can use an external utility such as Michael Kaplan's SOON, or even the mdb compactor downloadable from here:http://www.mvps.org/access/general/gen0013.htm .

However you do it, if you want to do it in the middle of a process, the process won't know to automatically restart, and where to restarrt, unless you record this somehow.  You could have a table in which you record the fact that you are in the middle of the process, and what steps you have completed.  Then, at startup you can run code that checks whether you are in the process and picks up where you left off. 

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html

Answer 3

On Sun, 23 May 2010 23:37:03 +0000, Charles1422131 wrote:

You can't really compact yourself, but perhaps this utility would

help: http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8

Of course if the tables are in an external database you can certainly

compact them.

-Tom.

Microsoft Access MVP

>

>

>Hi,

>

>Is there a way when I am running VBA in a database to compact the database during the run.

>

>E.g., start vba code which runs some queries that create some large tables and then database compacts and then run some more queries creating more large tables and compact again etc.

>

>Thanks!

---

-Tom. Microsoft Access MVP