Backup Access Database Daily/Weekly

Posted on

Question :

We have a Microsoft Access database split into backend/frontend. What I am looking for is to be able to automatically backup these files on a daily or weekly basis – what is the best way to go about this? We don’t want to have to worry about backups for the database, just know that it is occuring automatically on a schedule.


Answer :

(Borrowing from my earlier answer here…)

re: actually performing the backup

Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like

copy /Y d:appsdatabasesmydatabase.accdb z:backupsdatabases*.*

re: automatic scheduling of the backup

The Task Scheduler in Windows could take care of that for you. Once you’ve created your script to copy the database file(s) you can create a scheduled task to run it periodically. See the MSDN article Using the Task Scheduler (Windows) for more information.

You can write a sub to backup the database to a specified folder and run this sub when the database is launched. There are a lot of ways to do this, but I personally use the following:

Function fMakeBackup() As Boolean

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

    Source = CurrentDb.Name

    Target = "Z:My AppsBackupsYourFielName"
    Target = Target & Format(Date, "mm-dd") & "   "
    Target = Target & Format(Time, "hh-mm") & ".accdb"

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

End Function

Leave a Reply

Your email address will not be published. Required fields are marked *