Backing up Your Database

Everybody knows that data should be backed up frequently, and I like to make it as convenient as possible to back up a database. My standard database main menu features a Backup button, which calls the BackupDB procedure listed next. I created the Backup code and menu button in an earlier version of Access, when there was no way of backing up a database without closing it down.

Since that time, Microsoft has added a backup command that doesn't require closing down the database, though it's still not as convenient as my one-click backup. The Access 2007 backup command is available through the Manage button on the File menu (shown in Figure 9.7). Selecting the "Back Up Database" option opens the Save As dialog shown in Figure 9.8.


The Access 2007 Back Up Database selection.


The Access 2007 Back Up Database selection.


The built-in database save dialog.


The built-in database save dialog.

If you are running the backup code in Windows Vista, you may be unable to back up databases in certain folders, because of Vista security restrictions. This is a Vista issue, not a problem with the database, as you can backup the database after moving it to another folder with lower security.

Though it is much easier to back up an Access database in Access 2007 than in previous versions, the new "Back Up Database" selection on the Manage menu defaults to saving the database copy in the same folder as the database itself. If you want to save your backups to another folder (which I prefer, to avoid confusion between databases and their backups), you have to browse for that folder. The BackupDB function in the following basBackup module saves backups to a folder called Backups under the database folder; you can modify the hard-coded path for saving backups as desired if you want to save your backups to another location. If you want to save backups to a Daily Backups folder on drive E, for example, you would replace the lines of code strBackupPath = Application.CurrentProject.Path _ & "\Backups\"

with strBackupPath = "E:\Daily BackupsX"

See Chapter 14 for a discussion of an add-in with user-selectable backup options, ncluding selection of the backup folder from a Folder Picker dialog.

The Access 2007 Backup.accdb database contains the table, module, and macros that are used to do the database backups. These database objects can be imported into any Access 2007 database, and the BackupDB function can be run from the mcrBackup macro, or from a button on the main menu. The basBackup module is listed as follows:

Option Explicit Option Compare Database

Private dbs As DAO.Database

Private fld As Scripting.Folder

Private fso As Scripting.FileSystemObject

Private intReturn As Integer

Private rst As DAO.Recordset

Private strBackupPath As String

Private strCurrentDB As String

Private strDayPrefix As String

Private strDBName As String

Private strDefault As String

Private strFinalSaveName As String

Private strPrompt As String

Private strSaveName As String

Private strTitle As String

Public Function BackupDB(

Requires a reference to the Microsoft Scripting Runtime library. On Error GoTo ErrorHandler

Set fso = CreateObject("Scripting.FileSystemObject") strCurrentDB = Application.CurrentProject.FullName Debug.Print "Current db: " & strCurrentDB strBackupPath = Application.CurrentProject.Path _ & "\Backups\"

Attempt to set a reference to the backup folder.

Set fld = fso.GetFolder(strBackupPath) strDayPrefix = Format(Date, "mm-dd-yyyy") strSaveName = Left(Application.CurrentProject.Name, Len(Application.CurrentProject.Name) - 6) _ & " " & SaveNo & ", " & strDayPrefix & ".accdb" strSaveName = strBackupPath & strSaveName Debug.Print "Backup save name: " & strSaveName strTitle = "Database backup"

strPrompt = "Accept or edit name of database copy" strDefault = strSaveName strFinalSaveName = InputBox(prompt:=strPrompt, _ title:=strTitle, Default:=strDefault)

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblBackupInfo") With rst .AddNew

![SaveDate] = Format(Date, "d-mmm-yyyy") ![SaveNumber] = SaveNo .Update .Close End With fso.CopyFile strCurrentDB, strFinalSaveName

ErrorHandlerExit: Exit Function


If Err.Number = 7 6 Then

If the backup folder was not found, create it.

fso.CreateFolder strBackupPath Resume Next Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Public Function SaveNo() As String

On Error GoTo ErrorHandler

Dim intDayNo As Integer Dim strNextNo As String

Create a unique save number for today.

intDayNo = Nz(DMax("[SaveNumber]", "tblBackupInfo", _

"[SaveDate] = Date()")) Debug.Print "Day no. " & intDayNo strNextNo = CStr(intDayNo + 1) Debug.Print "Next No. " & strNextNo SaveNo = strNextNo

ErrorHandlerExit: Exit Function


& "; Description: " & Err.Description Resume ErrorHandlerExit

End Function

The SaveNo() function creates an incrementing number for the current backup by picking up the latest number stored for todays date from tblBackupInfo and adding 1 to it.

The BackupDB procedure backs up the current database, creating a save name from the databases name (picked up from the Name property of the CurrentProject property of the Application object), plus the SaveNo() value and todays date, formatted with dashes. (You can change the date format as desired, so long as you don't use slashes or other characters that are not permitted in file names.) The proposed save name is presented in an InputBox, where it can be edited as desired, such as adding info on specific changes made to the database; it is then saved to a folder called Backups under the current database folder.

The GetFolder method of the FileSystemObject is used to reference the Backups folder; if the folder is not found, the function's error handler creates the folder using the CreateFolder method. A record is added to tblBackupInfo with the date and the save number, and finally the

CopyFile method of the FileSystemObject is used to copy the current database to a backup with the final save name in the Backups folder.

The tblBackupInfo table stores dates and incrementing numbers for the backup names. The backups you make on a given day will have a number (starting with 1) and the date, so they don't overwrite each other, and you will know the order in which the backups were created. Figure 9.9 shows the InputBox presented by the BackupDB function; you can accept the proposed save name or edit it as desired.


Saving a database copy using the BackupDB function.


0 0

Post a comment