Append/combine tables in VBA?

[tweetyksc]

We have 15 different site locations and numerous tables and listfiles at the 15 different sites. I'm trying to run review/cleanup before we go to a centralized system (YAY!). I've linked to all the table files then combine them all into one table file in Access so I can do counts, etc. For this I have 15 different append queries, to combine all into the one table (a delete query is run to clear the table first). This works fine as all the tables are in one table file per location with table codes. However there are also 10 "code files" that I want to combine (i.e. 10 x 15!) There's got to be an easier way than setting up 15 different append queries x the 10 filenames I have isn't there?

 

[tweetyksc]

OK so I can see I will need to use SQL but taking that from the query I can also see is not sufficient..

Here is the SQL behind the query - What do I need to do to adapt it to work in VBA? newbie to sql
--------------------
INSERT INTO TABLES ( SCHOOLNUM, TABLETYPE, CODE, CODEDESC )
SELECT ATBL8999.SCHOOLNUM, ATBL8999.TABLETYPE, ATBL8999.CODE, ATBL8999.CODEDESC
FROM ATBL8061
WHERE (((ATBL8999.CODE) Is Not Null));

 

[namliam]

You can run the queries in code... Currentdb.Execute "YourQuery"

If that is what you are looking for...

Why use links and not just use Docmd.Transfer"Something" to import the files into the location/table you want to have them?

 

[namliam]

INSERT INTO TABLES ( SCHOOLNUM, TABLETYPE, CODE, CODEDESC )
SELECT ATBL8999.SCHOOLNUM, ATBL8999.TABLETYPE, ATBL8999.CODE, ATBL8999.CODEDESC
FROM ATBL8061
WHERE (((ATBL8999.CODE) Is Not Null));

Oh and BTW above query will not work... ATBL8999 doesnt excist in the from...

It would have to be something like:

INSERT INTO TABLES ( SCHOOLNUM, TABLETYPE, CODE, CODEDESC )
SELECT ATBL8999.SCHOOLNUM, ATBL8999.TABLETYPE, ATBL8999.CODE, ATBL8999.CODEDESC
FROM ATBL8999
WHERE (((ATBL8999.CODE) Is Not Null));

See what I mean?