Compact And Close Database With Delay, Access 2007

[Ramya_mudambi]

Hi,
I'm struck with an issue "how to COMPACT the DB by introducing delay of 10 seconds and then close the DB"
In the Database, I'm able to accomplish the "Compact" the database using the function below.

Function Compact()
SendKeys "%(FMC)", False
End Function

As my DB is quite huge, the Compact action takes around 10 seconds to complete.

Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.

Function Compact()
SendKeys "%(FMC)", False
DoCmd.Quit
End Function

I would need some guidance on how to introduce this delay of 10 seconds and then close the DB.

Kindly help.

Many thanks.

 

[pr2-eugin]

Why don't you have Compact On Close option under File->Options->Current Database as the default? This will not need for you to manually use SendKeys. Only needs DoCmd.Quit, which will automatically compact as it is closing.

 

[Ramya_mudambi]

The database is going to be used for Reporting purpose and will be used by just 1 person only. This is not a SPLIT DB.
As my requirement is quite Unique, the compact operation needs to be performed on a click of a button on a specific occasion ONLY !! This is the reason Compact ON Close has not been enabled.

I leaned towards SENDKEYS.

 

[MarkK]

The problem is that a Compact & Repair actually completely closes your database. You can test this.

1) Set a start up form.
2) run a compact & repair
3) notice that your start up form opens.

When you use the menu, (same as sendkeys) Access doesn't close, but your database does, and then it re-opens immediately. The impact, however, is that your running code is killed because the whole context in which it runs, the whole database, gets shut down.

If you must have your requirement met, you can set a flag in a table, run your compact & repair, and read the flag on re-open. If the flag was set, then your last shut-down was a compact & repair, clear the flag, wait your 10 seconds , and then shut down again.