[COLOR="Navy"]Sub[/COLOR] SaveBackups()
[COLOR="SeaGreen"]'VBScript code to back up specific data base files[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'Scripting.FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'Scripting.File[/COLOR]
[COLOR="Navy"]Dim[/COLOR] BackupFileName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] destFolder [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] BaseName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Days_Back_To_Delete_Old_Backups [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'VBA.Collection to hold DB Paths[/COLOR]
[COLOR="Navy"]Dim[/COLOR] d [COLOR="SeaGreen"]'//variant to use for parsing backup dates[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR] [COLOR="SeaGreen"]'//Counter[/COLOR]
[COLOR="SeaGreen"]'---------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'Preconditions[/COLOR]
[COLOR="SeaGreen"]' A Backup folder has been created and the[/COLOR]
[COLOR="SeaGreen"]' value of destFolder below has been edited[/COLOR]
[COLOR="SeaGreen"]' appropriately[/COLOR]
[COLOR="SeaGreen"]'[/COLOR]
[COLOR="SeaGreen"]' The value for Days_Back_To_Delete_Old_Backups[/COLOR]
[COLOR="SeaGreen"]' below has been set to the number of days that[/COLOR]
[COLOR="SeaGreen"]' you would like to keep backups[/COLOR]
[COLOR="SeaGreen"]'Postconditions[/COLOR]
[COLOR="SeaGreen"]' Backups with a timestamped name have been[/COLOR]
[COLOR="SeaGreen"]' saved in the backup folder, and backups[/COLOR]
[COLOR="SeaGreen"]' older than the Days_Back_To_Delete_Old_Backups[/COLOR]
[COLOR="SeaGreen"]' setting have been deleted[/COLOR]
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="SeaGreen"]'---------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'Create Objects[/COLOR]
[COLOR="Navy"]Set[/COLOR] col = [COLOR="Navy"]New[/COLOR] VBA.Collection
[COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
[COLOR="SeaGreen"]'---------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'User settings[/COLOR]
[COLOR="SeaGreen"]'Days back after which delete backups[/COLOR]
Days_Back_To_Delete_Old_Backups = 7
[COLOR="SeaGreen"]'Where to save backups[/COLOR]
destFolder = "C:\Users\xenou\Documents\DBBackups"
[COLOR="SeaGreen"]'Databases to backup[/COLOR]
col.Add "C:\Users\xenou\Documents\BackEndDB.accdb"
col.Add "C:\Users\xenou\Documents\FrontEndDB.accdb"
[COLOR="SeaGreen"]'---------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'Go[/COLOR]
[COLOR="SeaGreen"]'New Backups saved with naming convention <name>_yyyymmdd.hhnnss.<extension>[/COLOR]
[COLOR="Navy"]If[/COLOR] FSO.FolderExists(destFolder) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] col.Count
[COLOR="Navy"]If[/COLOR] FSO.FileExists(col(i)) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] f = FSO.GetFile(col(i))
BackupFileName = destFolder & "\" & FSO.GetBaseName(f.Path) & "_" & _
Format(Now, "yyyymmdd.hhnnss") & "." & FSO.GetExtensionName(f.Path)
FSO.CopyFile col(i), BackupFileName, True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="SeaGreen"]'Cleanup old backups[/COLOR]
[COLOR="Navy"]If[/COLOR] FSO.FolderExists(destFolder) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] col.Count
[COLOR="SeaGreen"]'//Check for backups with same name, excluding timestamp[/COLOR]
BaseName = FSO.GetBaseName(FSO.GetBaseName(col(i)))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] FSO.GetFolder(destFolder).Files
[COLOR="Navy"]If[/COLOR] Left(FSO.GetBaseName(f.Path), Len(FSO.GetBaseName(f.Path)) - 16) = BaseName [COLOR="Navy"]Then[/COLOR]
[COLOR="SeaGreen"]'//Parse timestamp in filename and convert to a real date[/COLOR]
d = FSO.GetBaseName(f.Path)
d = Right(d, 15)
d = DateSerial(CLng(Left(d, 4)), CLng(Mid(d, 5, 2)), CLng(Mid(d, 7, 2))) + _
TimeSerial(CLng(Mid(d, 10, 2)), CLng(Mid(d, 12, 2)), CLng(Mid(d, 14, 2)))
[COLOR="SeaGreen"]'//Delete backup if it is older than the cutoff date for saving backups[/COLOR]
[COLOR="Navy"]If[/COLOR] Now - d > Days_Back_To_Delete_Old_Backups [COLOR="Navy"]Then[/COLOR]
FSO.DeleteFile (f.Path)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR] f
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]