How Can I Compact the Current Access Database in Code?

In my previous two posts: (What Does It Mean to Compact My Access Database? and How Do I Compact an Access Database?),  I discussed what compacting a database does and how to do it through the Access User Interface.  But it is also possible to use VBA code to do it as well.

Why would you want to use code?  Well, if you're making an application for others to use, you might want to give them a button to compact the database so they don't have to know the menu/ribbon selections.  You may also want to control when or how the compacting is done.

accDoDefaultAction Method
In Access 2003, it was possible compact the current database with the following code:

• CommandBars("Menu Bar"). _
  Controls("Tools"). _
  Controls("Database utilities"). _
  Controls("Compact and repair database..."). _
  accDoDefaultAction

In Access 2007 and 2010, however, this method no longer works.  The command will still execute (modified slightly--notice the new first line and the ellipsis missing from the end?)

• CommandBars("Menu Bar").Enabled = True
  CommandBars("Menu Bar"). _
  Controls("Tools"). _
  Controls("Database utilities"). _
  Controls("Compact and repair database"). _
  accDoDefaultAction

However, it will return with an error message: 
You cannot compact the open database by running a macro or Visual Basic code.

Well, that's not exactly true.

Send Keys Method
The Send Keys method is a way of sending keystrokes in code to the Access Interface.  Since the menu/ribbon is different depending on your context (and which version of Access you're using) the send keys argument can vary quite a bit.

First of all, you need to put the code in an Event Procedure behind a button on a form.  This will give us the form menu/ribbon context.

Access 2003: Sendkeys "%(TDC)", False
Access 2007: Sendkeys "%(FMC)", False
Access 2010: Sendkeys "%(YC)", False

I'm not really partial to that solution, though, because, as I said, I don't like to rely on a specific key sequence that can vary from context to context and version to version.

DAO CompactDatabase Method

A better solution, I think, is to use the DAO CompactDatabase method. The downside is that you can't use that on the current database. The workaround for that is to:

1. Programmatically create a new, temporary database
2. Copy several pre-created forms, macros and modules to this temp database
3. Close the main database and launch temporary database with the /x switch which initiates the compact process.
4. After compacting is done, close temp database and relaunch main database

On my website is a small sample database called "CompactDatabase.mdb" which illustrates this process.

The CompactDatabase method can also be used on an external database (in fact, it's easier to do so.) This is useful if you have split your database into a Front-End and a Back-End. You can use the CompactDatabase method directly from the Front-End against the Back-End. The BE, which stores the tables, is generally the one that needs compacting most. I've also got a sample called CompactBackEndDB.mdb which illustrates this.

The real usefulness of the DAO method is the safety and flexibility you have.  Instead of deleting the old database, you can rename it (say, by adding a date to the file name) so that you keep a backup.  In fact, the CompactDatabase method can also be used simply to create a backup of the database.

Compacting With ADO
For completeness, I should mention it's also possible to compact a database with ADO.  However, since I have no direct experience with it (I prefer DAO), I'll just give a Microsoft knowledge base article on the subject: http://support.microsoft.com/kb/230501/en-us.

Compacting Less Often
No discussion of compacting would be complete without addressing methods to reduce the need to compact in the first place.  I'll conclude this series next time with that.

.