Pretty simple question, I know.
15 Answers
In response to the excellent post by jdawgx:
Please be aware of a flaw in the code for CompactDB() above.
If the database's "AppTitle" property is defined (as happens when an "Application title" is defined in the database properties), this invalidates the "default window title" logic shown, which can cause the script to fail, or "behave unpredictably". So, adding code to check for an AppTitle property - or using API calls to read the Window title text from the Application.hWndAccessApp window could both be much more reliable.
Additionally, in Access 2019, we have observed that:
SendKeys "multi-key-string-here"
... may also not work reliably, needing to be replaced with:
SendKey (single-character)
'put a DoEvents or Sleep 150 here
SendKey (single-character)
'put a DoEvents or Sleep 150 here
SendKey (single-character)
'put a DoEvents or Sleep 150 here
SendKey (single-character)
...to get proper responses from the Access UI.
ALSO for Access 2019:
Sendkeys "%yc" ( <-- works for Access 2016)
is no longer correct.
it is now:
Sendkeys "%y1c"
...and if that little change wasn't enough - try to determine (in code) how to tell the difference between Access 2016 and 2019 - Good Luck!! because Application.Version alone won't help, and even combining Application.Version and Application.Build is not a guarantee (unless you are in a controlled-release enterprise environment, and then it may work as the possible version/build #s in circulation should be more limited).
Please Note the following - all of you who favor doing a "Compact on Close" solution for MS-Access.
I used to prefer that option too, until one day, when I received the WORST error message possible from the DBEngine during a Compress & Repair operation:
"Table MSysObjects is corrupt - Table Truncated."
Now, you have probably never realized that THAT error is even a possibility.
Well, it is. And if you ever see it, your ENTIRE DATABASE, and EVERYTHING IN IT is now simply GONE. poof!
What is funny about that is that Access will let you actually reopen the "fixed" database, only, the Access window and menu items are all now utterly useless (except to close the DB and exit access again) because ALL the tables (including the other MSYS* tables, forms, queries, reports, code modules, & macros) are simply gone - and with the disk space previously allocated to them released to the tender mercies of the Windows OS - unless you have additional protection than the bog-standard recycle bin, which won't help you either.
So, if you REALLY want to accept the risk of Compact on Close completely clobbering your database - with NO POSSIBILITY of recovering it, then please...do carry on.
If, OTOH, like me you find that risk an unacceptable one, well, don't enable C&R-on-Close - ever again.
Related