Rebuild/Repair Database 1

[mbaddar]

Hi,

I'm trying to create code that will use the repair and compact database methods so a user can repair and compact a database with the click of a command button.

This is the code that I have so far and as soon as the first line is executed the project closes and none of the other lines are executed. Can anyone see what I'm doing wrong? I'm fairly certain I have to close the database before I use the repair or compact method.

In the code below "CatalogDB" is the name of my backend database:

Me.Application.CloseCurrentDatabase

DBEngine.RepairDatabase Me.Application.CurrentProject.Path & "\CatalogDB.mdb"
DBEngine.CompactDatabase Me.Application.CurrentProject.Path & "\CatalogDB.mdb", _
Me.Application.CurrentProject.Path & "\CatalogDB.mdb"
Me.Application.OpenCurrentDatabase (Me.Application.CurrentProject.Path & "\CatalogDB.mdb&quot

Does anyone see anything I'm doing wrong?

Thanks,
MBaddar

 

[RickSpr]

You can't run code in a database that's closed!

What it comes down to is, you can only compact/repair a database from VBA code in a different database. And you can compact database B using code in database A, but only if the code in A wasn't called by code in B.

The easiest way out of this predicament is to use Access 2000. A2K has an option in the Tools>Options menu that automatically compacts the database every time you close it (or when the last user closes it, for multiuser databases).

If that's not an option, maybe you could create a Windows shortcut that runs Access with a "/compact" startup command lien option, and specifies your database. Then they could just close the database, double-click the shortcut to compact it, and then open the database again.

BTW, if you use a split database structure (tables in the "back end", everything else in the "front end&quot, there's seldom any need to compact the front end, because hardly anything changes there. The changes, and hence the need for compaction, are in the back end with the tables. And since the code runs in the front end, it's a simple matter to compact the back end as long as you don't have any tables open. Split structures are usually used for applications where the data is stored on a network server and used by multiple users, but you might want to use a split structure, even for a single-user application, to solve this problem. Rick Sprague

 

[mbaddar]

Thanks Rick,

That was a big help.

Thanks,
mbaddar