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 |
