backup tables

[coachafrane]

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

Sub backup()
   Dim dTime As Date
    On Error Resume Next
    dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
    If Err.Number <> 0 Then Exit Sub
    Do Until Time = dTime
        DoEvents
    Loop
    Dim sfile As String, oDB As DAO.Database
    'IF DAO.dll does not load, then find
    'ACEDAO.dll in Program Files (64-bit machine) or MS Office AC DB Engine Object
    sfile = CurrentProject.Path & "\" & "Staff Data" & ".accdb"
    If Dir(sfile) <> "" Then Kill sfile
    Set oDB = DBEngine.Workspaces(0).CreateDatabase(sfile, dbLangGeneral)
    oDB.Close
    DoCmd.Hourglass True
 
    Dim oTD As TableDef
    For Each oTD In CurrentDb.TableDefs
        If Left(oTD.NAME, 4) <> "MSys" Then
            DoCmd.CopyObject sfile, , acTable, oTD.NAME
            'OR: DoCmd.TransferDatabase acExport,"Microsoft Access", sFile, acTable,oTD
        End If
    Next oTD
 
    DoCmd.Hourglass False
    MsgBox "Backup is stored in the same folder"
End Sub

[jimatqsi]

coachafrane,
Here's one way. Put this code in place of your "if left" block of code.

Select Case oTD.NAME
case “operational areas”
     DoCmd.CopyObject sfile, , acTable, oTD.NAME
case “Projects”.
     DoCmd.CopyObject sfile, , acTable, oTD.NAME
case “Communities”
     DoCmd.CopyObject sfile, , acTable, oTD.NAME
case “Zones”
     DoCmd.CopyObject sfile, , acTable, oTD.NAME
 
End Select
 

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