Re: Combine two tables with VBA

[Marshall Barton]

I have two tables that I would like to combine in VBA.
Both tables have the same structure (field names and
format). I would simply like to append the contents of
table2 to the bottom of table1. Is there a VBA command to
do this? I do not want to loop through the records of
table2 and copy them one at a time.

Use an Append query to do that.

db.Execute "INSERT INTO table1 SELECT * FROM table2", _
dbFailOnError

Note: you can use a UNION ALL query to retrieve all the data
in both tables without actually combining the tables.

 

[George]

Thanks for the info - however, I want a table instead of
a query as a result. Howdo I change the query to a make-
table query?

 

[Marshall Barton]

Thanks for the info - however, I want a table instead of
a query as a result. Howdo I change the query to a make-
table query?

I thought you wanted the table2 data appended to table1???

If you want to put both sets of data into a new table then
you need to run two queries. The first to make the new
table with data from table1 and the second to append the
data from table2.

db.Execute "SELECT * INTO newtable FROM table1", _
dbFailOnError
db.Execute "INSERT INTO newtable SELECT * FROM table1", _
dbFailOnError

 

[Marshall Barton]

I misunderstood your first answer. I read the
word "Append query" and assumed that it was a query. If
the db.Execute command appends table2 into table1, then
that is exactly what I need.

Oh OK. Note that you can not perform an SQL statement
directly in VBA, you have to use something that understands
SQL. There is the RunSQL method, but, for several reasons,
I prefer to use the DAO Execute method for this kind of
thing. So, the code I posted earlier uses the Execute
method to run the append (INSERT INTO) query.