Microsoft KB Archive/100923

= ACC: Sample Macro for Compacting and Repairing a Database =

Article ID: 100923

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q100923



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article demonstrates a sample macro that repairs and compacts a database.



MORE INFORMATION
The sample macro in this article automates the process of compacting and repairing a database. It repairs a database called MYDB, and compacts it to a new database called NewDB. The macro will only prompt for information if the name of the database it is compacting to already exists.

NOTE: The macro must be run from a different database than the one you want to repair and compact.

In Microsoft Access 97, create the following macro:

  Macro Name       Action Test            SetWarnings SendKeys RunCommand SendKeys RunCommand SetWarnings

Test Actions -  SetWarnings WarningsOn: No  SendKeys Keystrokes: Mydb.mdb{enter} Wait: No  RunCommand Command: RepairDatabase SendKeys Keystrokes: Mydb.mdb{enter}NewDB{enter} Wait: No  RunCommand Command: CompactDatabase SetWarnings Warnings On: Yes

In Microsoft Access 7.0, create the following macro:

  Macro Name       Action Test            SetWarnings SendKeys DoMenuItem SendKeys DoMenuItem SetWarnings

Test Actions -  SetWarnings WarningsOn: No  SendKeys Keystrokes: Mydb.mdb{enter} Wait: No  DoMenuItem Menu Bar: Startup Menu Name: Tools Command: Database Utilities SubCommand: Repair Database SendKeys Keystrokes: Mydb.mdb{enter}NewDB{enter} Wait: No  DoMenuItem Menu Bar: Startup Menu Name: Tools Command: Database Utilities SubCommand: Compact Database SetWarnings Warnings On: Yes

In Microsoft Access version 2.0 and 1.x, create the following macro:

  Macro Name       Action Test            SetWarnings SendKeys DoMenuItem SendKeys DoMenuItem SetWarnings

Test Actions SetWarnings WarningsOn: No  SendKeys Keystrokes: MYDB.MDB{enter} Wait: No  DoMenuItem Menu Bar: Startup (in Microsoft Access version 1.x = Init) Menu Name: File Command: Repair Database SendKeys Keystrokes: MYDB.MDB{enter}NewDB{enter} Wait: No  DoMenuItem Menu Bar: Startup (in Microsoft Access version 1.x = Init) Menu Name: File Command: Compact Database SetWarnings Warnings On: Yes