Schedule job to selectively pick DBs in a Folder to compact/repair them

vivekr

New member
Local time
Tomorrow, 02:30
Joined
May 9, 2011
Messages
4
Hi,
I'm not familiar much with Access so I don't know if it's possible, but would appreciate any input regarding the following:

Multiple macros from different databases (all located within one folder or sub-folders) are run daily on 6 different schedulers which really hogs their performance as well as takes up a lot of free space. So periodically, we end up manually compacting and repairing large databases to free up space; a tedious process as one can imagine. I was wondering if it's possible to set up a job (either through VBA module or any other way) to run once a week, where I have the code to look at all databases present in a particular folder (as well as its sub-folders); pick DBs with size greater than 200KB, and compact & repair them.
If possible, what would be the best solution to this problem? Please provide complete info on how to do so..Thanks in advance.

Vick
 
Do you know of the 'Compact on Close' option that does automatically?
 
Yes, and I have started implementing that option for newly created databases from this month onwards. But what to do for the previously created DBs (about a few hundreds of them)? I don't want to manually open all those DBs to select the option of 'Compact on Close'...
 
Hi,

As I understand it, for the database to compact and repair successfully, no users should be in the database at that time, the problem you will encounter is if you have more than one user that accesses that database, and anyone user exits, the compact and repair will run, but will not be able to compact and repair successfully and will could corrupt your database.

You need to be 100% sure that no one else is in the database on closing the database, so you may need to also have a routine that checks for other users being in the database and if there are, kick them out before the compact and repair process runs.

Just my observation, there may be experts on here that know more than me on the subject.

John
 
yeah, that would be a good thing to include where I check if any databases are open before running the module to compact & repair all DBs located in a folder. But I mainly plan to automate the job to run every Friday night, when no users are in the office and when no jobs are scheduled to run then. But still I believe this would be a good thing to incorporate since compacting and repairing 100s of DBs might take a lot of time and might go into Saturday morning which has a few scheduled jobs. Thanks for the tip mate.

Meanwhile, can I get some advise on how to handle the above issue? Any suggestions???
 
Hi,

The following posts on this forum under VBA Modules may be of assistance to you:

03-23-2011 01:54 AM posted by ria arora

06-22-2007 06:35 AM posted by cpremo

If do a search you may find others that have covered the subject of Compacting and repairing.

John.
 
Thanx, but I didn't find those posts very useful..How do I create 1 DB module to compact & repair other multiple databases located in one folder??
 

Users who are viewing this thread

Back
Top Bottom