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!