Good afternoon...
I would like to append records from 40 identical tables into one table (same
fields, same structure)
This is a heavy duty task and I would like to know if anyone has a method to
do it fast.
Thanks so much for your advices
MK
A couple of ways suggest themselves. One would be to create one (or maybe two,
40 is a lot of tables) UNION queries in the SQL window. Start by creating a
select query on one of the tables, selecting all the fields. Select View...
SQL from the menu, and edit the SQL text from something like
SELECT this, that, theother, who, what, IDontKnow
FROM Table1;
to
SELECT this, that, theother, who, what, IDontKnow
FROM Table1
UNION ALL
SELECT this, that, theother, who, what, IDontKnow
FROM Table2
UNION ALL
SELECT this, that, theother, who, what, IDontKnow
FROM Table3
<etc>
through all 40 tables. Use your own table and fieldnames of course.
Save this query and base an Append query on it.
OR... especially if this is something you'll be doing repeatedly, perhaps with
different tables...
create a "driver" table with a text field containing the SQL or the query
names of the append queries, a sequence number field, and a Yes/No field to
run the query. You can write some fairly simple code to open this driver table
as a recordset, sorted by the sequence field, with a criterion of True on the
yes/no field; loop through the records and use the Querydef Execute method to
run the queries in sequence.
John W. Vinson [MVP]
