Compact in Access97 without Prompting

[SillyMcGilly]

I have read numerous threads on this, but no one has really answered the question the way I need it answered.

I am looking to Compact on Close in a Access97 database. I know Access 2k has this option in the Options, but 97 does not. I know I can use code like:

sendkeys "{F10}", true
sendkeys "TDC"

when I close my database, but this is no good because the user still gets prompted "Which database??" How do I do a compact on close that is totally oblivious to the user. When I manually select "Tools">"Database Utilities">"Compact & Repair", I DO NOT get prompted "Which database?"...Access just seems to know it is the current one. How do I get this same effect but automatically with no user interaction in code.? Maybe this has been posted, and I am just an idiot.

Please help.
Thanks

 

[ChrisO]

Well this wouldn’t be the easiest way to do it but it works.

It uses a second small DB to compact the Main db.

When you hit the ‘close and compact’ button, watch the task bar.

Hope that helps.

Regards
Chris

 

[SillyMcGilly]

Chris,

WOW. Thanks. I took a quick look at the attachment, and boy, what a lot of work to compact it! I will have to take a longer look at it to understand it better, as my VBA experience is mediocre.

If anyone has any easier method to do this, please respond. I have a custom toolbar ONLY on my database. The only 3 options are print, print preview and compact database. I may just pop-up a msgbox and tell them to press the compact button before they close.