'Backup this database' button (1 Viewer)

Islwyn

Registered User.
Local time
Today, 10:55
Joined
Aug 29, 2011
Messages
28
Not sure if anyone's posted anything similar before, but the below, which is derived from code passed on by Bob Larson (thanks again Bob), is for a 'backup this database' button for users who need clear instructions how and reminders to regularly backup a standalone database. I found that FileCopy would not work while the database was open because permission was denied, so this is the way around it.

--------------------------------------
Code:
Private Sub BackUp_Click()
    Dim CopyFile As String
    Dim FileName As String
    Dim FileAddName As String
    Dim CopyAddName As String
    Dim CopyAddName2 As String
    Dim todaysdate As String
    Dim CopyNameDef As String
    Dim FileAdd As String
    Dim CopyAdd As String
    Dim strFilter As String
 
    If MsgBox("DO YOU WANT TO BACK UP THE DATABASE?", vbYesNo) = vbYes Then
        ' record date of backup in a table (so can be displayed clearly on main menu)
        DoCmd.SetWarnings False
 
        DoCmd.openquery "BackupDate1"
        DoCmd.openquery "BackupDate2"
 
        ' set name of database to be copied (without the '.mdb' bit):
        FileName = "NameOfTheDatabase"
 
        ' set folder address for database
        FileAdd = "D:\Users\MyName\FolderWhereDatabaseIsKept\"
 
        ' set address for second backup location which the user will not see
        CopyAdd = "D:\Users\MyName\FolderWhereOtherBackupsAreKep t\"
 
        ' set the name and address of the file to be backed up:
        FileAddName = FileAdd & FileName & ".mdb"
 
        ' include date in a default name for the new backup file
        todaysdate = Date$
        CopyNameDef = FileName & todaysdate & ".mdb"
        'Ask for alternative name for backup file
        strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mdb)", "*.mdb")
 
        CopyAddName = ahtCommonFileOpenSave(OpenFile:=False, Filter:=strFilter, _
                                            Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY, FileName:=CopyNameDef, _
                                            InitialDir:=FileAdd, DialogTitle:="Backup Database")
        CopyAddName2 = CopyAdd & FileName & todaysdate & ".mdb"
 
        ' set the file name of the batch file to create:
        CopyFile = FileAdd & "BackupDb.cmd"
 
        ' create the batch file:
        Open CopyFile For Output As #1
 
        Print #1, "Echo Off"
        Print #1, "ECHO Making backup of database"
        Print #1, "Copy /Y """ & FileAddName & """ """ & CopyAddName & """"
        Print #1, ""
        Print #1, "Copy /Y """ & FileAddName & """ """ & CopyAddName2 & """"
        Print #1, ""
        Print #1, "START /I MSAccess.exe """ & FileAddName & """"
 
        Close #1
        ' run the batch file:
        Shell CopyFile
        'close the database:
        DoCmd.Quit
    End If
End Sub

---------------------------------------
 
Last edited by a moderator:

boblarson

Smeghead
Local time
Today, 03:55
Joined
Jan 12, 2001
Messages
32,059
Two things -

1. You have some queries running which you didn't explain to people that they would need to have a table and the SQL for those.

2. I would not use DoCmd.SetWarnings but instead execute the queries using

CurrentDb.Execute "QueryNameHere"

Also, you didn't turn warnings back on (which I would do in the Exit procedure of your function).
 

speakers_86

Registered User.
Local time
Today, 06:55
Joined
May 17, 2007
Messages
1,919
I have buttons that run bobs code in Aphrodite. I just posted a new version, but it's not approved yet.
 

Islwyn

Registered User.
Local time
Today, 10:55
Joined
Aug 29, 2011
Messages
28
Thanks Bob,

Thanks for the advice regarding the Warnings, I will amend my code appropriately.

The first Query, "BackupDate1", uses a delete query to delete a record in a Table containing the last backup date.

This date is shown in a text box on the Main Menu form, and also on a form as you close the database asking if you want to back up the database before closing or just close it (i.e. "Your database was last backed up on the" [backupdate] "Do you want to...").

The second adds the current date (Date()) to the table using an append query (SQL below)

INSERT INTO BackUpDate ( BackDate )
SELECT Date() AS Expr1;

The reason the old record is deleted and then a new record added, rather than a record just appended, is for other functionality reasons which I won't go into - an update query could be used normally.

Thanks

Is
 

Users who are viewing this thread

Top Bottom