"Compact and Repair Database" Command in a Form

[Michael]

I would like to run the "Compact and Repair Database"
command by clicking a command button on a form. I am using
MS Access 2000.

The "DoCmd.RunCommand acCmdCompactDatabase" cannot work
while running a VB code or macro. Also, I can't find the
right parameters to use with "DoCmd.DoMenuItem" for that
command.

I already created a custom toolbar with this command. But
this is the top menu. I would like to have it also in the
main menu, which is a form.

Can it be done?

Thanks,

Michael

 

[Douglas J. Steele]

You can't compact the current database through code.

 

[Alp Bekisoglu]

Hi Michael,

The following worked for me. Try it in a copy and see if it also works for
you. The code is behind the OnClick event of a commandbutton, in my case the
button name is CompactDB.

Private Sub CompactDB_Click()
On Error GoTo Err_CompactDB_Click

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

Exit_CompactDB_Click:
Exit Sub

Err_CompactDB_Click:
MsgBox Err.Description
Resume Exit_CompactDB_Click

End Sub

Hope this helps,

Alp

 

[Michael]

It works!

Thank you very much!

Michael

-----Original Message-----
Hi Michael,

The following worked for me. Try it in a copy and see if it also works for
you. The code is behind the OnClick event of a commandbutton, in my case the
button name is CompactDB.

Private Sub CompactDB_Click()
On Error GoTo Err_CompactDB_Click

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

Exit_CompactDB_Click:
Exit Sub

Err_CompactDB_Click:
MsgBox Err.Description
Resume Exit_CompactDB_Click

End Sub

Hope this helps,

Alp



.

 

[Guest]

This code works fine on condition that it is the only line in the OnClick-event of your pushbutton

If you try to put more commands (like a complete VBA procedure), it won't work
You get a message "You can't compact the open database while running a macro or Visual Basic code. Instead of using a macro or code, on the Tools menu, point to Database Utilities, and then click Compact/Repair Database.

I have no explanation for this strange behaviour, I just found out after a lot of testing, so don't ask me why ..

JMathys