You are currently offline, waiting for your internet to reconnect

ACC: Sample Macro for Compacting and Repairing a Database

This article was previously published under Q100923
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article demonstrates a sample macro that repairs and compacts adatabase.
MORE INFORMATION
The sample macro in this article automates the process of compacting andrepairing a database. It repairs a database called MYDB, and compacts it toa new database called NewDB. The macro will only prompt for information ifthe name of the database it is compacting to already exists.

NOTE: The macro must be run from a different database than the one you wantto 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				

Notes


  • In most cases this macro works fine. However, remember that keystrokes sent by the SendKeys action are buffered temporarily and that results may vary. A large database may take extra time to compact or repair; in this case, the macro may not exhibit expected behavior.
  • In Microsoft Access 2.0, you can use new command-line options to create an icon in Program Manager to compact or repair your database. You can also use the data access objects (DAO) methods CompactDatabase and RepairDatabase from Access Basic.
  • In Microsoft Access 7.0 and 97 you can use command-line options to create a shortcut on your desktop to compact or repair your database. You can also use the data access objects (DAO) methods CompactDatabase and RepairDatabase from Visual Basic for applications.
  • In Microsoft Access 97, you do not have to close the current database in order to repair it or compact it. On the Tools menu, point to Database Utilities and then click Compact Database or Repair Database. Microsoft Access automatically closes and reopens the database after the compact or repair command is finished.
REFERENCES
For more information about command line, CompactDatabase, or RepairDatabasesearch the Help Index for "command line", "CompactDatabase", or"RepairDatabase."
Properties

Article ID: 100923 - Last Review: 01/18/2007 22:39:31 - Revision: 2.1

  • 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
  • kbinfo kbusage KB100923
Feedback