Discussion Locked

Compact / Repair databases from Access

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am in Microsoft Access. I need to automate a
procedure to Go to Tools Menu, Click Database
Utilities and Click Compact/Repair Database. The
procedure might be a macro or a module in
Microsoft Access.
Thank you.

11 replies

by sl-campbell

Just go to the Macro tab, select new, in the grid select ExecuteCommand (I believe this is the text in english) and at the bottom of the screen, select what you want done, do the same for each command that you want, save the macro.

by electraglide

Hello,
I understand that you want to make this an automated action, but Access has a feature that will allow you to compact and repair the database everytime it is closed.

You can do this by opening the database, selecting the tools menu, then options. Select the general tab, and make sure the Compact on Close option is selected.

The only time this will not occur is if this is a multiuser application, and there are still users using the application at the time of close, but from what I understand, even if you automate this, and it is a multiuser application, it will not compact and repair if there are users still using the application.

Mike

by rrv

write a macro like this
[DBEngine.compactdatabasee "C:\Old.mdb", "C:\New.mdb", _ dbLangGeneral, dbEncrypt]
change the file names as necessary. or take more help in dbengine and compactdatabase keywords in MSAccess. This works fine and is easy touse.

by dushy_ant

If u want to do it through a front end like VB, C++ or Delphi, then u may use AdoX extension for Access. More information on it can be had from msdn.microsoft.com

by bob sellman

Since the Access database needs to be closed to be compacted, why not just put a shortcut on the desktop to do the compacting?

Set up a shortcut that opens Access (msaccess.exe), includes the database name, plus has /compact after the database name. This will open the database and automatically compact it.

A sample of the target in the properties of the shortcut might be something like this:

"C:\Program Files\Microsoft Office\Office\msaccess.exe" d:\myapp\myapp.mdb /compact

And once you have the shortcut set up, you can copy the shortcut file to other workstations as necessary. Note that no one can be connected to the database in order to run compact.

This discussion is closed to new replies.