Compact and Repair through VBA

[a_20120]

when I did compact and repair through the VBA, the following error appears...

Your help appreciated...

 

[a_20120]

Hello Every One,
1. Any idea about Compact and Repair of the DB?
2. The advantages and disadvanteges of that
3. How Compact and repair through Coding?
...

 

[RuralGuy]

Compact on Close

 

[a_20120]

Thanks 4 replying...
is it possible to do the compact and Repair when u click on Button?

 

[RuralGuy]

DoCmd.RunCommand acCmdCompactDatabase

 

[a_20120]

Thanks Agian.
But when I click the Button it says Not Possible to Compact while the Macro is Open and something like that...like the above thread attachment...

 

[RuralGuy]

Why not just compact on close as is done in the first link?

 

[boblarson]

Put this in a STANDARD MODULE (not a form module):

Public Function CompactData()
        SendKeys "%(TDC)", False
End Function

Next, in the event you want do compact from put this:

DoCmd.Close acForm, Me.Name, acSaveNo
CompactData

Don't change the Me.Name part - leave it as is.

 

[a_20120]

Thanks All,
It is not actually compact and repair the DB, I saw in some Project there is Button, through that button they compact and repair the project...
Plz show me a way doing compact and repair with a best way also not harmful with the DB

 

[RuralGuy]

Bob's suggestion *will* work.

 

[halem2]

my 2 cents in...

it sure does work. Thanks Bob.

 

[boblarson]

One note though - It WON'T work in Access 2007. For Access 2007, you need to change it to:

SendKeys "%(FMC)", False

 

[gear]

Just a doubt please

I followed Bob's suggestion and used the Send Keys Method. It works fine. But I shall be grateful if you could clarify a little. Will this code only compact the DB or will it perform the compact and repair. Please clarify.

 

[boblarson]

The only thing that exists within Access is Compact AND Repair, they don't exist as separate entities.

 

[gear]

Thank you Sir.

 

[BrettStah]

This works for me (Access 2003) - put this sub in a code module, and then in the Click event of your button on your form, just call the sub:

Public Sub CompactDB()

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

End Sub

 

[gear]

Now Compact & Repair now working properly

Dear Bob Sir,
I created a Module as under:

Public Sub CompactData()
DoCmd.Close acForm, "frmMainMenu", acSaveNo
SendKeys "%(TDC)", False

End Sub


In the OnClick event of Command button I put this

CompactData

It was working fine earlier. When I clicked the button, the DB closed and reopened with the usual security warning. As I wanted to show this feature to a new user, when I clicked the button, the DB closed and nothing happened. I had to open the DB from the Desktop shortcut. What could have caused this error? Could you please explain?

 

[Steve5]

Very Helpful Bob - Thankyou