Updated Database Backup

Here’s an updated way for you to add backup capability to an Access database.

Access Archon #151

 


Introduction

A few years ago, in Access Archon #115, I described a way to create backups of the current database, using several objects that you could import into a database, to make it easy to regularly save backup copies of the database.  Over the years, I streamlined this method, paring it down to just a table, a module, and a set of macros to run the backup functions.  Most recently, I updated the backup procedures for my upcoming book on Office 2007 (for this book, they will be part of an Access add-in that also includes the table and query field listing procedures that were featured in Access Archon #150).


Modifications to Backup Procedures

That book won’t be out for a while – it should be published around the time of the release of Office 2007 – but in the meanwhile I have prepared an Updated Backup database, with code for backing up both front-end (or standalone) databases and back-end databases.  Unlike the older version of the backup code, there is no requirement for any specific syntax or location for the back end database; the path to the back end is picked up from the Current property of a linked table.

Another change:  Since Access 2007 has a new database format, and a new extension (.accdb instead of .mdb), I had to modify the code to deal with either type of extension.  The updated backup code will work with databases in formats from Access 2000 to Access 2007 (beta).

As with the earlier versions of the Backup technique, the dates and incrementing numbers for the backup databases are stored in a table, now called zstblBackupInfo (the zs prefix indicates that this is a system table).  I added two extra fields, so that backup numbers could be incremented separately for the front-end and back-end databases.  All the code is in the basBackup module; the two procedures that do the backups are called from the macros mcrBackupFrontEnd and mcrBackupBackEnd.


Using the Backup Procedures

If you want to add backup capability to an Access database, all you have to do is import zstblBackupInfo, basBackup, mcrBackupFrontEnd and (for databases with linked back ends), mcrBackupBackEnd into the database.  Check that you have references to the DAO and Scripting Runtime libraries, compile the code, and you are ready to go.  To back up the current database, run mcrBackupFrontEnd; to back up the back end, run mcrBackupBackEnd.  The proposed save name for the database backup copy is presented in an InputBox, as shown in Figure A; you can edit the name as desired, say to indicate a milestone achieved.

Figure A.  An InputBox that lets you modify the database copy name


VBA Code

The two procedures that back up a front-end (or standalone) database, or a back-end database, and the procedures that create incrementing numbers for them, are listed below:

Public Function BackupFrontEnd()

 

On Error GoTo ErrorHandler

  

   Set dbs = CurrentDb

   Set tdfs = dbs.TableDefs

   ‘Components of the FileSystemObject object library are used
   ‘to work with files

   Set fso = CreateObject(“Scripting.FileSystemObject”)

   strCurrentDB = Application.CurrentProject.Name

   Debug.Print “Current db: ” & strCurrentDB

   intExtPosition = InStr(strCurrentDB, “.”)

   strExtension = Mid(strCurrentDB, intExtPosition)

   intExtLength = Len(strExtension)

  

   ‘Create backup path string (Backups folder under database folder)

   strBackupPath = Application.CurrentProject.Path & “Backups”

   Debug.Print “Backup path: ” & strBackupPath

  

   ‘Check whether path is valid

On Error Resume Next

  

   Set sfld = fso.GetFolder(strBackupPath)

   If sfld Is Nothing Then

      ‘Create folder

      Set sfld = fso.CreateFolder(strBackupPath)

   End If

  

On Error GoTo ErrorHandler

   ‘Create proposed save name for backup

   strDayPrefix = Format(Date, “d-mmm-yyyy”)

   strSaveName = Left(strCurrentDB, _

      Len(strCurrentDB) – intExtLength) & ” Copy ” & SaveNo _

      & “, ” & strDayPrefix & strExtension

   strProposedSaveName = strBackupPath & strSaveName

   Debug.Print “Backup save name: ” & strProposedSaveName

   strTitle = “Database backup”

   strPrompt = “Save database to ” & strProposedSaveName & “?”

   strSaveName = Nz(InputBox(prompt:=strPrompt, _

      title:=strTitle, Default:=strProposedSaveName))

  

   ‘Deal with user canceling out of the InputBox

   If strSaveName = “” Then

      GoTo ErrorHandlerExit

   End If

  

   Set rst = dbs.OpenRecordset(“zstblBackupInfo”)

   With rst

      .AddNew

      ![SaveDate] = Format(Date, “d-mmm-yyyy”)

      ![SaveNumber] = SaveNo

      .Update

      .Close

   End With

 

   fso.CopyFile Source:=CurrentDb.Name, _

      destination:=strSaveName

  

ErrorHandlerExit:

   Exit Function

 

ErrorHandler:

   MsgBox “Error No: ” & err.Number & “; Description: ” & _

      err.Description

   Resume ErrorHandlerExit

 

End Function

 

Public Function BackupBackEnd()

 

