Access.2007.VBA.Bibl..

MsgBox “Error No: “ & Err.Number _

& “; 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

ErrorHandler:

MsgBox “Error No: “ & Err.Number _

& “; 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 today’s date from tblBackupInfo and adding 1 to it.

The BackupDBprocedure backs up the current database, creating a save name from the database’s
name (picked up from the Nameproperty of the CurrentProjectproperty of the Application
object), plus the SaveNo()value and today’s 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 GetFoldermethod of the FileSystemObjectis 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

Working with Files and Folders 9