Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Database Backup Restore From Desktop

Introduction.

We had an extensive discussion on the following database backup topics earlier:

1. Database Daily Backup.

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.

  1. Create a Notepad text file on the Desktop.

  2. Rename the desktop shortcut name to CreateBackup.vbs. The shortcut name extension vbs indicates that it is a Visual Basic Script file.

  3. Right-click on the shortcut and select Edit from the displayed menu.

  4. When the text file opens in Notepad, Copy and Paste the code given below into the File.

  5. 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. 

Follow the same procedure for creating the VB Script file with the Notepad explained above, and create the RestoreFile.vbs Desktop Shortcut with the following VBScript code copying and pasting them into it.
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 If

 The 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.

  1. Get Disk Free Space - Windows API
  2. Access And Windows API ShowWindow
  3. ListView Control Drag Drop Sort Events.
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code