Backup database via VBA

[RachH]

Is there a way to backup an Access database to another folder and rename the backup using VBA code?

[NeoPa]

You can Shell() to a COPY command (or a CMD file which includes a COPY). I would advise using ShellWait() though, as continuing to process a database while it is being copied is a recipe for corruption. Of the copy database at least.

[ADezii]

@RachH
Hvae you considered something as simple as a Batch File, to which a Short Cut can exist on your Desktop?

[Stewart Ross]

Hi. In one of my applications I have an Access DB which is set via the Windows task scheduler to open at 5am and close itself automatically when done. One of the tasks it performs is to copy a master database to a backup location. It renames the previous backup copy of the master DB then copies in the master DB.

The code uses the filesystem object to do the copying and renaming and is listed below. It refers to four form controls which are bound to fields in an underlying table that record the master url for the database being copied, the copy url for the location it is being copied to, and the file names (with extensions) of the original and copied files.

I provide this as a somewhat rough and ready routine, which for me is in use every day to provide an off-line copy of the master database on which I can work without danger of damaging a live application. In my case the database copied records the details of all incidents attended by the Fire and Rescue Service I work for, so working on the live data is too big a risk.

Private Sub CopyDB_Click()
    Dim fso As Object, f2 As Object, f3 As Object
    Dim strCopyFrom As String, strCopyTo As String, strPrev As String
    Dim strPrevLoc As String
    On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    strCopyFrom = Me.Control_DB_Original_URL & "\" & Me.Control_DB_Name
    strCopyTo = Me.Control_DB_Copy_URL & "\" & Me.Copy_DB_Name
    strPrev = "Prev-" & Me.Copy_DB_Name
    strPrevLoc = Me.Control_DB_Copy_URL & "\" & strPrev
    Me.Message = "Copy started at " & Format(Time(), "hh:mm") & " hrs." & vbCrLf & "This can take around 10 mins to complete..."
    Me.Repaint
    DoCmd.Hourglass True
    fso.DeleteFile strPrevLoc ' delete the previous backup version of the file.
    Set f3 = fso.Getfile(strCopyTo)
    f3.Name = strPrev
    On Error GoTo errhandler
    Set f2 = fso.Getfile(strCopyFrom)
    f2.Copy (strCopyTo)
    Me.Message = Me.Message & vbCrLf & "Copy ended at " & Format(Time(), "hh:mm") & " hrs."
    Me.Repaint
    Set fso = Nothing
    DoCmd.Hourglass False
    DoCmd.Close acForm, Me.Name
    Exit Sub
errhandler:
    MsgBox "Error: " & Err.Number & " -> " & Err.Description
    DoCmd.Hourglass False
End Sub

-Stewart

[NeoPa]

The reason I opted for the COPY command Stewart, is that it can work even on an open database (I was thinking the OP probably wanted the db to back itself up).

If that's not an issue then the FileSystem Object approach as you have it is flexible and does the job :)

[RachH]

Wow thanks for all the great responses! I'll play with things a bit to figure out what will work best for our need. I really appreciate the help :-)

[NeoPa]

You're welcome Rach :)

Welcome to Bytes!