Introduction.
We had an extensive discussion on the following database backup topics earlier:
2. Repairing Compacting Databases from a dedicated database with VBA Programs.
Besides the above, we already have an option in Access to Repair and Compact the current Database, while closing it.
The first topic 'Database Daily Backup' procedure runs from within the database when it is open for the first time in a day. Subsequent opening/closing events will not repeat the backup process on the same day.
The second procedure is run from within a dedicated database to Compact and Repair several databases given in a list, one by one.
The new method that we are going to try out is the VB Script, saved in textile, and run from desktop Shortcuts, for backup and Restore operations, manually. It is not an automated procedure, as far as invoking the program is concerned. You can run the code manually by double-clicking on the shortcut, whenever you think it is time to take a backup of your database.
Restore the corrupted database from its earlier backup(s), using another desktop shortcut, when you find the current database lost its consistency.
These programs are in VB Script, a light version of Microsoft Visual Basic, mostly used on web pages, and you will not have any difficulty in understanding the Code if you have some familiarity with the VBA language.
Create a Notepad text file on the Desktop.
Rename the desktop shortcut name to CreateBackup.vbs. The shortcut name extension vbs indicates that it is a Visual Basic Script file.
Right-click on the shortcut and select Edit from the displayed menu.
When the text file opens in Notepad, Copy and Paste the code given below into the File.
Select the Save option from the File Menu to save the Code, and select Exit to close the File.
The CreateBackup VB Script:
Call CreateBackup()
Sub CreateBackup()
'======================================================
'Desk-top Shortcut Name: CreateBackup.vbs
'Language : VBScript - Creates File Backup
'Remark : Run from Desktop Shortcut
' : Edit Backup Path
'======================================================
Dim s, t, p, a
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Backup is taken in the File Folder itself
' with Day and Month added (-dd-mm) to the File Name.
' Example: D:\AccTest\Testdb-16-07.accdb
p = "D:\AccTest\NorthWind.accdb" 'Edit
s = InputBox("File PathName:","CreateBackup()" , p)
If objFSO.FileExists(s) Then
'File Name changes appending with -dd-mm (-day-month) values.
a = Left(Now(), 5)
t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))
'Create the File backup
a = objFSO.CopyFile(s, t, True)
MsgBox "Backup Successful!" & vbCr & vbCr & "Source: " & s & vbCr & "Backup: " & t,vbInformation,"CreateBackup()"
Else
MsgBox "Source PathName: " & s & vbCr & "Not Found, Program Aborted!",vbCritical,"CreateBackup()"
End If
Set objFSO = Nothing
End Sub
Database CreateBackup() Code Line-By-Line.
The Backup procedure is very simple, let us go through it. The first statement Call CreateBackup() runs the Subroutine given below. This approach is necessary because when you right-click on the desktop shortcut and select Open or double-click on it, the code written within a Subroutine or Function will be ignored by the script running procedure. An explicit call of the subroutine is required to start executing the VBScript enveloped within a subroutine structure.
Note: You may write the Script in a Notepad Text File with vbs extension, without enveloping the code within the Subroutine/Function structure. It will execute the VBScript straight through what is written in the Text File with the vbs file extension when you double-click on the desktop shortcut.
The Function classification is acceptable only if you omit the return value part after the function name like:
Function CreateBackup() as Integer - not acceptable
Function CreateBackup() - acceptable
The required variables are declared at the beginning of the subroutine. The Variable Type declarations, like db_Pathname As String, are not allowed in VBScript. It can be compared with the VBA Variant Data type. The variable Type is changed based on the first value type assigned to it. If you are using VBScript within a VBA Subroutine/Function you can declare variables as we do normally in VBA.
Set objFSO = CreateObject("Scripting.FileSystemObject")The above statement creates a File System Object and assigns it to the objFSO Variable.
The variable p is initialized with a pathname that will appear as the default database pathname in the Inputbox() function prompt. If you are using this Desktop Shortcut to backup the same database every time, then set this variable to the required file pathname otherwise change the database pathname appearing in the InputBox() function TextBox to use the required database pathname for backup.
If objFSO.FileExists(s) Then
The FileExists() method of the objFSO script object checks whether the source file pathname given as the parameter exists or not. If it is found, then the backup file name is prepared by adding the current Day and Month values, in -dd-mm format (example: D:\AccTest\NorthWind-16-07.accdb) by the following statements:
a = Left(Now(), 5)
t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))
The following statement calls the CopyFile() method of the objFSO object and creates a copy of the Source File with the new name in the same folder:
a = objFSO.CopyFile(s, t, True)
The first parameter, s is the source file pathname, and the second parameter t is the target file pathname. The last parameter True indicates that if the target file exists, then overwrite it without warning. After the copy operation, a message is displayed announcing the successful completion of the backup operation.
If the source pathname is not found then the program will be aborted with a critical message.
Note: If you take more than one backup of the same file on a particular day in the same folder, then the earlier backup file will be overwritten without warning because the third parameter setting is True.
Database RestoreFile() VBScript Code.
Call RestoreFile()
Sub RestoreFile()
'================================================
'Language : VBScript
'Desk-top Shortcut Name: RestoreFile.vbs
'Remarks : Restore File from Backup
'================================================
Dim db_Current
Dim db_Save
Dim db_Backup
Dim f_bkSource,f_Current, f_Save
Dim objFSO
'The following three demo lines can be replaced
'in the InputBox statement, to Input
'File Pathnames directly.
db_Current = "D:\AccTest\NorthWind.Accdb" 'The file needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb" 'save the [dbReplace] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore from this Backup File
f_bkSource = InputBox("Backup File PathName:","Restore()",db_Backup)
f_Current = InputBox("Restore File PathName:","Restore()",db_Current)
f_save = InputBox("Save Current before replace:","Restore()",db_save)
'Create File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
a = objFSO.CopyFile(f_Current, f_save, True)
a = objFSO.DeleteFile(f_current) 'delete original file after copying with a new name
End If
'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then
a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.
If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
Else
MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If
Else
MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If
Set objFSO = Nothing
End Sub
RestoreFile() Code Line-by-Line.
The first three variables: db_Current, db_Save, and db_Backup will be initialized with the current database pathname (the file that needs replacement from backup), and before replacing it is saved with the modified pathname xxxxxx-save.accdb, and the backup file pathname respectively.
Next three variables: f_bkSource, f_Current, and f_Save will accept the pathnames given by the user for the above values. objFSO will be initialized with the File System Object.
db_Current = "D:\AccTest\NorthWind.Accdb" ' db_Current needs replacement db_Save = "D:\AccTest\NorthWind-Save.Accdb" 'save the [db_current] file with a new name db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore file from this Backup database
The first three variables are initialized with the sample database pathnames as above which will appear in the InputBox() function Text Box as default Pathnames. If these are the files involved in the RestoreFile() program's input values, then you can press Enter Key to accept them as it is, or modify them as per your requirements.
Set objFSO = CreateObject("Scripting.FileSystemObject") After the InputBox functions are run the above statement creates the File System Object and assigns it to the objFSO object.
If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
a = objFSO.CopyFile(f_Current, f_save, True)
a = objFSO.DeleteFile(f_current) 'delete the original after copying with a new name
End IfThe above code segment checks whether the current file name given for replacement exists or not. If found, then it is copied with the modified name for safe keep. If the file doesn't exist, then bypass the above code segment. There is no status value returned in variable a, indicating the copy operation's success or failure. This Syntax simply allows us to use parenthesis around the function parameter values. The following statement is equally valid.
objFSO.CopyFile f_Current, f_save, True
Next, the corrupt file is deleted using the DeleteFile() method of the objFSO Object.
The following code segment runs a validation check with the FileExists() method on the Backup database file, if found, then makes a copy of it with the original database name, overwriting it, if already exists, but we have deleted it in the earlier operation:
'Check the Backup file Exists or Not If objFSO.FileExists(f_bksource) then a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup. If objFSO.FileExists(f_Current) then 'check restore operation was successful or not. MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()" Else MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If Else MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If
After the restore operation, it runs a check for the original file pathname, to ensure that the restore operation was successful and displays a message to that effect. If something went wrong with the copy operation, then a critical message is displayed.
If the backup filename itself is not found, then a critical message is displayed and the objFSO object is released from memory before the program ends.
Tip: You can back up and restore any type of file besides databases.
