LEARN MS-ACCESS TIPS AND TRICKS

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.




Srl.   Field Name  Type   Size
a.     bkupdate          Date
b.     workstation  Text  20










bkupdate	workstation
01/05/2008




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
        '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 strBatchFile
End 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.

---

Crosstab Union Queries for Charts
Union Query
Percentage on Total Query
Selecting Office Assistant
Office Assistant with Check Box Menu

Labels: msaccess tips