Monday, June 25, 2007

Compacting current database in Access

In Access 2000 and Access 2003 there was a way to compacting the current database using this VBA code.

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

It doesn't work in Access 2007.

You probably will get Run-time error '5': Invalid procedure call or argument

But it works when changed to this.

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

or using DoCmd.RunCommand acCmdCompactDatabase

The major problem is that you instead will get this error message.

You cannot compact the open database by running a macro or Visual Basic code.

Instead of using a macro or code, click the Microsoft Office Button, point to Manage, and then click Compact and Repair Database.

What I did was to use this code to make it compact when closed.

Application.SetOption "Auto Compact", True

And when loaded restore it to not Auto Compact when closed.

Application.SetOption "Auto Compact", False


Some of the latest blog posts

Subscribe to RSS headline updates from:
Powered by FeedBurner