6

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.

Thanks.

2 Answers 2

6

(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:\apps\databases\mydatabase.accdb z:\backups\databases\*.*

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.

9
  • that's basically what I'm after. Is there any issue with the database being split into frontend/backend? What if it's in use at the time of backup? Thanks.
    – user21663
    Mar 23, 2013 at 18:30
  • [1] re: frontend/backend -- The backend file is the one that constantly changes, so that's the one that needs (more) regular backups. [2] re: file (may be) in use -- There is no simple answer to that one. Shared-file databases do not have the same advantages as client-server databases in that regard. Mar 25, 2013 at 23:16
  • What does this mean ? Is it possible to copy it while in use?
    – GorillaApe
    Sep 19, 2014 at 11:54
  • @Parhs Yes, it is possible to copy an Access database file while it is in use (unless it has been opened as Exclusive). However, copying any file while it is actively in use is usually not a good idea. Sep 19, 2014 at 12:22
  • 1
    @Parhs While it is most unlikely that the source (original) file would get corrupted, the destination file might not be a reliable backup if writes were happening on the source file while it was being copied. Sep 19, 2014 at 12:34
5

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 Apps\Backups\YourFielName"
    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