VB Code to insert into a table from multiple tables

[hinkwale]

Hello,

I have this code that selects all records from a table and inserts in another whose fields are aligned exactly the same way -

Sub InsertIntoX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = CurrentDb

' Select all records in the New Customers table
' and add them to the Customers table.
dbs.Execute " INSERT INTO Hostnames_Applications_Main_test1 " _
& "SELECT * " _
& "FROM [Data_Source_Hostname_Application_BREWS_copy];"


dbs.Close
End Sub

I am trying to include extra code that will select all records from two other tables - Data_Source_Hostname_Application_CMDB_copy and Data_Source_Hostname_Application_MIKES_list_copy tables and inserting into Hostnames_Applications_Main_test1. How do I include the other tables in the code above?

 

[c23lemon]

I suggest you should wrap your existing code around a for loop and modify the portion where you have the target table, see below.

Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = CurrentDb

for each query in Array("Data_Source_Hostname_Application_BREWS_copy", "Data_Source_Hostname_Application_CMDB_copy", "Data_Source_Hostname_Application_MIKES_list_copy")

' Select all records in the New Customers table
' and add them to the Customers table.
dbs.Execute " INSERT INTO Hostnames_Applications_Main_test1 " _
& "SELECT * " _
& "FROM [" & query & "];"

Next each

dbs.Close
End Sub