ACC: Sample Macro for Repair/Compact Operations

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

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

   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

   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