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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
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
    Commented 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.
    – Gord Thompson
    Commented Mar 25, 2013 at 23:16
  • What does this mean ? Is it possible to copy it while in use?
    – GorillaApe
    Commented 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.
    – Gord Thompson
    Commented 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.
    – Gord Thompson
    Commented 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
Improve this answer