Run Companct and Repair

[jgelpi16]

I am looking for a way to have a compact and repair run by the click of a button instead of having to use the option on the menu. Any suggestions? Thank you!

[Access_Blaster]

CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
   accDoDefaultAction

[jgelpi16]

Thank you for your help.

[jgelpi16]

Unfortunately when I tried to use this code I received the following error:

"You cannot compact the open database by running a macro or Visual Basic code.

Instead of using a macro or code, click the Microsoft Office Button, point to Manage, and then click Compact and Repair Database."

Any suggestions?

[Access_Blaster]

You have the code behind a standard button?

[Access_Blaster]

I just read the part about the OPEN form that is true, it needs to close first. Infact I believe all objects have to be closed.

[Rawb]

Your best bet would be to create a new Shortcut on your Desktop to Compact and Repair the database.

When you create the shortcut, point it to MSACCESS.EXE instead of your database then do the following:
Add the path and filename to your database after MSACCESS.EXE
Add "/compact" after your database path

Once you're done, you'll end up with a shortcut that points to MSACCESS.EXE like the following:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\Database\MyDB.mdb" /compact

Whenever you run that shortcut, it'll Compact and Repair your database for you.

[jgelpi16]

Well My goal is this. I have a bunch of append queries that run and to keep the DB within the 2GB size constraints I need to run a compact and repair after running a portion of the queries. So what I was going to do is develop a button that would run Query1, Query2, Query3 and then run a compact and repair. This would save me from having to manually run each query and then manually clicking the compact and repair button. The only code I have in the command button is the code you provided above.

I guess I could just have the command button run the queries and then run the compact and repair myself.

[Rawb]

Hrm,

You may be out of luck then because it looks like you can't run a Compact and Repair from within the database you're Compacting and Repairing.

Have you considered splitting the database into two (or three?) separate ones? That would allow you to keep individual file sizes below 2GB fairly easily, unless 99% of all your data is in a single table.

[jgelpi16]

Yea, splitting up the DB would too much of a hassle for what I am doing. I will just run the compact and repair seperately. Thanks for your help.