By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Backup database via VBA

P: 15
Is there a way to backup an Access database to another folder and rename the backup using VBA code?
Apr 16 '09 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 15k+
P: 31,709
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.
Apr 16 '09 #2

Expert 5K+
P: 8,679
Hvae you considered something as simple as a Batch File, to which a Short Cut can exist on your Desktop?
Apr 16 '09 #3

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CopyDB_Click()
  2.     Dim fso As Object, f2 As Object, f3 As Object
  3.     Dim strCopyFrom As String, strCopyTo As String, strPrev As String
  4.     Dim strPrevLoc As String
  5.     On Error Resume Next
  6.     Set fso = CreateObject("Scripting.FileSystemObject")
  7.     strCopyFrom = Me.Control_DB_Original_URL & "\" & Me.Control_DB_Name
  8.     strCopyTo = Me.Control_DB_Copy_URL & "\" & Me.Copy_DB_Name
  9.     strPrev = "Prev-" & Me.Copy_DB_Name
  10.     strPrevLoc = Me.Control_DB_Copy_URL & "\" & strPrev
  11.     Me.Message = "Copy started at " & Format(Time(), "hh:mm") & " hrs." & vbCrLf & "This can take around 10 mins to complete..."
  12.     Me.Repaint
  13.     DoCmd.Hourglass True
  14.     fso.DeleteFile strPrevLoc ' delete the previous backup version of the file.
  15.     Set f3 = fso.Getfile(strCopyTo)
  16.     f3.Name = strPrev
  17.     On Error GoTo errhandler
  18.     Set f2 = fso.Getfile(strCopyFrom)
  19.     f2.Copy (strCopyTo)
  20.     Me.Message = Me.Message & vbCrLf & "Copy ended at " & Format(Time(), "hh:mm") & " hrs."
  21.     Me.Repaint
  22.     Set fso = Nothing
  23.     DoCmd.Hourglass False
  24.     DoCmd.Close acForm, Me.Name
  25.     Exit Sub
  26. errhandler:
  27.     MsgBox "Error: " & Err.Number & " -> " & Err.Description
  28.     DoCmd.Hourglass False
  29. End Sub
Apr 17 '09 #4

Expert Mod 15k+
P: 31,709
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 :)
Apr 17 '09 #5

P: 15
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 :-)
Apr 17 '09 #6

Expert Mod 15k+
P: 31,709
You're welcome Rach :)

Welcome to Bytes!
Apr 18 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.