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
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."
Keywords: kbinfo kbusage KB100923