Automate Backing Up a Database with VBA

Here’s the code you can implement to assign a back-up procedure to a button.  You will need to create a button and assign this macro to it, but after that, all you have to do is click the button and a back up copy will be made with the back-up date appended to the suffix of the file.  This is a great little tool for those of us who forget to back things up regularly.  Access databases can become corrupt seemingly out of the blue, so make this piece of code your friend.

Public Sub cmd_make_BU_Click()

Dim Source As String
Dim Target As String
Dim retval As Integer

Source = CurrentDb.name

'This is the only thing to change - add the path of where you want the file to save here
Target = "V:\Human Resources\HR OPS\Compensation\databases\LTI_Tracking_Database_Backup_"
Target = Target & Format(Date, "mm-dd") & ".accdb"

' create the backup
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(Source, Target, True)
Set objFSO = Nothing

'Opens the folder of the file you just created
Application.FollowHyperlink "V:\Human Resources\HR OPS\Compensation\databases\"

End Sub