Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Other topics FAQ

Access Howto:

How can I do an automated back-up of my attached Back-End db using FileSystemObject? by jimmythegeek
Posted: 6 Feb 02 (Edited 27 Sep 07)

One advantage FileSystemObject has over FileCopy is that it can copy an open database, where FileCopy returns an error if the file is open. You must make sure you have the proper reference. While in the module design view go to Tools | References and select "Microsoft Scripting Runtime" from the list

This function will automatically backup any attached Back-end database. It can be modified to work with multiple back-ends as well, contact me if you need help. The function below backs up to the a: drive, however you can back up to any drive that will allow writing, this includes network drives as well, just use the UNC path. It also works great to Zips and Jazz's and such.

The function as is, will overwrite the existing file every time. If you want a new backup every day, simply un-comment the appropriate lines, and comment out the one strDest value line, and it will write a file in the following format each time:

TodaysDate_FileName.mdb (i.e.  052600_MyBackEnd.mdb)

Simply copy the following sub routine into a module, replace the appropriate text, and you are ready to roll.

On the On Click event of a button simply type "Call Backup". That's It !!

Hope it helps you.


Public Sub BackUp()
    'This function backs up to the a: drive, you can back up to any location, just change the strDest Value

On Error GoTo Err_Backup    
    Dim db As Database
    Dim strSource As String, strDest As String, strError As String
    Dim strDate As String, strDateX As String
    Dim fso As FileSystemObject

    If MsgBox("Are you sure you want to back up data?", vbQuestion + vbYesNo, " Continue?") = vbYes Then
        ' Un-comment the following 3 lines for a new backup for every day
        'strDate = Format(Date, "mm/dd/yy")
        'strDateX = Left(strDate, 2) & Mid(strDate, 4, 2) & Right(strDate, 2)
        'strDest = "a:\" & strDateX
        Set db = CurrentDb()
        DoCmd.Hourglass True
        'Put any table name in here that exists in your back-end
        strSource = db.TableDefs("TableNameHere").Connect
        strSource = Mid(strSource, 11, Len(strSource) - 10)
        ' If you are using a new back-up every day, un-comment this line and replace the database name, and comment out the next line down
        'strDest = strDest & "_YourBackEndDBNameHere.mdb"
        ' Replace with your database Back-end name and different drive letter or UNC Path if desired
        strDest = "a:\YourBackEndDBNameHere.mdb"
        Set fso = New FileSystemObject
        fso.CopyFile strSource, strDest, True
        Set fso = Nothing
        DoCmd.Hourglass False
        MsgBox ("Backup Complete")
    End If
    Exit Sub

    Select Case Err.Number
        Case 61
            strError = "Floppy disk is full" & vbNewLine & "cannot export mdb"
            MsgBox strError, vbCritical, " Disk Full"
            Kill strDest
        Case 71
            strError = "No disk in drive" & vbNewLine & "please insert disk"
            MsgBox strError, vbCritical, " No Disk"
        Case Else
            Err.Raise Err.Number, Err.Description
    End Select
    DoCmd.Hourglass False
    Resume Exit_Backup
End Sub


Jim Lunde

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close