Compact & Repair in access07

[AstonCheah]

In Access 03, i was using this code to compact my data.

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

but it failed in access 07, how can i compact my data in access 07? anyone can help? thanks.

 

[RodgerDJr]

At what point do you want to do this? Do you want the user to click on a button when they want to do this? or as a scheduled task?

 

[AstonCheah]

I just want to create a button, and do it whenever needed. thanks.

 

[boblarson]

Well, I don't normally suggest SendKeys but in this case I will make an exception since this has worked well for me.

Public Sub CompactData()
' you must make sure all objects are closed before trying to compact
Dim frm As Form
Dim rpt As Report
For Each frm In Forms
   DoCmd.Close acForm, frm.Name, acSaveNo
Next frm
For Each rpt in Reports
   DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt
        SendKeys "%(FMC)", False
End Sub

Put it in a standard module and name the module something like modUtilities and then call the function from your command button.

 

[boblarson]

Fixed a typo

 

[AstonCheah]

Boblason,

thanks for your help, but i can only run your code when the allow full menu in access options is set to true.

to protect my design, i have set it to false and disabled the bypasskey. how can i run the compact & repair by clicking a button if the allow full-menu in access options is set to false and the bypasskey is disabled? is it advisable to use the 'compact on close' function in a multi-users database?

 

[odoyle81]

bump...

I have the exact same problem - I'd love to hear any solutions!

 

[AstonCheah]

odoyle81,

now i am using the 'compact on close' function to compact my database, and this function will automatically compact it when the last user closes the database. it works well for me till today.