I have finished coding the solution. I ended up using DAO after all.

The solution grabs a collection containing info about the source and target database paths and table names.

Then it loops through the collection, opens the target database, deletes the table if it exists there, then runs a query using the “IN” clause to pull the table from the source database.

Here’s the main function:

Public Sub DeleteAndReCopyTables()
    Dim TableCollection As VBA.Collection
    Dim TblToCopy As EG_TableToCopy
    Dim itm As Variant
    Dim dbTrg As DAO.Database

    Set TableCollection = GetTableCollection()
    
    For Each itm In TableCollection
        Set TblToCopy = itm
        Set dbTrg = DBEngine.OpenDatabase(TblToCopy.TargetDbPath)
        DeleteDAOtableIfExists dbTrg, TblToCopy.TargetTable
        dbTrg.Execute "SELECT * INTO " & TblToCopy.TargetTable & " FROM " & TblToCopy.SourceTable & _
            " IN '" & TblToCopy.SourceDbPath & "'"
        dbTrg.Close
        Set dbTrg = Nothing
        Set TblToCopy = Nothing
        Set itm = Nothing
    Next itm
    Set TableCollection = Nothing
End Sub

As you can see, I’m delegating to a few other functions. The first grabs the collection with all the information. It uses a collection I can loop through. Each element of the collection is a simple Class containing just public string variables so I can get names that make sense.

' Class EG_TableToCopy
Option Compare Database
Option Explicit

Public SourceDbPath As String
Public TargetDbPath As String
Public SourceTable As String
Public TargetTable As String

And here is the GetTableCollection function:

Private Function GetTableCollection() As VBA.Collection
    Dim retVal As VBA.Collection
    Set retVal = New VBA.Collection
    
    Const S_InventoryDataPath = "C:\SourceDb\InventoryData.mdb"
    Const S_PlannedReqDataPath = "C:\SourceDb\PlannedReq300_be.mdb"
    Const S_PartSourceDataPath = "C:\SourceDb\PartSource_be.mdb"
    Const T_InventoryDataPath = "C:\TargetDb\New_InventoryData.accdb"
    Const T_PlannedReqDataPath = "C:\TargetDb\New_PlannedReqData.accdb"
    Const T_PartSourceDataPath = "C:\TargetDb\New_PartSourceData.accdb"
    
    AddTablesToCollection retVal, S_InventoryDataPath, T_InventoryDataPath, _
        Array("ColorData", "FamilyData", "Family", "PartNumbers")
    
    AddTablesToCollection retVal, S_PlannedReqDataPath, T_PlannedReqDataPath, _
        Array("Defaults_CartonSize", "Defaults_LeadTime", "Defaults_SafetyStock", "ExtForecast")
    
    AddTablesToCollection retVal, S_PartSourceDataPath, T_PartSourceDataPath, _
        Array("FamilyBOM")
    
    Set GetTableCollection = retVal
End Function

Then I use another function to add the TableToCopy objects to the collection. This function takes the collection to add to, the source db, the target db and an array of table names. Even though I made this a function, it should have been a sub because I don’t return anything. I simply add to the collection it was given.

Private Function AddTablesToCollection(ByRef TTCs As VBA.Collection, srcTable As String, _
        trgTable As String, arrTableNames As Variant) As VBA.Collection
    Dim tn As Variant
    Dim Ttc As EG_TableToCopy
    For Each tn In arrTableNames
        Set Ttc = New EG_TableToCopy
        Ttc.SourceDbPath = srcTable
        Ttc.SourceTable = CStr(tn)
        Ttc.TargetDbPath = trgTable
        Ttc.TargetTable = CStr(tn)
        TTCs.Add Ttc
        Set Ttc = Nothing
    Next tn
End Function

After getting the collection, I loop through it, open the target db and delete the target table if it exists using this sub:

Private Sub DeleteDAOtableIfExists(srcDb As DAO.Database, tblName As String)
    Dim dName As Variant
    For Each dName In srcDb.TableDefs
        If dName.Name = tblName Then
            dbTrg.TableDefs.Delete tblName
            Exit For
        End If
    Next dName
    Set dName = Nothing
End Sub

The sub above loops through all the tables in the database and deletes the table name if it’s found in the TableDefs collection.

Finally, the original function then runs a SQL statement in the form of SELECT * INTO localtable * FROM srctable IN ‘srcDbPath’. This connects automatically to the remote srcDbPath table, kind of like a linked table, but you don’t have to actually create a link.

That’s it. There’s a few things you could do to this code to make it better. Like I’m only defining one table name for both the source and target tables, although you could change the AddTablesToCollection function to fix that. It also does absolutely no error checking. If it does encounter errors it will pop up the standard dialog. It will not be very graceful if you put the wrong paths in, or the paths are a UNC that is currently unavailable, or you put in the wrong table names.

But it does work for my purposes and in testing performed it’s function.

Now on to another task!