Access.2007.VBA.Bibl..

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

ErrorHandler:

If Err.Number = 76 Then

If the backup folder was not found, create it.

fso.CreateFolder strBackupPath

Resume Next

Else

Part II Writing VBA Code to Exchange Data between Office Components