Hi Guys,
pasted below is some code that I recently found to backsup a database to the a: drive. As I am not a programmer is there some one who could add code to this module that would compress the database first then delete/overwrite the current database on the a; drive and then backup again onto the a: drive.
The module code follows:
How can I do an automated back-up of my attached Back-End db using FileSystemObject?
faq181-1543
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("tbl1Members".Connect
strSource = Mid(strSource, 11, Len(strSource) - 10)
strDest = strDest & "_DNZ Membership Database_be.mdb"
Set fso = New FileSystemObject
fso.CopyFile strSource, strDest, True
db.Close
Set fso = Nothing
DoCmd.Hourglass False
MsgBox ("Backup Complete"
End If
Exit_Backup:
Exit Sub
Err_Backup:
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
Thanks in advance.
Hayton McGregor
pasted below is some code that I recently found to backsup a database to the a: drive. As I am not a programmer is there some one who could add code to this module that would compress the database first then delete/overwrite the current database on the a; drive and then backup again onto the a: drive.
The module code follows:
How can I do an automated back-up of my attached Back-End db using FileSystemObject?
faq181-1543
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("tbl1Members"
.ConnectstrSource = Mid(strSource, 11, Len(strSource) - 10)
strDest = strDest & "_DNZ Membership Database_be.mdb"
Set fso = New FileSystemObject
fso.CopyFile strSource, strDest, True
db.Close
Set fso = Nothing
DoCmd.Hourglass False
MsgBox ("Backup Complete"
End If
Exit_Backup:
Exit Sub
Err_Backup:
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
Thanks in advance.
Hayton McGregor