Automatically Running a Compact and Repair 2

[hsp7777]

Greetings,

I work with a rather large Access database at my place of employment and we have corrupt records from time to time, forcing me to run a Compact and Repair several times a week on the source database. At any rate, I was wondering if there is some type of scheduling tool which would allow me to automatically run a Compact and Repair on a nightly basis (such as 3:00 am)? I wasn't sure what type of software is out there, so I'm certainly open to suggestions.

Thanks in advance!

 

[68790nuiihui]

We have several ac97 db's that require compacting and repairing frequently, and also need to have macro's run inside them. i wrote the following batch script to do it all for us. you then just add the script to your windows scheduled tasks.

put the full unc paths to all your ac97 db's in a text file in the same directory as this script, enclosing each path in quotes.

if you have any macro's that need running, drag them from access into the same folder. they will get a (hidden) .mam suffix and will be run by this script.

you'll also note that the following script runs Clickok.ahk - this is an AutoHotKey (freeware - find it on Google) script that waits for an irritating ms access dialogue to appear and clicks the ok button...

Clickok.ahk contains the following:

; WAIT FOR THE "SUCESSFULLY..." DIALOGUE TO APPEAR
WinWait, Microsoft Access, Successfully
; MAKE THE DIALOGUE THE ACTIVE WINDOW
IfWinNotActive, Microsoft Access, Successfully, WinActivate, Microsoft Access, Successfully
; CLICK OK
MouseClick, left, 184, 101



The batch file contains the following:

@ECHO OFF
CD /D R:\@APPS\WESCODCSFIX
TITLE ACCESS97 DATABASE FIXES
IF EXIST "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" GOTO BEGIN

ECHO ACCESS97 MUST BE INSTALLED ON THIS MACHINE.
PAUSE
GOTO END

:BEGIN

REM RUN MAMS
FOR %%A IN (*.MAM) DO %%A

REM CALL REPAIR ROUTINE FOR ALL AC97 DB'S IN 97MDB.TXT
FOR /F %%A IN (97MDB.TXT) DO CALL :REPAIR %%A
GOTO END

REM FIX SPECIFIED DATABASE
:REPAIR
START "" "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" %1 /COMPACT /REPAIR
ClickOK.ahk

:END
EXIT





Hope this helps...

John

 

[hsp7777]

Thanks. I appreciate it.

 

[hsp7777]

By the way, I'm assuming this code will also work on Access 2000 databases. Am I correct?

 

[68790nuiihui]

Acc2k is in fact simpler as it doesnt give the irritating "Successfully repaired [yourdb.mdb]" dialogue, so you can strip out the reference to the clickok.ahk script from the batch script and you wont need to install AutoHotKey. the path to the access program is also slightly different - change it to "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE11\MSACCESS.EXE" ("office11" folder instead of "office").

Also, forgot to mention - change the path in the second line of the batch script to the path that your batch script is actually in.

drop me an email if you get stuck...

 

[hsp7777]

Thanks so much. (By the way, my path to the MSACCESS.EXE file was also "Office", even though I am running Access 2000).

I do have one additional question, however. What does the "/D" parameter in the following line of code do:

CD /D R:\@APPS\WESCODCSFIX

Thanks again!

 

[PHV]

In a console window type the following command:
cd /?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hsp7777]

By the way, do you know if there is a way to bypass the Compact and Repair code if the database is open? For instance, if I have two databases listed in the text file and one of them is open, I receive the following error message:

"You attempted to open a database that is already opened exclusively by user 'Admin' on machine (machine name). Try again when the database is available."

Is there a way to first check and see if the database is open before I attempt to do a Compact and Repair?

Thanks so much!

 

[PHV]

Check for the existence of the LDB file.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hsp7777]

Thanks. By the way, do you know if there is there a way to write out a log file to indicate that the Compact and Repair was successful?

 

[68790nuiihui]

yes - instead of using
START "" "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" %1 /COMPACT /REPAIR

use something like this:

CALL "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" %1 /COMPACT /REPAIR
IF NOT %ERRORLEVEL%==0 GOTO FAIL
ECHO Compact and Repair on %DATE% at %TIME% was successful>>ComactAndRepair.log
GOTO END

:FAIL
ECHO Compact and Repair on %DATE% at %TIME% was NOT successful>>ComactAndRepair.log


When you CALL a program or procedure, it generates an exit code (sometimes called error level) upon termination. As a rule, an exit code of 0 means that the the process was sucessful, and a non-0 exit code means that the process was not successful.

 

[hsp7777]

Thanks so much! I appreciate it!

 

[hsp7777]

I have one other question. How would I run a Compact and Repair for a database that has a space in the name? The name of the database in question is "J:\QC-App 1.0.mdb". I have tried using the following in the "2000MDB.txt" file:

"J:\QC-App%1.0.mdb"
"J:\QC-App%%1.0.mdb"
"'J:\QC-App 1.0.mdb'"
"J:\'QC-App 1.0.mdb'"

None of the above combinations work, however. Any suggestions (aside from renaming the database)?

Thanks in advance!

 

[68790nuiihui]

J:\QC-App 1.0.mdb" in the text file should work...
If not, try opening up a command prompt and entering the command that would be run in the batch script, so...
START "" "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" %1 /COMPACT /REPAIR
would become
START "" "C:\Program Files\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" [whatever is in your text file] /COMPACT /REPAIR
You may need to play about with the positioning of the quotes...
The simplest way though would probably be to rename your database...

 

[hsp7777]

Greetings,

I am attempting to use the code below (in a batch file) to write out text to a log file, but it writes out that the Compact and Repair was successful even if it does not run (due to the Database being open by another user). Is there anything obvious in my "IF" statement that I am missing?

Thanks in advance!

hsp7777

Code:

REM COMPACT AND REPAIR THE SPECIFIED DATABASE
:REPAIR
CALL "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" "J:\Northwind.mdb" /COMPACT /REPAIR
IF NOT %ERRORLEVEL%==0 GOTO FAIL
ECHO Compact and Repair on %DATE% at %TIME% was successful>>CompactAndRepair.log
GOTO END

:FAIL
ECHO Compact and Repair on %DATE% at %TIME% was NOT successful>>CompactAndRepair.log

:END
EXIT

 

[PHV]

IF ERRORLEVEL 1 GOTO FAIL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dilettante]

Isn't this really a job for JetComp.exe?

 

[hsp7777]

I added "IF ERRORLEVEL 1 GOTO FAIL", but this still writes out to the log file that the Compact and Repair was successful, even if the database is open and the Compact and Repair doesn't complete. It almost appears that no error occurs even if the Compact and Repair was not successful. Does this makes sense?

Code:
@ECHO OFF
CD /D C:\ACCESS
TITLE ACCESS2000 DATABASE FIXES
IF EXIST "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" GOTO BEGIN

PAUSE
GOTO END

:BEGIN
:REPAIR
START "" "%PROGRAMFILES%\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE" "J:\Northwind.mdb" /COMPACT /REPAIR
IF ERRORLEVEL 1 GOTO FAIL
ECHO Compact and Repair on %DATE% at %TIME% was successful>>CompactAndRepair.log
GOTO END

:FAIL
ECHO Compact and Repair on %DATE% at %TIME% was NOT successful>>CompactAndRepair.log

:END
EXIT