Automating Compact & Repair Routine

[missymarie1014]

I have an Access 2000 application that I have written that is completely menu driven. Users have no access to the typical menu bars or context menus. I am wondering if there is a way to automatically run the Tools|DatabaseUtilities|CompactAndRepairDatabase routine when the user exits the software or when the user opens the software. Can you reference this routine through code somehow and make it happen?
Or, in lieu of this and to avoid potential problems if multiple users are accessing the program, is there a way to automate that process to run at a particular time, say after the store is closed and the software is definitely not being used?
Thanks in advance for any help and ideas!

 

[genomon]

You cannot execute code to compact a database from itself.
You can either check the option to "Compact on Close", or create an exe or mde that compacts from Windows Scheduler (or an open app with a timer). The VBA would look something like

DBEngine.CompactDatabase DBName, NewDBName

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[missymarie1014]

Thanks for the respopnse genomon! I see the checkbox for CompactOnClose and that will serve my needs I believe. It works fine. However another question came to mind. If the database is being used by 2 people concurrently, does it wait for the final user to exit before compacting, and, if not, would the compact on close option be a potential problem with respect to data integrity if data is being written at the same time that the first user has exited and the database might be compacting?

 

[Remou]

Compact on close is generally not so useful for multi-user databases because it is the back-end (multi-user database should always be split) that need to be compacted. I would go with Genomon's suggestion of using script, I think. Here is a link:

http://www.mvps.org/access/_vti_bin/shtml.exe/search.htm

 

[mp9]

Or try the Handy Access Launcher

http://www22.brinkster.com/accessory/resources/001.shtml

 

[missinglinq]

Also, it's a really good idea to copy your file before doing a C & R. Compacting can actually cause data corruption.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!