Create Update Query

[Dave]

I have a number of text files that I have linked to within MS Access. I need
to combine these files into one. All files have the same number of columns
and column names. How do I do this?

I have started to create an Update Query with all the source tables in the
query design window. I have the fields of the destination table in the grid
of the query design window. I do not know what information to put into the
"Update To" grid of the design window.

 

[Jeanette Cunningham]

Dave,
instead of an update query, use an append query.
The first append query will put all the records from the second table into
the first table.
Create a new append query to put all the records from the third table into
the first table.
And so on until finished.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Dave]

Am I able to do this within a single query?

 

[pietlinden]

Am I able to do this within a single query?

No, you'll need to run an append query against each text file.

I wonder if you couldn't create an import specification and then use
TransferText. If all the text files are in a single directory, you
could just use DIr to loop oover the contents of the directory and
process with TransferText and your import spec. (sorry about the
typos, I'm not watching what I'm typing...)

If you dig around here or in the help there is an example of how to
use Dir to loop over the contents of a directory... better yet...
check out allen browne's website...

www.allenbrowne.com
He has an example of a recursive search, among other things.

 

[John W. Vinson]

I have a number of text files that I have linked to within MS Access. I need
to combine these files into one. All files have the same number of columns
and column names. How do I do this?

I have started to create an Update Query with all the source tables in the
query design window. I have the fields of the destination table in the grid
of the query design window. I do not know what information to put into the
"Update To" grid of the design window.

An Update query updates existing records in a table, changing the value in the
record which is already there... not what you want!

Instead, create a local Access table, with all the datatypes and field sizes
that you want.

Then either do as Jeannette suggests and run multiple Append queries, or you
can build a UNION query and do it all in one shot: open a new query based on
one of the linked text files, include all the fields, and select View... SQL;
then copy and paste the SQL so that it resembles

SELECT file1.thisfield, file1.thatfield, file1.theotherfield FROM file1
UNION ALL
SELECT file2.thisfield, file2.thatfield, file2.theotherfield FROM file2
UNION ALL
< etc through all the linked tables >

Save this query and then base an Append query on it, appending into the new
empty Access table.