SandboxM

Couple of days ago, my Access database (mdb) got corrupted. I kept getting the “Invalid bookmark” error whenever I ran a select against one of my tables. To make it worse, the last time I had backed up my database was 4 days ago, so in other words, in one snap I had nothing to show for my week’s worth of work. That’s when I decided to come up with an automated way of backing up my data because apparently I couldn’t trust myself to do the job regularly.
I looked around for sample VB code or a bat file that would backup my files at regular intervals. But backing up the files wouldn’t be enough, I also wanted to zip them up together so that would a) save space and b) keep them all together. I found lots of snippets that required WinZip or 7-zip (I know it’s free) but I didn’t want to rely on a specific 3rd party program. Finally I found 2 pieces of code here and here which I converted into a VBS file.

Here’s the code:

strDBName = "myAccessDB"
strFolderLocation = "C:\Databases\"
strDate = Right("00" & DatePart("M", Date),2) & Right("00" & DatePart("D", Date),2) & DatePart("YYYY", Date)
strTime = Right("00" & DatePart("H", Time),2) & Right("00" & DatePart("N", Time),2) & Right("00" & DatePart("S", Time),2)
zipfilename = strFolderLocation & strDate & "_" & strAppName & ".zip"
filename = strFolderLocation & strAppName & ".mdb"
Set fso = CreateObject("Scripting.FileSystemObject")
'Zip it only if the MDB file is found
IF fso.FileExists(filename) THEN
'If a file with the same name exists, append Time to the Zip file name.
IF fso.FileExists(zipfilename) THEN
zipfilename = strFolderLocation & strDate & strTime & "_" & strAppName & ".zip"
END IF
strZIPHeader = Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
Set tf = fso.CreateTextFile(zipfilename)
tf.Write(strZIPHeader)
tf.Close()
With CreateObject("Shell.Application")
.NameSpace(zipfilename).CopyHere(filename)
End With
wScript.Sleep 1000
Wscript.Echo(strAppName &  " zipped and saved to " & zipfilename)
ELSE
WScript.Echo("Could not find database: " & filename)
END IF

Finally for the automation part, I set up a Scheduled Task in Windows’ Task Scheduler to execute the VBS file every day at 9pm so my work is backed up a few hours after I have left the office.