Question Saving database as backup

[MadeCurler]

I'd like to have a button on my dashboard that gives the user the ability to back up the database and save it with a different date. For example, existing database called "Fishing.accdb" and I'd like to have code behind a button that allows the database to be saved as "Fishing0711.accdb"

I'm trying to avoid the user having to go into the File Save as menu system....I've tried the docmd.copyObject method but can't get it to work

 

[Lightwave]

I usually do this by creating a small visual basic script that I place on the users desktop I have never tried to do this from within a database.

Copy the text at the bottom of this post into notepad.
Change the C:\data\Targetdb.accdb to the name and location of the database you wish to be backed up.
Now save this as a text file and within explorer alter the suffix to .vbs then create a shortcut of this file and place this on your desktop.

Now anyone clicking on the shortcut will run the visual basic script which will copy the file you have targeted and save it to the location C:\ with the name CopyTargetdbat12-11-01.accdb

The date will increment to the date when the backup was done.
If you wish to call the backed up database something else alter the following part appropriately - for instance simply delete & Year(Varnow) if you wish not to include the year in the backup name.

vardatefile = "CopyTargetdbat" & Year(Varnow) & "-" & Month(Varnow) & "-" & Day(Varnow) & ".accdb"

If you wish to change the location of where it is saved then alter.

BFilePath = "C:\" & vardatefile

I would suggest you get it working as a shortcut on the desktop first and then you could try putting the code behind a button in the database. It may or may not work but in the interim you will have a desktop shortcut that will do the trick.

Dim FSO
Dim BFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "CopyTargetdbat" & Year(Varnow) & "-" & Month(Varnow) & "-" & Day(Varnow) & ".accdb"
BFilePath = "C:\" & vardatefile

FSO.CopyFile "C:\data\Targetdb.accdb", BFilePath, "True"
Set FSO = nothing

 

[MadeCurler]

Thank you very much for your prompt reply. I have modified your code but can't get it to work....

Dim FSO
Dim BFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "CopyTargetdbat" & Year(Varnow) & "-" & Month(Varnow) & "-" & Day(Varnow) & ".accdb"
BFilePath = "G:\Bills Folder\Trial_Save_Database" & vardatefile

FSO.CopyFile "G:\Bills Folder\Mortimers\Archive3\Mortimers.accdb", BFilePath, "True"
Set FSO = nothing

 

[Lightwave]

In the first instance did you save it as a vbs file create a shortcut and just try and get it working as a shortcut on the desktop?

 

[Lightwave]

Also I think you need to change the code as follows.

Dim FSO
Dim BFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "Trial_Save_Database" & Year(Varnow) & "-" & Month(Varnow) & "-" & Day(Varnow) & ".accdb"
BFilePath = "G:\Bills Folder\" & vardatefile

FSO.CopyFile "G:\Bills Folder\Mortimers\Archive3\Mortimers.accdb", BFilePath, "True"
Set FSO = nothing

I think there were errors in your syntax of what you were wishing to achieve.

 

[MadeCurler]

Thank you very much for your prompt reply. I have now got it to work and saving the backup in a folder called "Trial_Save_Database" by adding a backslash as shown..

Dim FSO
Dim BFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "Copy of Database " & Year(Varnow) & "-" & Month(Varnow) & "-" & Day(Varnow) & ".accdb"
BFilePath = "G:\Bills Folder\Trial_Save_Database\" & vardatefile

FSO.CopyFile "G:\Bills Folder\Mortimers\Archive3\Mortimers.accdb", BFilePath, "True"
Set FSO = nothing

 

[Lightwave]

Made can I just ask.

Did you get it working as a desktop shortcut or code behind a button within the database?

 

[MadeCurler]

I saved the "BackUp.vbs" file in my database folder and double clicked that. On checking I found a copy in the folder.......path.....
"G:\Bills Folder\Trial_Save_Database\"
called "Copy of Database 2012-11-08.accdb"

Thanks again for your help

 

[Reflex_ht]

Hy, i´m making a backup of the backend DB wehere all data is stored with this VBA code directli trough MS Access:

