If your Database is installed on a Network then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deletion by mistake etc., you can always send a request to the Computer Department giving details of location, filename and safe backup date from which you would like to restore from. This may take a few hours to few days to get it done because the backup tapes or other mediums must be transported back from its storage location before they is able to complete your request.

If your Project have its own backup procedure and does it on a regular basis you don't have to go after others with requests and resultant delay in restoring the Database. This will also ensure that your Application's down time is very minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but when it comes to the safety of the Database File itself this will not work. When it is on a Network several Users may be accessing the shared folder besides the Application Users and a mistake can happen to anybody.

You cannot make a Database Read-Only under Network Security to protect it from inadvertent loss. If you do that then you cannot work with the Database.

As a precautionary measure we can take a quick Daily Backup of the Database File, from Server to Local Disk or vice versa with a DOS Batch File. The Backup should run immediately on opening the Database, by any one of the Users, for the first time of the Day.

We can do this with a simple VBA Routine to create a DOS Batch File in the database folder and run it from there to make a copy to local drive. We need a small table with a single record to keep track of the Backup event.

1. Create a Table with the following structure and save it with the name Bkup_Ctrl and add a single record with a date earlier than today in the bkupdate Field. Leave the other field blank.

    

   Table : Bkup_Ctrl Structure

   Srl.	Field Name	Type	Size
   1.	bkupdate	Date/Time
   2.	workstation	Text	20

    

   Table : Bkup_Ctrl

   bkupdate	workstation
   01/05/2008	PC1-1234

    

2. Copy and Paste the following Code into a Global Module of your Project and save the Module.

    

   Public Function SysBackup()
   '------------------------------------------------------'
   'Author: a.p.r. pillai
   'Date  : 01-Apr-2008
   'URL   : http://www.msaccesstips.com
   'All Rights Resersed by msaccesstips.com
   '------------------------------------------------------
   Dim dbPathName, j As Long, t As Date
   Dim bkupdate, strBatchFlle As String, qot As String
   
   On Error GoTo sysBackup_Err
   
   qot = Chr$(34)
   bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
   ' bkupdate+7 > date() for weekly backup
   If bkupdate = Date Or bkupdate = 0 Then
       Exit Function
   End If
   
   dbPathName = CurrentDb.Name
   j = InStrRev(dbPathName, "\ ")
   
   If j > 0 Then
       strBatchFlle = Left(dbPathName, j)
       strBatchFile = strBatchFlle & "bakup.bat"
       Open strBatchFile For Output As #1
           Print #1, "@Echo off"
           Print #1, "Echo :------------------------- "
           Print #1, "Echo : " & dbPathName
           Print #1, "Echo Daily Backup to C:\ "
           Print #1, "Echo :------------------------- "
           Print #1, "Echo : "
           Print #1, "Echo :Please wait... "
           Print #1, "Echo : "
           Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\ " & qot
   'add lines here for Back-end database or for other Files
       Close #1
   
   'Copy file
       Call Shell(strBatchFile, vbNormalFocus)
       t = Timer
       Do While Timer <= t + 10
          DoEvents 'wait for 10 seconds
       Loop
   
       DoCmd.SetWarnings False
       DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"        DoCmd.SetWarnings True
   
       'Kill strBatchFileEnd If
   
   sysBackup_Exit:
   Exit Function
   
   sysBackup_Err:
   MsgBox Err.Description, , "sysBackup()"
   Resume sysBackup_Exit
   End Function
   

 

3. Add the following line of Code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other Form that opens immediately after loading the Database.

 

SysBackup

At the beginning of the SysBackup() routine, the Program reads the last backup date from the Bkup_Ctrl Table and checks whether it matches today's date, if it does then stops the program from proceeding further. By replacing the expression bkupdate = date() with the expression bkupdate+7 > date() you can schedule the Backup to run on weekly intervals on a particular Day of the Week.

The VBA Routine creates a DOS Batch File in the same folder of your Database and Runs it. The DOS Copy command is used for copying the Database File to the User's local drive. Even though a VBA FileCopy() Function is available, this will not work from within to make a copy of the same Database.

You may modify the line to change the Target Location C:\ to a different one, if needed.

A delay loop is built into the routine to slow down the program for about 10 seconds, to give enough time for the DOS Command to complete copying the Database. Normally, the VBA Code execution will not wait for the DOS Command to complete before executing the next statement. This will also prevent the User from start working with the Database before the copy operation is complete. You may increase or decrease this value depending on the size of the Database, or after trial runs of the procedure to determine approximate time it takes to copy.

The control table's bkupdate Field is updated with current date immediately after completion of the Copy operation and this will prevent further running of this procedure in subsequent Sessions on the same day. If your Application has a Back-End Database then install this table in there and link it to the Front-End. If your Application is on a Network and shared by several Users then by referring the workstation field you can easily find out which machine have the latest Backup Copy.

The Kill strBatchFile statement (if enabled) will delete the DOS Batch File after the backup operation. The delay loop protects the DOS Batch File from this statement for about 10 seconds. Enable this line if you don't want the batch file to remain in the database folder.

You can create a DOS Batch file manually, with a Text Editor like Windows Notepad, install it in the Database Folder and run it from Code or Macro. You may define the Source and Target Locations manually for the Copy command.

The advantage of the above Code is portability and convenience. You can copy the Code and the bkup_ctrl Table into your other Projects and run it without much change or worrying about the Source or Target Location addresses of the Database.

Download Demo Database