Excel to repair Access database

[adder]

Not sure whether I should post this in the Access section, but here's what I need - creat a macro in excel to compact 2 Access dbs (Risk.mdb and Riskdata.mdb)

This is a daily task for me, for now I'm doing it mannually, open each database, and go Tools --> database utilities --> compact and repair database.

I've thought about using macro within each database - whenever opening the file it triggers repair automatically, however I don't want to do it every time I open the database, instead I wish to control the repair from a master excel file.

Thanks for your help in advance

 

[Norie]

Can't you just set both databases to compact and repair on close?

 

[Ruddles]

This was discussed fairly recently - 1-2 months ago, perhaps - and I seem to recall we came to the conclusion that you couldn't trigger a compact & repair from Excel.

Try doing a forum search.

 

[ZPhantom]

Its possible but not for the faint hearted, - and sorry but havnt got my example code to hand;

you can use ADO and JRO.JetEngine i *think*

take a look at http://support.microsoft.com/kb/230501

 

[adder]

Thanks all you guys above. I decided to go with scheduled tasks. It's probably easier.

 

[Norie]

Why not File>Options and select Compact on close in the Current Database section?

PS That's for 2010, but the option is available in other versions.

 

[adder]

Hi Norie,

I should've stated it clearer... I use 2003 version of Excel and Acess. I was too lazy to manually open the 2 mdb just for the sake of compact and repair (c/r), and then manually close them. So either c/r on open or on close is not something I was looking for.

Initially I thought if hitting a button in a excel file would automatically open the 2 access mdb, do the c/r, then close mdb - that would be ideal.

For now I'm settled with the scheduled task, which automatically opens 1 mdb at a time, does c/r, and closes the mdb, at a specified time every day. I also created the task shortcuts on desktop so that if I need to c/r at any other time I can double click the shortcuts to do the job automatically.

Having said that, I'll continue to search an one-button-click solution from Excel. Thanks

 

[Ruddles]

This appears to work in Excel 2007:-

Sub CRDB()
 
    Dim oAcc As Access.Application

    Set oAcc = CreateObject("Access.Application")

    oAcc.DBEngine.CompactDatabase "c:\temp\test1.mdb", "c:\temp\test2.mdb"
 
End Sub

You need a reference to the Microsoft Access Object Library. You end up with the new database in a different file but I guess you can KILL the old file and NAME the new one back.

I can't test in Excel 2003.

 

[Norie]

Why do you need to compact and repair a database that hasn't been used recently?

If you do want code that opens, compacts and repairs then closes why not set the database to compact and repair on close?

Then all you need is code that will open and close the database.

The compact and repair will happen automatically when the code closes the database.

 

[rguy84]

I agree with Norie. The setting he is talking about is one of those "Set it and forget it" settings. It may add a few extra seconds to closing time.