Compact and Repair in Access 07

[Steve]

I am making a maintenance routine to compact and repair the database
automatically once a week. I had it working in Access 2003 perfectly, but I
need to get it working in Access 2007 now. I tried sendkeys with %(FMC), but
this does not work when called from the maintenance code. It will work if I
have it on the onclick event for a button, but not when I call it from my
maintenance routine. In Access 2003 I used the code below called from the
maint routine and it worked perfectly.

Function CompactDB()
CommandBars("Menu Bar").Enabled = True
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Function

Is there an equivalent in Acces 2007 I can use. If not, is there another way
to code this in VBA to automatically compact & repair the current db?

Thanks
Steve

 

[Steve]

For those that are interested, I figured out how to do this. Here are the
steps.

1. You only need to complete this step if you have limited the access to
menus for the db. If you can go to the Office Button, then Manage, then
Compact and Repair Database, you DO NOT need to do this step. I had the db
set up so that all menus were disabled, so I had to customize the Office
Button to add ONLY the Compact and Repair Database button. You can figure out
how to do this at this MS website:

http://msdn.microsoft.com/en-us/library/bb187398.aspx

If you must customize your Office Button, what is important is that the ONLY
button with a ALT+C shortcut on the Office Button must be the Compact and
Repair Database button (so this means you must hide the Close Database
button).

2. If you had to customize the Office Button as listed in step 1, you need
to call the following VBA command to run the Compact & Repair:

SendKeys "%(FC)"

If you DID NOT have to customize the Office Button as listed in Step 1, you
need to use the command

SendKeys "%(FMC)"

F is the Office Button, M for Manage, C for Compact and Repair.

This is the same as using the ALT+F+C (or ALT+F+M+C if your db has full
menus) on your keyboard which will trigger the Compact and Repair Database.

Something you must be aware of is that the SendKeys must be used at the end
of any code you may have running. I have it set up so the db will back itself
up. The backup routine sets a timer on a form (set for long enough for the db
to copy itself). The only command for the OnTimer event is the SendKeys. The
SendKeys fires and the db does a compact and repair.

This function works just like the code I listed in my first post that I used
for Access 2003.

I had read several websites that said it was impossible to do this in Access
2007, but they're wrong.