DOS - MS Access Compacting Script

_compacter.readme

----------------------------------------------
Windows Command-line MS Access DB Compact Automation
----------------------------------------------

DOS / Windows Command-line script that compacts MS Access databases.
Checks if db is open (.ldb file exists) and skips compacting that db
if it does. Can task schedule to run after-hours for maintenance.

Purpose ...

I've used MS Access to data-junction various data sources in the past
for reporting databases, as well as create various data entry systems
for various departments.

Over time, you end up with various DB's needing compacting. So, came
up with a DOS script that could get task scheduled to run after-hours.
Script checks if the db is open (looks for .ldb file). If it's open,
it skips it. Otherwise, it compacts it. Also spits out a text .log file,
so you can see what it did. Can easily add more databases to the script.

compacter.bat

:::::::::::::::::::::::::::::::::::::::
::
::  batch file goes through the MS Access db's and compacts them
::  using "compacter_sub.bat" sub-routine
::
::  VAR_ACC   = path to the MS Access .exe that does the compacting
::  VAR_LOG   = path to log file where we note each db compacted
::
:::::::::::::::::::::::::::::::::::::::
::
::  Set static variables that the main & sub routine will use

@ECHO OFF
SET VAR_ACC="C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
SET VAR_LOG=..\compacter.log

:::::::::::::::::::::::::::::::::::::::
::
:: start new log (comment this out if you just want to keep appending to one huge log file)

echo ------------------------------------------------------ > %VAR_LOG%
echo %DATE% %TIME% ... Start >> %VAR_LOG%
echo ------------------------------------------------------ >> %VAR_LOG%
echo.

:::::::::::::::::::::::::::::::::::::::
::
:: call the sub-script to compact each database
:: will only compact if the .ldb file doesn't exist, to prevent corruption
:: if someone is in it at the time.

CALL compacter_sub.bat "C:\folder\subfolder\database01"
CALL compacter_sub.bat "C:\folder\subfolder\database02"

:: etc, etc

:::::::::::::::::::::::::::::::::::::::
::
:: Add a spacer to show completion time, and separate next run from this one
:: just in case we're compiling a huge log instead of replacing old one.

echo.
echo %DATE% %TIME% ... Done!
echo.
echo ------------------------------------------------------ >> %VAR_LOG%
echo %DATE% %TIME% ... Finish >> %VAR_LOG%
echo ------------------------------------------------------ >> %VAR_LOG%

compacter_sub.bat

:::::::::::::::::::::::::::::::::::::::::::::::::::
::
:: Sub-procedure for the MS Access compacting script.
:: It takes in the %1 db name argument, and see's if its .ldb
:: file exists.  If it does, it will skip the compacting.
:: If it doesn't, then it'll compact the respective .mdb.
::
:::::::::::::::::::::::::::::::::::::::::::::::::::
::
:: syntax
::
:: CALL compacter_sub.bat %1
::
:: ...where...
::
:: %1 = path/name of db file (enclosed in quotes if there's any spaces)
::
:::::::::::::::::::::::::::::::::::::::::::::::::::
::
:: main script already set up variables for...
::
:: * VAR_ACC = MS Access exe
:: * VAR_LOG = Log file
:: * VAR_DB  = Database to compact
::
:: so, we just use those below, but set up the .mdb & .ldb names to use
::
:: %1 is normal arg, but %~1 removes quotes from arg passed...

SET VAR_LDB="%~1.ldb"
SET VAR_MDB="%~1.mdb"

:: if .ldb exists, log that we're skipping the compact
:: otherwise, compact it and append log that we did
:: "/compact" command-line switch tells it to just compact instead of open

IF EXIST %VAR_LDB% (
echo %DATE% %TIME% ...skipping..... %VAR_MDB% >> %VAR_LOG%
echo %DATE% %TIME% ...skipping..... %VAR_MDB%
) ELSE (
echo %DATE% %TIME% ...compacting... %VAR_MDB% >> %VAR_LOG%
echo %DATE% %TIME% ...compacting... %VAR_MDB%
%VAR_ACC% %VAR_MDB% /compact
)