Compact and Repair Access from VB

[dburnham]

I have created an application in VB using Access. I need to figure out how to use the Compact and Repair feature found in Acces either with in my application or as a stand alone feature distributed with my application. Any and all help is appreciated.

 

[sunila7]

I think u use this for Compacting DB
DBEngine.CompactDatabase olddb, newdb, locale, options, password
and
DBEngine.RepairDatabase dbname to repair Db....

but be sure that u have closed the database before u start doing a repair or compact...

hope it helps
Sunil

 

[MikeCox]

Take a look at the VB object browser (F2) for Access, that should get you going with the VB syntax. I think it'll be something like objAccess.DoCmd(acCmdCompactDatabase). Another thing you should be aware of is that all users must be logged out of the db including your VB app before it'll run. I think the simple solution to that would be to close any recordset connections you have open. Just wanted to make sure you didn't get bit by that later...

HTH!

~Mike
Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin

 

[AdamIT]

Add a reference to the DAO object library and use this code:

DBEngine.CompactDatabase "c:\source.mdb", "c:\destination.mdb"
DBEngine.RepairDatabase "c:\source.mdb"

These two features will compact and repair your database. Unfortunately, since this is DAO technology, it only work on Access 97 databases.

You could look into using the Access object and use automation to work with a 2000 database:

Dim objAccess As Access.Application
objAccess.RunCommand acCmdCompactDatabase

But I don't know the details of the Access.Application object.

HTH,
Adam

 

[MikeCox]

Here ya go:
------------
Dim XS As Access.Application
XS.OpenCurrentDatabase "c:\test.mdb"
XS.DoCmd.RunCommand (acCmdCompactDatabase)
Set XS = Nothing
------------

Good luck!

~Mike
Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin

 

[dburnham]

Thanks to All!!

 

[dburnham]

When using
DBEngine.CompactDataBase "source", "Destination"
I receive a message that the DB already exists. How can I compact the database with out creating a new DB or changing the name.

Is it possible to delete the original DB and then change the DB name of the ompacted DB back to the original file name?

 

[petermeachem]

That's exactly what you need to do. Best to make a copy before you compact unless it gets screwed, and then of course you need code to copy the backup back. Peter Meachem
peter@accuflight.com