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

1 comment:

  1. Restored comment
    by vish
    Hi,

    Nice posting. If you are getting error using compact and repair tool then you have other option of stellar phoenix access repair software to repair your database. It is one of the good software utility which easily repair MS access database.

    Thanks

    ReplyDelete

Some of the latest blog posts

Subscribe to RSS headline updates from:
Powered by FeedBurner

Contact Us | About JTB World | Subscribe to this blog
JTB World's website | Website General Terms of Use | Privacy Policy
^ Top of page

© 2004- JTB World. All rights reserved.