ACC: Sample Macro for Compacting and Repairing a Database

Article translations Article translations
Article ID: 100923 - View products that this article applies to.
This article was previously published under Q100923
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

On This Page

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
				

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 RepairDatabase search the Help Index for "command line", "CompactDatabase", or "RepairDatabase."

Properties

Article ID: 100923 - Last Review: January 18, 2007 - Revision: 2.1
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
Keywords: 
kbinfo kbusage KB100923
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.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com