Compacting a Database? 3

[Cleis]

Is it possible to compact and repair a database upon a user exiting a database. If not, is their a way to do this via a command button on a form? I'm working with Access 97 and have an app that deletes large recordsets from +20 tables then pulls in the fresh data via odbc from our AS/400. I do this so that the user can work off line and because of speed issues. Any help would be appriciated

Thanks

Rich

 

[xtreme1]

Sure, you could just write another MDB thats only purpose is to compact the other.
Just write the code necessary to compact the external database, then include a macro named "Autoexec" which executes that code. The compacting code should also exit the entire "mini-database" when it has completed compacting. The "Autoexec" macro, if you haven't come across it already, is a special macro which will execute on the opening of the database. This of course follows along the lines of Autoexec.bat.
After you've set that up, just make sure to include the opening of that database at the close of your main one(put the opening statement after all of the code that runs the delete queries). This way, the database closes and the code you need run to compact the database is run in an entirely seperate file. I hope that I've explained this in a way that is able to be understood. If you have any questions or need any other help, drop me an e-mail and I'll see what I can do. Good luck! =]

 

[Katriona]

Try looking up the CompactDatabase Method in the Access helpfile for some code to help you out with this.

 

[christheprogrammer]

Although not truly "programming", this method will work (May require minor tweaking).

Function compact_database()
' Compact the current (open) database.

SendKeys ("%(W1)&quot
SendKeys ("%(TDC)&quot

End Function

What this does is: %(W1) means "press alt-w alt 1"
-this brings the database view into focus.
% (TDC) means "alt-t alt-d alt-c"
-the sequence of commands to compact current database.

The reason I did it this way was because if you use the compactdatabase method, it always asks you which db to compact, and you can't compact the current one. This is the only way, I think. Good luck

Chris

 

[Birddog]

I'm not sure about Access 97 but with Access 2000 you can go to tools -> options -> general and choose "compact on close".

I don't have a copy of Access 97 loaded to test this but I hope you can do it in that version also.