automatic compact & repair

[travismp]

I have a large DB that I want to compact & repair daily. I have the option set to "compact on close"

is there a way to write a batch file that will open the program, then close it again so it will do a compact?

if not a batch file then any type of program that might be able to handle this?

 

[travismp]

also can you write a batch file that will close all open programs?

 

[travismp]

Nevermind all... I found a solid work around. If you need to manually run a compact and repair daily here are the steps:


1.) set up a scheduled task (start - control panels - scheduled tasks) to open the DB.
2.) Have the option set to "compact on close"
3.) Write a batch file (start - programs - accessories - notepad)

@echo off
taskkill /im MSACCESS.EXE /f

4.) Save the file
5.) Rename the file to *.bat instead of *.txt
6.) Schedule the close batch file to run 5 minutes later.

Pretty easy stuff.

 

[travismp]

nope does not work. sorry. This will kill the app, but it does not compact on close. Technically it is Access is being killed not closed. I will keep trying. This will work if I can come up with a command for close istead of kill in batch file.

 

[The_Doc_Man]

There is a command line option /X that allows you to name a macro when you open the DB from a batch file.

If you compose the macro to do the compact & repair followed by a Quit, and if you put the batch file running that macro via /X to run when nobody is on your database, that is fairly close to automagic in effect.

 

[travismp]

Doc that sounds great. Do you have any examples that i use as a guide? I will not know how to do this unless I see a sample.

1.) Create a batch file that opens the DB.
2.) Create a macro in the DB that does a compact & repair, then close DB

How does a DB know if it was opened by a batch file? Where does the /X come into play?

Thanks.

 

[The_Doc_Man]

You would have to search the forum for examples.

As to how this works, build the macro first. Make it do the compact and repair without exiting. OK, add the QUIT command as the last element of the macro. Test that. (This is the aw-shucks part because Access will exit when you test it...)

OK, you are good for part 1.

Now part 2 - write a .BAT file that runs access from where it is installed, pointing to the database you want to do this to, with /X and the macro name.

Part 3 - In Windows task scheduler, have it run the .BAT file. Windows scheduler knows what to do with batch files when it sees that file type, so it won't be TOO stupid - i.e. more stupid than any Windows function ever is.

The task scheduler will do the scheduling part. (That almost sounds like a "Well, DUH" statement, doesn't it?) The batch job supplies the pointer to the database and the macro. And the macro does the compact, repair, and quit for you.

A DB does not need to know how it was opened. In this context, it should never WANT to know.

 

[travismp]

Thanks

here is my batch file:

"C:\Program Files\Microsoft Office\OFFICE\MSACCESS.EXE" "F:\MAIN.mdb" /x QUIT

Then I set the DB options to Compact on Close. I could not find the actual settings in the MACRO to compact & repair. So I set the mac to quit the db. It does the compact on close so it is good enough

thanks for the help.

 

[RuralGuy]

Have you tried:
"C:\Program Files\Microsoft Office\OFFICE\MSACCESS.EXE" "F:\MAIN.mdb" /compact

 

[travismp]

Rural that worked great without adding a macro. nice thanks. It closes after the compact which is great, but why does it do that?

 

[RuralGuy]

By design. It is a way to compact a db without actually opening it and leaving it open.