Access.2007.VBA.Bibl..

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 BackupDBfunction 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 Backups\”

See Chapter 14 for a discussion of an add-in with user-selectable backup options,

including 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 BackupDBfunction 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()

CROSS-REFCROSS-REF

Working with Files and Folders 9