Setup Form with Backup Button - Help!

L

Lord Max

Registered User.
Local time
Yesterday, 16:01
Joined
Sep 13, 2010
Messages
12
Hi,

Is it at all possible to create a form that contains a backup button, which, when pushed, either backs up the database using the filename of the days date or opens up a Save As menu to save the current database?

If further possible, would it be possible to write the actual name of what the user wants the save to be called in a form field, and select the location of the save also in the form, and finally click a 'Save' button in the form? So everything is handled within the form?

Thank you for your assistance!

Max
 
It doesn't work from my end too so it was most likely deleted by the original poster.
 
Backup Button
Code: 
Private Sub Button_Backup_Click()
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Const conPATH_FILE_ACCESS_ERROR = 75
On Error GoTo Backup_Button_Backup
'buf = Back Up Folder
'buf is created if it does not exist
'CurrentProject.Path = the path that the FE is located
buf = CurrentProject.Path & "\Backups\"
MkDir buf
    Resume Backup_Button_Backup
Backup_Button_Backup:
'Use yyyy-mm-dd hh-mm-ss as folder name. Change as needed.
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
str = CurrentProject.Path & "\Backups\" & MD_Date
'Source = where the data is stored
source = CurrentProject.Path & "\Data\"
MkDir str
Set fs = CreateObject("Scripting.FileSystemObject")
'Change the file extension as needed
fs.CopyFile source & "*.accdb", str
Set fs = Nothing
MsgBox "Data backup at " & vbCrLf & MD_Date & vbCrLf & "successfully!", _
        vbInformation, "Backup Successful"
Exit_Button_Backup:
  Exit Sub
Err_Button_Backup:
  If err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox err.Description, vbExclamation, "Error Creating " & str
  
End If
    Resume Exit_Button_Backup
End Sub
 
Restore Button

Code: 
Private Sub Button_Restore_Click()
Dim frm As Object
 
'Close all open forms
For Each frm In CurrentProject.AllForms
    If frm.IsLoaded Then
        DoCmd.Close acForm, frm.Name
    End If
Next frm
'Open form F_Restore. Used to give tables time to close.
    DoCmd.OpenForm "F_Restore", acNormal, "", "", , acNormal
End Sub

Form F_Restore
Code: 
Private Sub Form_Timer()
 
If Me.Timer = 0 Then
Dim str As String
Dim buf As String
Dim MD_Date As Variant
Dim fs As Object
Dim source As String
Dim pubInputFolder As String
Dim txtfolder As String
Dim deletesource As String
Const conPATH_FILE_ACCESS_ERROR = 75
    On Error GoTo 0
 
With Application.FileDialog(4)
    .InitialFileName = CurrentProject.Path & "\Backups\"
    .AllowMultiSelect = False
    .Filters.Clear
    .Show
    pubInputFolder = .SelectedItems(1)
    txtfolder = pubInputFolder
End With
On Error GoTo Backup
'First do a backup
'Note folder now has R at the end = backup at restore
buf = CurrentProject.Path & "\Backups\"
MkDir buf
    Resume Backup
Backup:
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss") & " R"
str = CurrentProject.Path & "\Backups\" & MD_Date
source = CurrentProject.Path & "\Data\"
MkDir str
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile source & "*.accdb", str
Set fs = Nothing
deletesource = CurrentProject.Path & "\Data\"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile deletesource & "\*.accdb"
Set fs = Nothing
Dim backupsource As String
Dim destination As String
backupsource = txtfolder
destination = CurrentProject.Path & "\Data\"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile backupsource & "\*.*", destination & "\"
Set fs = Nothing
MsgBox "Data from " & vbCrLf & backupsource & vbCrLf & "successfully restored!", _
        vbInformation, "Restore Successful"
ExitBackup:
    DoCmd.Close acForm, "F_Restore"
    DoCmd.OpenForm "_Admin", acNormal, "", "", , acHidden
    DoCmd.OpenForm "F__Menu", acNormal, "", "", , acNormal
 
  Exit Sub
ErrBackup:
  If err.Number = conPATH_FILE_ACCESS_ERROR Then
    MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
           "accessing it!", vbExclamation, "Path/File Access Error"
  Else
    MsgBox err.Description, vbExclamation, "Error Creating " & str
 
    Resume ExitBackup
End If
Else
Me.Timer = Me.Timer - 1
End If
End Sub
 
Thank you for posting the code. I am hitting one snag:
fs.CopyFile source & "*.accdb", str

The code is creating the backup folder and subfolders just as written. When it hits this line it stops. My though I am using Access 2010 I have saved database as an .mdb do to users having different versions of access. Am I supposed to sage the extension to .mdb? I tried and it is not working for me. After workin on a backup button for most of a day your code was a brilliant, I just need to overcome this last hurdle.

Thank you!
 
