Appending multiple tables

  • Thread starter Thread starter Indu Aronson
  • Start date Start date
I

Indu Aronson

I have 35 tables that are named File1 to File35 that I
have to append to a table called tblUnivData.

Is doing them one by one the only option? I know how to
do them one by one but I was wondering if there was an
easier way out. There are no duplicate data and all 35
tables have the same fields as tblUnivData.

Any ideas / help will be most welcome.

Thanks.
 
One idea would be to create and save a union query (name it qryUnion) that
gets the records from all the tables, and then create one append query that
copies all the records to the final table.

The union query would look like this (not complete, but you get the idea):

SELECT * FROM File1
UNION ALL
SELECT * FROM File2
UNION ALL
SELECT * FROM File3
UNION ALL
(etc.)
SELECT * FROM File35;


The append query then would use the above query (qryUnion) as the source
table.
 
Create one Append Query using File35. In the Designer, give File35 an
Alias of F.

Switch to SQL view.

INSERT INTO tblUnivData ( Field1, Field2, Field3)
SELECT F.Field1, F.Field2, F.Field3
FROM File35 AS F;

Run it.
then change File35 to File34 in one place.
Run it.
Repeat until done.

If you need to automate it,

Sub AppendToTblUnivData(strFile as string)
Dim sql1 as string
sql1 = "INSERT INTO tblUnivData ( Field1, Field2, Field3) " _
& " SELECT F.Field1, F.Field2, F.Field3 " _
& " FROM " & strfile & " AS F;"

currentdb.execute sql1
End sub

Sub Main
AppendToTblUnivData "File1"
AppendToTblUnivData "File2"
AppendToTblUnivData "File3"
AppendToTblUnivData "File4"
....
end Sub

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org
 
Ken, thank you very much for your time and help. Your
method saved me a lot of time and made it a relatively
painless process.

Thanks again.

--Indu
 
OR....

In debug window:

for i = 1 to 35:docmd.RunSQL "INSERT INTO tblUnivData (Field1, Field2,
Field3, Field4) " _
& "SELECT Field1, Field2, Field3, Field4 FROM File" & cstr(i) & ";":next
 
Back
Top