On Error GoTo ErrorHandler

 

   Dim strBackEndDBNameAndPath As String

   Dim strBackEndDBName As String

   Dim strBackEndDBPath As String

   Dim strFilePath As String

   Dim strFullDBName As String

   Dim strFileName As String

   Dim strFullPath() As String

   Dim strDBName As String

   Dim intUBound As Integer

  

   Set dbs = CurrentDb

   Set tdfs = dbs.TableDefs

   ‘Components of the FileSystemObject object library are used

   ‘to work with files

   Set fso = CreateObject(“Scripting.FileSystemObject”)

   strCurrentDB = Application.CurrentProject.Name

   ‘Debug.Print “Current db: ” & strCurrentDB

   strDayPrefix = Format(Date, “d-mmm-yyyy”)

   intExtPosition = InStr(strCurrentDB, “.”)

   strExtension = Mid(strCurrentDB, intExtPosition)

   intExtLength = Len(strExtension)

  

   ‘Check whether there are any linked tables, and exit if not

   strBackEndDBNameAndPath = “”

  

On Error Resume Next

   For Each tdf In tdfs

      ‘Debug.Print “Table name: ” & tdf.Name

      ‘List table properties, and get back end database

      ‘path from Connect property of a linked table

      If Left(tdf.Name, 4) <> “MSys” Then

         For Each prp In tdf.Properties

            Debug.Print vbTab & “Property name: ” & vbTab & prp.Name _

               & vbCrLf & vbTab & “Property value:” & vbTab & prp.Value

            If prp.Name = “Connect” Then

               If Nz(prp.Value) <> “” And _

                  Left(prp.Value, 1) <> “?” Then

                  ‘Debug.Print “Connect string: ” & prp.Value

                  strBackEndDBNameAndPath = Mid(prp.Value, _

                     InStr(prp.Value, “=”) + 1)

                  ‘Debug.Print “Back end db name and path: ” _

                     & strBackEndDBNameAndPath

                  GoTo ContinueBackup

               Else

                  ‘Skip other properties

                  GoTo NextTable

               End If

            End If

         Next prp

      End If

NextTable:

   Next tdf

  

On Error GoTo ErrorHandler

   ‘No linked tables found

   strTitle = “No back end”

   strPrompt = “There are no linked tables in this database”

   MsgBox strPrompt, vbExclamation + vbOKOnly, strTitle

   GoTo ErrorHandlerExit

  

ContinueBackup:

   ‘Extract back end name and path from Connect property string

   strFullPath = Split(strBackEndDBNameAndPath, “”, -1, _

      vbTextCompare)

   intUBound = UBound(strFullPath)

   strBackEndDBName = strFullPath(intUBound)

   strBackEndDBPath = Mid(strBackEndDBNameAndPath, 1, _

      Len(strBackEndDBNameAndPath) – Len(strBackEndDBName))

   Debug.Print “Back end Database name: ” & strBackEndDBName

   Debug.Print “Back end Database path: ” & strBackEndDBPath

  

On Error Resume Next

   ‘Check whether back end path is valid

   Set sfld = fso.GetFolder(strBackEndDBPath)

   If sfld Is Nothing Then

      strTitle = “Invalid path”

      strPrompt = strBackEndDBPath _

         & ” is an invalid path; please re-link tables and try again”

      MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle

      GoTo ErrorHandlerExit

   End If

  

   ‘Create backup path string (Backups folder under back end

   ‘database folder)

   strBackupPath = strBackEndDBPath & “Backups”

   ‘Debug.Print “Backup path: ” & strBackupPath

  

   ‘Check whether path is valid

   Set sfld = fso.GetFolder(strBackupPath)

   If sfld Is Nothing Then

      ‘Create folder

      Set sfld = fso.CreateFolder(strBackupPath)

   End If

  

On Error GoTo ErrorHandler

 

   ‘Create proposed save name for backup

   strDayPrefix = Format(Date, “d-mmm-yyyy”)

   strSaveName = Left(strBackEndDBName, _

      Len(strBackEndDBName) – intExtLength) _

      & ” Copy ” & BackEndSaveNo _

      & “, ” & strDayPrefix & strExtension

   strProposedSaveName = strBackupPath & strSaveName

   Debug.Print “Backup save name: ” & strProposedSaveName

   strTitle = “Database backup”

   strPrompt = “Save database to ” & strProposedSaveName & “?”

   strSaveName = Nz(InputBox(prompt:=strPrompt, _

      Title:=strTitle, Default:=strProposedSaveName))

     

   ‘Deal with user canceling out of the InputBox

   If strSaveName = “” Then

      GoTo ErrorHandlerExit

   End If

  

   Set rst = dbs.OpenRecordset(“zstblBackupInfo”)

   With rst

      .AddNew

      ![BackEndSaveDate] = Format(Date, “d-mmm-yyyy”)

      ![BackEndSaveNumber] = BackEndSaveNo

      .Update

      .Close

   End With

  

   fso.CopyFile Source:=strBackEndDBNameAndPath, _

      destination:=strSaveName  

ErrorHandlerExit:

   Exit Function

 

ErrorHandler:

   MsgBox “Error No: ” & err.Number & “; Description: ” & _

      err.Description

   Resume ErrorHandlerExit

 

End Function

 


References

The code in the sample database needs the following references (in addition to the default references):

Microsoft DAO 3.6 Object Library

Microsoft Scripting Runtime

If you import code or objects into a database of your own, you may need to set one or more of these references.  The version number may differ, depending on your Office version; check the version you have.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.


Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch151.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

Updated Backup.mdb

Access 2000 database (can also be used in higher versions of Access)

Wherever you want

 

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.