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

backup tables

P: 7
I have this code that backs up all my tables in the same folder.

I want to however back up only specified table with names; “operational areas”, “Communities”, “Zones” and “Projects”.

Can I please get help on how to integrate that into my existing code or any other way to achieve that


Expand|Select|Wrap|Line Numbers
  1. Sub backup()
  2.    Dim dTime As Date
  3.     On Error Resume Next
  4.     dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
  5.     If Err.Number <> 0 Then Exit Sub
  6.     Do Until Time = dTime
  7.         DoEvents
  8.     Loop
  9.     Dim sfile As String, oDB As DAO.Database
  10.     'IF DAO.dll does not load, then find
  11.     'ACEDAO.dll in Program Files (64-bit machine) or MS Office AC DB Engine Object
  12.     sfile = CurrentProject.Path & "\" & "Staff Data" & ".accdb"
  13.     If Dir(sfile) <> "" Then Kill sfile
  14.     Set oDB = DBEngine.Workspaces(0).CreateDatabase(sfile, dbLangGeneral)
  15.     oDB.Close
  16.     DoCmd.Hourglass True
  17.  
  18.     Dim oTD As TableDef
  19.     For Each oTD In CurrentDb.TableDefs
  20.         If Left(oTD.NAME, 4) <> "MSys" Then
  21.             DoCmd.CopyObject sfile, , acTable, oTD.NAME
  22.             'OR: DoCmd.TransferDatabase acExport,"Microsoft Access", sFile, acTable,oTD
  23.         End If
  24.     Next oTD
  25.  
  26.     DoCmd.Hourglass False
  27.     MsgBox "Backup is stored in the same folder"
  28. End Sub
Apr 6 '14 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,240
coachafrane,
Here's one way. Put this code in place of your "if left" block of code.

Expand|Select|Wrap|Line Numbers
  1. Select Case oTD.NAME
  2. case “operational areas”
  3.      DoCmd.CopyObject sfile, , acTable, oTD.NAME
  4. case “Projects”.
  5.      DoCmd.CopyObject sfile, , acTable, oTD.NAME
  6. case “Communities”
  7.      DoCmd.CopyObject sfile, , acTable, oTD.NAME
  8. case “Zones”
  9.      DoCmd.CopyObject sfile, , acTable, oTD.NAME
  10.  
  11. End Select
  12.  
And please remember to use Code tags around any program code you post. It's simple, just click the [CODE/] button and type your code between the tags that appear.

Jim
Apr 6 '14 #2

Post your reply

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