merge text file data into table

[BLTibbs]

Windows xp and office 2003 professional.

I have a text file that has different field names than my access table. I
need to append new records and update existing records from the text file
into my table. The text file and the table do share a common unique ID
number (but the fields are not named the same).

It seems like this should be a simple thing to automate, but I am running in
circles. Is there code or an update query way to do this that I am missing?

Thanks!

 

[John Nurick]

If you need to append new records and update existing ones you'll need
to run both an append query and an update query.

One way is to create a linked table to access the text file, and two
queries to move the data. The linked table of course will have the text
file's own field names, but and you map these to the Access names in the
queries.

First run the update query, which must join the two tables on the ID
field and update values in the Access table to match corresponding
values in the text file. Then the append query on the linked table,
appending records where ID NOT IN (SELECT ID FROM AccessTable).