trypots.nethome
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
.CloseCurrentDatabase
.Quit
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
Else
Set ts = objFSO.OpenTextFile(fPath, 8, True, -2)
ts.WriteLine arg
ts.Close
End If
End If
Set ts = Nothing
Set objFSO = Nothing
End Sub
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
.CloseCurrentDatabase
.Quit
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
Else
Set ts = objFSO.OpenTextFile(fPath, 8, True, -2)
ts.WriteLine arg
ts.Close
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.