By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

P: 4
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
Feb 4 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fExportAllDBObjects()
  2. On Error Resume Next
  3. Const conPATH_TO_BKUPS As String = "C:\DB Backups\"
  4. Dim strAbsoluteBkUpPath As String
  5. Dim aob As AccessObject
  6. Dim strDBName As String
  7. Dim wrkSpace As Workspace
  8. Dim dbBackup As Database
  9.  
  10. DoCmd.Hourglass True
  11.  
  12. 'Retrive the Current Database Name only, strip out .mdb
  13. strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
  14.  
  15. 'Make the Backup DB Name unique for each Date
  16. strDBName = strDBName & "_" & Format$(Date, "mmddyyyy") & ".mdb"
  17.  
  18. strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
  19.  
  20. 'If a Bacup already exists for this Date, then DELETTE it
  21. If Dir$(strAbsoluteBkUpPath) <> "" Then
  22.   Kill strAbsoluteBkUpPath
  23. End If
  24.  
  25. 'Get Default Workspace.
  26. Set wrkSpace = DBEngine.Workspaces(0)
  27.  
  28. 'Create the Database
  29. Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
  30.  
  31. 'Export all Tables
  32. For Each aob In CurrentData.AllTables
  33.   If Mid$(aob.Name, 2, 3) <> "Sys" Then     'Don't Export System Tables
  34.     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  35.                            acTable, aob.Name, aob.Name
  36.   End If
  37. Next
  38.  
  39. 'Export all Queries
  40. For Each aob In CurrentData.AllQueries
  41.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  42.                          acQuery, aob.Name, aob.Name
  43. Next
  44.  
  45. 'Export all Forms
  46. For Each aob In CurrentProject.AllForms
  47.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  48.                          acForm, aob.Name, aob.Name
  49. Next
  50.  
  51. 'Export all Reports
  52. For Each aob In CurrentProject.AllReports
  53.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  54.                          acReport, aob.Name, aob.Name
  55. Next
  56.  
  57. 'Export all Macros
  58. For Each aob In CurrentProject.AllMacros
  59.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  60.                          acMacro, aob.Name, aob.Name
  61. Next
  62.  
  63. 'Export all Modules
  64. For Each aob In CurrentProject.AllModules
  65.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  66.                          acModule, aob.Name, aob.Name
  67. Next
  68.  
  69. DoCmd.Hourglass False
  70. 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.

Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,607
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.
Feb 4 '10 #2

P: 4
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
Feb 4 '10 #3

ADezii
Expert 5K+
P: 8,607
The code would be considerable, but when I get a chance I'll post a Template that you can work from.
Feb 4 '10 #4

P: 4
OK, Thanks. I'll wait for it
Feb 4 '10 #5

ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fExportAllDBObjects()
  2. On Error Resume Next
  3. Const conPATH_TO_BKUPS As String = "C:\DB Backups\"
  4. Dim strAbsoluteBkUpPath As String
  5. Dim aob As AccessObject
  6. Dim strDBName As String
  7. Dim wrkSpace As Workspace
  8. Dim dbBackup As Database
  9.  
  10. DoCmd.Hourglass True
  11.  
  12. 'Retrive the Current Database Name only, strip out .mdb
  13. strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".mdb", "")
  14.  
  15. 'Make the Backup DB Name unique for each Date
  16. strDBName = strDBName & "_" & Format$(Date, "mmddyyyy") & ".mdb"
  17.  
  18. strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
  19.  
  20. 'If a Bacup already exists for this Date, then DELETTE it
  21. If Dir$(strAbsoluteBkUpPath) <> "" Then
  22.   Kill strAbsoluteBkUpPath
  23. End If
  24.  
  25. 'Get Default Workspace.
  26. Set wrkSpace = DBEngine.Workspaces(0)
  27.  
  28. 'Create the Database
  29. Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
  30.  
  31. 'Export all Tables
  32. For Each aob In CurrentData.AllTables
  33.   If Mid$(aob.Name, 2, 3) <> "Sys" Then     'Don't Export System Tables
  34.     DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  35.                            acTable, aob.Name, aob.Name
  36.   End If
  37. Next
  38.  
  39. 'Export all Queries
  40. For Each aob In CurrentData.AllQueries
  41.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  42.                          acQuery, aob.Name, aob.Name
  43. Next
  44.  
  45. 'Export all Forms
  46. For Each aob In CurrentProject.AllForms
  47.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  48.                          acForm, aob.Name, aob.Name
  49. Next
  50.  
  51. 'Export all Reports
  52. For Each aob In CurrentProject.AllReports
  53.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  54.                          acReport, aob.Name, aob.Name
  55. Next
  56.  
  57. 'Export all Macros
  58. For Each aob In CurrentProject.AllMacros
  59.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  60.                          acMacro, aob.Name, aob.Name
  61. Next
  62.  
  63. 'Export all Modules
  64. For Each aob In CurrentProject.AllModules
  65.   DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  66.                          acModule, aob.Name, aob.Name
  67. Next
  68.  
  69. DoCmd.Hourglass False
  70. 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.
Feb 4 '10 #6

P: 4
Hi ADezzi,
Many Thanks!!!!
The code you posted works splendid!!!!
Thanks again!
Juan
Feb 5 '10 #7

ADezii
Expert 5K+
P: 8,607
You are quite welcome, Juan.
Feb 5 '10 #8

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2.     Const conPATH_TO_BKUPS As String = "e:\"
  3.     Dim strAbsoluteBkUpPath As String
  4.     Dim aob As AccessObject
  5.     Dim strDBName As String
  6.     Dim wrkSpace As Workspace
  7.     Dim dbBackup As Database
  8.  
  9.     DoCmd.Hourglass True
  10.  
  11.     'Retrive the Current Database Name only, strip out .mdb
  12.     strDBName = Replace(Mid$(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1), ".accdb", "")
  13.  
  14.     'Make the Backup DB Name unique for each Date
  15.     strDBName = strDBName & ".accdb"
  16.      '& "_" & Format$(Date, "mmddyyyy")
  17.     strAbsoluteBkUpPath = conPATH_TO_BKUPS & strDBName
  18.  
  19.     'If a Bacup already exists for this Date, then DELETTE it
  20.     If Dir$(strAbsoluteBkUpPath) <> "" Then
  21.       Kill strAbsoluteBkUpPath
  22.     End If
  23.  
  24.     'Get Default Workspace.
  25.     Set wrkSpace = DBEngine.Workspaces(0)
  26.  
  27.     'Create the Database
  28.     Set dbBackup = wrkSpace.CreateDatabase(strAbsoluteBkUpPath, dbLangGeneral)
  29.  
  30.     'Export all Tables
  31.     For Each aob In CurrentData.AllTables
  32.       If Mid$(aob.Name, 2, 3) <> "Sys" Then     'Don't Export System Tables
  33.         DoCmd.TransferDatabase acExport, "Microsoft Access", strAbsoluteBkUpPath, _
  34.                                acTable, aob.Name, aob.Name
  35.       End If
  36.     Next
  37.  
  38.  
  39.  
  40.     DoCmd.Hourglass False
Jun 16 '11 #9

ADezii
Expert 5K+
P: 8,607
  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.
Jun 19 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.