Private Sub cmdQuit_Click()
On Error GoTo ErrH

    If strUser = "User1" Or strUser = "User2" Then
    Response = MsgBox("Wollen sie das Program beenden und ein Backup der Datenbank erstellen?", vbYesNo + vbQuestion, "Beenden")
    Else
    Response = MsgBox("Wollen sie das Program beenden?", vbYesNo + vbQuestion, "Beenden")
    End If
    If Response = vbYes Then
    OldName = "X:\Container........DATEN.accdb"
    NewName = "X:\Container..........DATENBackup.accdb"
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing
    If strUser = "User1" Or strUser = "User2" Then
    OldName2 = "X:\Container..........DATEN.accdb"
    NewName2 = "C:\DATENBackup.accdb"
    retval = 0
    Dim objFSO2 As Object
    Set objFSO2 = CreateObject("Scripting.FileSystemObject")
    retval = objFSO2.CopyFile(OldName2, NewName2, True)
    Set objFSO2 = Nothing
    End If
        Application.Quit
    End If
    
'ErrorHandler
Exit_ErrH:
    Exit Sub
ErrH:
Dim ErrNr, ErrB, ErrD, ErrSB, Nachricht, ErrC, ErrF As Variant
ErrNr = Nz(Err.Number, 0)
ErrB = Nz(Err.Description, "Keine Beschreibung")
ErrD = Now()
ErrSB = Nz(strUser, "Nicht indentifiziert")
ErrC = "cmdQuit_Click"
ErrF = Me.Form.Name
Nachricht = "Fehler!" & vbNewLine & "Fehler Nummer: " & ErrNr & vbNewLine & "Fehler Beschreibung: " & ErrB & vbNewLine & vbNewLine & "Der Fehler wird gespeichert und eine Nachricht wird zum Administrator gesendet"
MsgBox Nachricht, vbCritical, "Fehler!"
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO ErrorH " & _
            "( [ErrNr],[ErrB],[ErrD],[ErrSB],[ErrC],[ErrF]) " & _
            "VALUES (""" & ErrNr & """,""" & ErrB & """,""" & ErrD & """,""" & ErrSB & """,""" & ErrC & """,""" & ErrF & """)"
DoCmd.SetWarnings True
DoCmd.SendObject acSendNoObject, , , "myemail@adress", , , "Fehler Meldung", "Fehler!" & vbNewLine & "Fehlernummer: " & ErrNr & vbNewLine & "Fehlerbezeichnung: " & ErrB & vbNewLine & "Fehler im Formilar: " & ErrF & vbNewLine & "Fehler in Funktion: " & ErrC & vbNewLine & "Fehler Zeit: " & ErrD & vbNewLine & "Fehler bei Sachbearbeiter: " & ErrSB, False
    Resume Exit_ErrH
End Sub

Sorry for the large ERRor handler

The code makes a Backup Copy of the Backend DB to our Shared Drive X and on the Lokal drives of the users on C: . Because the DB is used of 5 users it would be to mutch to do that by every one so we decided to make it worikng just for 2 users.

Suggestions would be greate

 

[MadeCurler]

Thanks for taking the time to answer my query. I'm an enthusiastic amateur and your code for error handling is well outside my comfort zone.

 

[Reflex_ht]

I know your situation. I´m not bather. I stardet working with Access 4 moths ago ( 2 months of that with vba). I think that my errorhandler is not so god like it looks like i think it´s to complikated to be an herror handler. I must have it on every Sub and function and often the errorhandler is more difficult than the code he hase to save from error crashing.

You can just take the errorhandler away like this and you´ll have just the code:

Private Sub cmdQuit_Click()


If strUser = "User1" Or strUser = "User2" Then
Response = MsgBox("Wollen sie das Program beenden und ein Backup der Datenbank erstellen?", vbYesNo + vbQuestion, "Beenden")
Else
Response = MsgBox("Wollen sie das Program beenden?", vbYesNo + vbQuestion, "Beenden")
End If
If Response = vbYes Then
OldName = "X:\Container........DATEN.accdb"
NewName = "X:\Container..........DATENBackup.accdb"
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(OldName, NewName, True)
Set objFSO = Nothing
If strUser = "User1" Or strUser = "User2" Then
OldName2 = "X:\Container..........DATEN.accdb"
NewName2 = "C:\DATENBackup.accdb"
retval = 0
Dim objFSO2 As Object
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
retval = objFSO2.CopyFile(OldName2, NewName2, True)
Set objFSO2 = Nothing
End If
Application.Quit
End If
End Sub