If your Database is installed on a Local Area Network (LAN) location then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept them in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deleted by mistake etc., you can always send a request to the Computer Department giving details of location, database name and safe backup date from which you would like to restore from. This may take 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 are able to complete your request.

If your Project have its own backup procedure and does it on regular basis then you don't have to go after Network backups, causing delays 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 the database is on a Network location several Users have access rights to that folder, besides your Application Users, and the database is not safe there.

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 for the first time of the day, by any one of the users.

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. The backup program should run only once a day, when the database is open for the first time of the day, by any one of the users and should prevent the program from running on subsequent shut-downs and re-opening events.

  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