My Front-end database has several local tables used for collecting data to print reports and do mail-merges. All other tables are linked to my back-end database. As these reports are created, the database gets larger. So if the database gets too large, I want the user to be able to fix this.
The Main method gets the current size of the database on opening. In my Switchboard Activate event, it gets the now current size of the database. If this is larger than the original size (by a pre-determined percentage), it prompts the user and asks if they want to fix this condition. If they click YES, it runs a macro which clears the various work tables.
I want it to also compact/repair the database as if they clicked File>Info>Compact & Repair or Database Tools > Compact and Repair Database button on the ribbon.
Can't seem to figure out this last part. However I write code to compact / repair the database, it tells me I can't do this in code or in a macro.
There are a couple of ideas, but not sure which ones will work.
- I could write a VBScript to rename the database, then automate Access to do this, call that script in a shell command, and immediately quit Access, but the script would have to know when the operation was complete, then run the database again.
- I could try to use the CommandBars objects, but not sure if they will work for this. The few examples I tried would not work.
- I could simply put up a MessageBox and tell the user to click the Compact & Repair button, but that seems amateurish.
Any ideas how I could accomplish this?
Thanks...
UPDATE: I can get this to work by executingBut I hesitate to use SendKeys as it causes strange behavior sometimes. In this case it may be ok, because all it does is compact / repair the database then run it again, right?Code:SendKeys "%fic"