Compact and Repair another DB from VBA

[yippie_ky_yay]

Hello all,

I saw the SendKeys function - but how could I compact and repair another database? (preferably without adding any code to the target db at all).

The reason is that I need a certain table deleted from the database I use for the web - so it would be nice to compact it afterwards too!

Thanks in advance,

-Sean

 

[fuzzygeek]

Go to the VBA help screen and look up the compactdatabase method. Both JRO and DAO examples for compacting DBs are given. Yes using code you can compact another database.

 

[yippie_ky_yay]

Thanks fuzzygeek! I'll look that up.

Never heard of JRO before - what is that? Is it another name for ADO?

Thanks again,
-Sean

 

[Nero]

Never heard of JRO before - what is that? Is it another name for ADO?

Jet Replication Objects. It's an extension to ADO used for compact/repair.

 

[Mark Wild]

There are a few samples in this showthread.php?s=&threadid=34436&highlight=compact for using code to comapct other dbs (typically BE's in the samples)

 

[simongallop]

Try this:

Function Compact_DB()
'SET PATH
Path = "C:\MyFiles\dev\"

'COMPACT CHOSEN DATABASE, TO TEMPORARY DATABASE NAME
DBEngine.CompactDatabase Path & "MyDatabase.mdb", Path & "Spare1.mdb"

'DELETE OLD DATABASE
Kill Path & "MyDatabase.mdb"

'RENAME TEMPORARY DATABASE TO ORIGINAL NAME
Name Path & "Spare1.mdb" As Path & "MyDatabase.mdb"

End Function

Haven't tried this in 2k though. Built it for 97

 

[yippie_ky_yay]

Thank you everyone who replied!

Harry, I used your code in Access 2000 and it worked like a charm! Even better than expected because I only need to compact the db before moving it to a seperate folder - so I didn't need to bother with the renaming, and deleting lines (I just specified a different path for the destination).

Thanks again!

-Sean

 

[mdjks]

worked like a charm in 2010 as well