The reason I used the extension is so that it doesn't backup any temp files. You can change this.

But why not leave the database in 2010 format? Use Access runtimes (see one of my other threads). That way the user doesn't need Access (any version) just the Runtime (free!!!). Now you get all the advantages of 2010 without having to "downgrade" the database.

Just click on the link in my sig. There is a whole thing on making deployment files that force the database to use Runtimes, installers etc.
 
Does the above Backup script require any modification to handle a single BACKEND file requiring daily backup?

e.g. C:\Program\backend_be.mdb

backed up as C:\Program\Backups\backend_be 20110927.mdb

Many thanks!
 
I assembled the code specifically for a multi file backend.

The code makes a new folder. The folder name is the current date and time. Then it copies files from the source folder to the created folder.

To restore it just copies files from one folder to another, after making a backup.
 
OK ... I'll copy the code and see how I get on ... many thanks
 
Park said:
Thank you for posting the code. I am hitting one snag:
fs.CopyFile source & "*.accdb", str

The code is creating the backup folder and subfolders just as written. When it hits this line it stops. My though I am using Access 2010 I have saved database as an .mdb do to users having different versions of access. Am I supposed to sage the extension to .mdb? I tried and it is not working for me. After workin on a backup button for most of a day your code was a brilliant, I just need to overcome this last hurdle.

Thank you!
Click to expand...

The code fails for me at this point also, error message received is
"Run-time error 76:
Path not found!

At this point the code has correctly created the \Backups\2011-09-28 folder but this error seems to stop any files being copied into the folder?

Any ideas why?

Update: I changed
source = CurrentProject.Path & "\Data\"
to
source = CurrentProject.Path
as the \Data\ element from the data source path wasn't required, but I am now getting
"Run-time error 76:
File not found!

I have tried changing the code
fs.CopyFile source & "*.accdb", str
e.g. to
fs.CopyFile source & "*.mdb", str
fs.CopyFile source & "*.*", str
but still get the same message :-(

Further Update: Success! :)
Changed code to
fs.CopyFile source & "\.mdb", str
... the difference being the addition of a backslash?

Given that the backslash seems to be required should it perhaps be added higher up in the code somewhere?
e.g.
source = CurrentProject.Path & "\"
 
Last edited:
I would now like to road-test the RESTORE code ... but it looks like there's a form "F_Restore" that needs to be built, into which I'm guessing the Restore coding needs to be copied into the On-Timer event property?
 
Yes, you need to create the F_Restore form with the code in the timer.

The first part of the restore code closes any open forms. Then opens F_restore form. Then waits a second or two. Then runs the restore code.

The reason:
If a form has access to a table, the code stalls because the backend is open. The F_Restore form is not assosiated with any table. Remember the first bit of code, that shuts down any open forms (hidden or visable). But it can take a while for the table to be completely closed. Therefore we wait a second or two. Then we start replacing files.
 
Last edited:
I did acutally look into completely closing the front end and opening a second front end that runs the restore code. But some idiot at MS forgot that once a database is closed the code stops running. Batch files? Yes I guess.
 
Does your "F_restore" form have any fields on it or is it completely blank??
 
oops my post disapeared

The first part of the code closes all open forms which "close" any open forms.
F_Restore is not linked to any tables.
It waits 2 seconds to give the tables time to "close" completely.
Then files are replaced.

For the F_restore form you need the following:

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Private Sub Form_Timer()

If Me.Timer = 0 Then
Restore
Else
Me.Timer = Me.Timer - 1
End If

End Sub

On the form itself are a Lable with the works "Please Wait..."
Then there is an unbound box called Timer, default value is 2 (for two seconds).
The form has no border, min/max/close buttons ect. Just a full screen. The 2 seconds count down.
 
Dairy Farmer said:
For the F_restore form you need the following:

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Private Sub Form_Timer()

If Me.Timer = 0 Then
Restore
Else
Me.Timer = Me.Timer - 1
End If

End Sub
Click to expand...

Is all this code placed in the On Current form property? I tried this and got an error message when the code Private Sub Form_Timer() was read ... given that it follows an "End Sub" command is this the start of some additional code?
Also, what does "Restore" link to?

I'm very close ... but can't light the cigar just yet!
 
Last edited:
You must log in or register to reply here.

Similar threads

A
Problems creating 'front-end' portion of split database (ie. no 'Save As' dialogue after clicking 'Make ACCDE' and 'Save As' button)
2
Replies
20
Views
535
Silversee
S
T
Cancel,Save and Delete buttons for Order form
Replies
9
Views
239
Gasman
Gasman
asteropi
Solved I need some help with a find-item button
2
Replies
27
Views
754
Pat Hartman
P
mloucel
Solved Form_BeforeUpdate error 2115
Replies
8
Views
538
Pat Hartman
P
K
Tree View and Ribbon Structure
Replies
4
Views
272
KitaYama
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom