Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Scheduling a Compact/Repair

    EDIT: After looking over what I wrote, I figured it would be better to cut out everything that was unnecessary. . .

    Below this edit (In post #2 actually) is the original.

    I'm having trouble scheduling an automated compact/repair. We're on a networked setup where each user logs into a Forms-based Access frontend to modify data on a "master database" on our server (located on Drive D for the purposes of this post). A custom Access Workgroup is set up and requires each user to log in with their own username/password to the database.

    I'm using Access 2000 on Windows XP Professional. My user is a member of the computer's local Administrator's group.

    When I try and run the command straight from the command line or from within a BAT file, it works perfectly, but when I set up a scheduled task, the MSACCESS.EXE process appears to hang.

    The command I'm using is as follows:

    "C:\Program Files\Microsoft Office\Office\MSAccess.exe" "D:\AccessDB\Database.mdb" /wrkgrp "D:\AccessDB\System\System.mdw" /compact /user <User Name> /pwd <Password>

    Is there a way to get access to automatically compact/repair this database on a schedule?
    Last edited by Rawb; 08-11-09 at 12:43.

  2. #2
    Join Date
    Aug 2009
    Posts
    4
    THIS IS MY ORIGINAL POST FOR INTERESTED PARTIES!
    I'm trying to automate a compact/repair on several access databases and I can't get it to work. The process starts just fine, but it appears that Access just hangs on start instead of running the compact/repair.

    I'm using Access 2000 on a Windows XP Pro computer. The user assigned to run the Scheduled task is a member of the Administrators group and has full access to the system. The Access Database is set up to require a username/password before it will open and I'm specifying them through commandline switches

    I can manually run the command to do the compact/repair from the command line (and run it from a BAT file) just fine, but when I try to run it from Scheduled Tasks, the msaccess.exe process appears to hang.

    Although access is actually being run from inside a BAT script file, I know everything else in the script is working just fine (as I said above, the scripts works PERFECTLY when manually run). Below is slightly pared down version of my script (some comments removed, etc.):

    Code:
    echo off
    cls
    
    set SCRIPT_PATH=C:\Program Files\Backup Scripts\Access Backup
    set SECURE_PATH=C:\Program Files\Backup Scripts\Secure
    set MSO_PATH=C:\Program Files\Microsoft Office\Office
    set MDB_PATH=D:\AccessDB
    set BAK_PATH=C:\Backups
    set WRKGRP=D:\AccessDB\System\System.mdw
    
    call "%SECURE_PATH%\msaccess_cmd_switches.bat"
    
    echo %date%| sed -e "s/^... //gi" -e "s/\([0-9][0-9]\)\/\([0-9][0-9]\)\/\([0-9][0-9][0-9][0-9]\)/\3-\1-\2/gi" -e "s/^/set TMP_DATE\=/gi" -e "s/[\t ]*$//gi"> "%TEMP%\set_date.bat"
    call "%TEMP%\set_date.bat"
    
    echo [%TMP_DATE% %TIME%] Beginning backup >> "%SCRIPT_PATH%\Logs\log.txt"
    
    if NOT exist "%MDB_PATH%\Database1.ldb" (
      echo [%TMP_DATE% %TIME%]   Repairing file 1. . .>> "%SCRIPT_PATH%\Logs\log.txt"
      "%MSO_PATH%\MSAccess.exe" "%MDB_PATH%\Database.mdb" /wrkgrp "%WRKGRP%" /compact %CMD_SWITCHES%
    
      echo [%TMP_DATE% %TIME%]   Repairing file 2. . .>> "%SCRIPT_PATH%\Logs\log.txt"
      "%MSO_PATH%\MSAccess.exe" "%MDB_PATH%\Database2.mdb" /wrkgrp "%WRKGRP%" /compact %CMD_SWITCHES%
    
      echo [%TMP_DATE% %TIME%]   Repairing file 3>> "%SCRIPT_PATH%\Logs\log.txt"
      "%MSO_PATH%\MSAccess.exe" "%MDB_PATH%\Database3.mdb" /wrkgrp "%WRKGRP%" /compact %CMD_SWITCHES%
    ) else (
      echo [%TMP_DATE% %TIME%]   Aborting! The following users are still connected:>> "%SCRIPT_PATH%\Logs\log.txt"
      cut -c 33-64,97-128,161-192,225-256,289-320,353-384,417-448,481-512,545-576,609-640 G:\AccessDB\Database.ldb| sed -e "s/\x0  */, /g" -e "s/^/                             /g" -e "s/, $//g">> "%SCRIPT_PATH%\Logs\log.txt"
      goto error_state1
    )
    echo [%TMP_DATE% %TIME%]   File repair completed.>> "%SCRIPT_PATH%\Logs\log.txt"
    
    echo [%TMP_DATE% %TIME%]   Compressing files into ZIP archive "%BAK_PATH%\%TMP_DATE%.zip". . .>> "%SCRIPT_PATH%\Logs\log.txt"
    zip -9jq "%BAK_PATH%\%TMP_DATE%.zip" "%MDB_PATH%\Database.mdb" "%MDB_PATH%\Database2.mdb" "%MDB_PATH%\Database3.mdb"
    echo [%TMP_DATE% %TIME%]   Compression completed. . .>> "%SCRIPT_PATH%\Logs\log.txt"
    goto no_error
    
    :error_state1
    echo [%TMP_DATE% %TIME%] Backup aborted, files still in use!>> "%SCRIPT_PATH%\Logs\log.txt"
    echo.>> "%SCRIPT_PATH%\Logs\log.txt"
    goto exit_script
    
    :no_error
    echo [%TMP_DATE% %TIME%] Backup completed successfully!>> "%SCRIPT_PATH%\Logs\log.txt"
    echo.>> "%SCRIPT_PATH%\Logs\log.txt"
    
    :exit_script
    set CMD_SWITCHES=
    set SCRIPT_PATH=
    set SECURE_PATH=
    set TMP_DATE=
    set MSO_PATH=
    set MDB_PATH=
    set BAK_PATH=
    
    exit
    The Secure folder is merely a folder that only myself and the Administrators group have access too. The only thing in the "msaccess_cmd_switches.bat" script is a variable with my username and password to log onto the database.

    Does anyone know how I could get this working?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •