Compact and Repair Database (1 Viewer)

This morning my Work Order database was giving a "Invalid Argument (3001)" error message on one of my queries. After some troubleshooting, I figured out it something was corrupt. So I ran Compact and Repair and this fixed my problem. The people who use this database are not very familiar with Access so I thought I would write a macro and assign it to a button where they can periodically do a compact and repair. Well sure enough there are built in macro Run Commands for "CompactDatabase" and "RepairDatabase". So I made a macro to close the open one open form, then run RepairDatabase, then run CompactDatabase. However when I run the macro, I get the error message "You can't compact the open database while running a macro". Well why are these run commands built into Access macros if you can't do it?

Am I doing somethign wrong or is there an easier way have novice Access users the ability to run compact and repair?

Thanks,
Jim

If you are using A2K or above you can compact & repair every time the db closes.
Goto Tools >> Options >> General Tab and select 'Compact On Close'

HTH

Or, if you really want to compact from the click of a button, use the following in the OnClick event.

HTH

___ said:

If you are using A2K or above you can compact & repair every time the db closes.
Goto Tools >> Options >> General Tab and select 'Compact On Close'

HTH

Click to expand...

It works great if you are the only one in a database at the time you close it. We tried it with databases that usually have more than one databases and kept geting db(some number) because it could not replace it because it was open.

So we only have some databases compact and repair on close and others we do by hand.

Depends on how your office is setup.

We also have assigned people to compact and repair the databases when the network goes down or we have the power go off.

Kim

You can automate the compact and repair of access databases using a utility like, Digital DBA for MS Access Administrators. This utility also lets you schedule automatic backups and has email notification if your database becomes unavailable.

Compact and Repair

You may want to check this link, there is an utility there that allows the user to set a time and location to backup, repair and compact multiple databases.

http://www.candace-tripp.com/_pages/access_downloads.asp

Great list of examples! I will have a great time checking them out. The autobackup that includes a repair feature will probaby just what I need. That way I can run it over night when no one is using the database.

Thanks!

Jim

I have been trying to find a better solution, but for now I am using SendKey("FMC") to compact and repair my database from within the VBA code. One problem is that if another application is open and has the focus it receives the "FMC" keystrokes rather than the Access app. It works as long as Access has the focus.

___ said:

Or, if you really want to compact from the click of a button, use the following in the OnClick event.

Code:Copy to clipboard

        SendKeys "%(TDC)", False

HTH

Click to expand...

Works Like a Charm! Exactly what I needed! Thanks!