Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 22 '13 at 18:53

This question came from our site for professional and enthusiast programmers.

2 Answers 2

(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.

share|improve this answer
    
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. –  Sean Hawkins Mar 23 '13 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 Mar 25 '13 at 23:16

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.