the High Seas of Information Technology

Schedule compacts to run with Windows XP Scheduler (VBS)

Below is a sample VBS script to run an Access procedure, including logging.
You can point a scheduled task to this script to run it automatically.
The code should be saved as plain text in a file with a .vbs extension
download code   [verify download]

On Error Resume Next
Call Do_Compacts

Sub Do_Compacts()
    Write_Log (vbNewLine & CStr(Now()) & vbNewLine & "Begin scheduled task: compact and repair databases ...")
ret = 1
Set objAccess = CreateObject("Access.Application")
If Not objAccess Is Nothing Then
With objAccess
.OpenCurrentDatabase "\\Corpfs\acctg\DataSources_General\tp_CompactDB.mdb", False
ret = .Run ("MyCompactMyDatabases")
End With
Write_Log (vbNewLine & CStr(Now()) & vbNewLine & "End compact and repair databases.")
End If

    'Exit code - close Access
    If Not objAccess Is Nothing Then
With objAccess
End With
End If

End Sub

Sub Write_Log(ByRef arg)

fPath = "C:\myTemp\Scheduled\log.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fPath) Then
Set f = objFSO.GetFile(fPath)
If Clng(f.Size) > 8000000 Then
Exit Sub '//Runaway error log
Set ts = objFSO.OpenTextFile(fPath, 8, True, -2)
ts.WriteLine arg
End If
End If
Set ts = Nothing
Set objFSO = Nothing
End Sub

Note: for a one off you can also point your scheduled task right to your utility database to open it - an autoexec routine would run any macros you wish to run automatically (I think in this case I'd use the autoexec to call whatever other procedures need to run, and then close/quit the database when finished).

I email my log to myself each day to get a report of all the schedules tasks I've run this way. I use either CDO or the SafeSendMail routine of Wayne Phillips.

last modified: 28-Jan-2015
Copyright © 2015