How to make a backup of access 2003 database using a command button?

[jcnovosad]

Hello,
I need the procedure and/or code to add a command button to the Main Switchboard form, so the user click it and creates a full backup of the database in other folder, where the database name will have the date added to it.

I have tried different things, with no success... getting depress now!

if I press tools, database utilities, and backup database I'll get what I want, ... well I need to do the same, but using a command button.

(paramount will be if I can do the same without the user intervention, just checkind the date and if has been 7 days since the last backup, trigger a new one automatically)

many thanks in advance
Juan

[ADezii]

You may run into problems attempting to Backup the Current Database, so your best option may be to create a New Database with the Date appended to the Name, then export selectrive Objects to it.

[jcnovosad]

Hi ADezzi, you are right, I already ran into problems, but I found few webpages where people have said they found the way... using APIs, modules, functions and macros... but was not published the steps and the final coding... I tried for many hours all the "intermediate" processes posted without lack. What makes me holding faith is "tools, database utilities, and backup database" do the magic, if the Access 2003 has those 3 clicks, should be some way to replicate the same.
Following your line of thoughts, do you have the code behind the button to export/import all the objects from a database?
Take care

[ADezii]

The code would be considerable, but when I get a chance I'll post a Template that you can work from.

[jcnovosad]

OK, Thanks. I'll wait for it

[ADezii]

The following code will Backup all Database Objects to a Folder specified by the Constant conPATH_TO_BKUPS (Line #3). You can change this if you wish, but be sure to add the Trailing Backslash since I did not test for this for the sake of brevity. The actual Back Up Database will be the Base Name of the Current Database minus the .mdb Extension, plus an Underscore (_), plus the Date formatted as mmddyyyy, plus .mdb. Each Backup will be unique only as far as a single Day is concerned and will DELETE a previous Backup for that Day should it exist. I intentionally placed the code in a Public Function where it can easily and directly be called from a Main Switchboard Item. Have fun, and if you have any questions, please feel free to ask.

Public Function fExportAllDBObjects()
On Error Resume Next
Const conPATH_TO_BKUPS As String = "C:\DB Backups\"
Dim strAbsoluteBkUpPath As String
Dim aob As AccessObject
Dim strDBName As String
Dim wrkSpace As Workspace
Dim dbBackup As Database
 
DoCmd.Hourglass True
 
'Retrive the Current Database Name only, strip out .mdb
strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
 
'Make the Backup DB Name unique for each Date
strDBName = strDBName & "_" & Format$(Date, "mmddyyyy") & ".mdb"
 
strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
 
'If a Bacup already exists for this Date, then DELETTE it
If Dir$(strAbsoluteBkUpPath) <> "" Then
  Kill strAbsoluteBkUpPath
End If
 
'Get Default Workspace.
Set wrkSpace = DBEngine.Workspaces(0)
 
'Create the Database
Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
 
'Export all Tables
For Each aob In CurrentData.AllTables
  If Mid$(aob.Name, 2, 3) <> "Sys" Then     'Don't Export System Tables
    DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                           acTable, aob.Name, aob.Name
  End If
Next
 
'Export all Queries
For Each aob In CurrentData.AllQueries
  DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                         acQuery, aob.Name, aob.Name
Next
 
'Export all Forms
For Each aob In CurrentProject.AllForms
  DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                         acForm, aob.Name, aob.Name
Next
 
'Export all Reports
For Each aob In CurrentProject.AllReports
  DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                         acReport, aob.Name, aob.Name
Next
 
'Export all Macros
For Each aob In CurrentProject.AllMacros
  DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                         acMacro, aob.Name, aob.Name
Next
 
'Export all Modules
For Each aob In CurrentProject.AllModules
  DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                         acModule, aob.Name, aob.Name
Next
 
DoCmd.Hourglass False
End Function

P.S. - Just realized that you are using Access 2007. In that case, change the File Extension in Code Lines 12 (Comment), 13, and 16 to .accdb.

[jcnovosad]

Hi ADezzi,
Many Thanks!!!!
The code you posted works splendid!!!!
Thanks again!
Juan

[ADezii]

You are quite welcome, Juan.

[neelsfer]

This backup works very well. I adapted it to remove the date feature. How would one restore the same file it created from ie an external drive and replace the existing file?
I changed the code as shown below to export data to my e:drive

On Error Resume Next
    Const conPATH_TO_BKUPS As String = "e:\"
    Dim strAbsoluteBkUpPath As String
    Dim aob As AccessObject
    Dim strDBName As String
    Dim wrkSpace As Workspace
    Dim dbBackup As Database
 
    DoCmd.Hourglass True
 
    'Retrive the Current Database Name only, strip out .mdb
    strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".accdb", "")
 
    'Make the Backup DB Name unique for each Date
    strDBName = strDBName & ".accdb"
     '& "_" & Format$(Date, "mmddyyyy")
    strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
 
    'If a Bacup already exists for this Date, then DELETTE it
    If Dir$(strAbsoluteBkUpPath) <> "" Then
      Kill strAbsoluteBkUpPath
    End If
 
    'Get Default Workspace.
    Set wrkSpace = DBEngine.Workspaces(0)
 
    'Create the Database
    Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
 
    'Export all Tables
    For Each aob In CurrentData.AllTables
      If Mid$(aob.Name, 2, 3) <> "Sys" Then     'Don't Export System Tables
        DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
                               acTable, aob.Name, aob.Name
      End If
    Next
 
 
 
    DoCmd.Hourglass False

[ADezii]

1. Make a Backup Copy of your Data and other Objects.
2. DELETE Slective Objects from the Current Database.
3. Import Selective Objects from the External Database to replace those Deleted from the Current Database.
4. Should any part of this process fail, aside from the Backup, you will still have the Backup to return to.