query to append and update?

[MN]

I have to import a tab-delimited text file daily into Access through a
macro. All of the data needs to be added to an existing table. Some of
the data already exists but may be updated by the imported text file. I
can update the data through an update query or append the entire import
table through an append query. Is there a way to combine the two so that
I can update existing records and append only new records (without
duplicating existing records)? Thanks.

 

[Pieter Wijnen]

not without VBA code no
or at least in a simple (correct) way
however having unique keys in the table U can append safely with access &
then run (probably inefficient) update queries to update the data

Pieter

 

[Bob Quintal]

I have to import a tab-delimited text file daily into Access
through a macro. All of the data needs to be added to an
existing table. Some of the data already exists but may be
updated by the imported text file. I can update the data
through an update query or append the entire import table
through an append query. Is there a way to combine the two so
that I can update existing records and append only new records
(without duplicating existing records)? Thanks.

Import the data to a temporary table.
Run the update query to set the changed values into the
permanent table.
run a delete query to remove records from the temporary table if
they already exist in the permanent table.
Import the remaining (new) records to the permanent table from
the temporary table.
Delete all records from the temporary table, so you are set for
the next cycle.

You could do this faster and better with some VB code, instead
of a macro.

 

[MN]

Import the data to a temporary table.
Run the update query to set the changed values into the
permanent table.
run a delete query to remove records from the temporary table if
they already exist in the permanent table.

How would I setup the delete query to only find the records that aren't
in the permanent table? Thanks.

 

[Bob Quintal]

How would I setup the delete query to only find the records
that aren't in the permanent table? Thanks.

I would never set up a query to delete those that aren't in the
permanent table. I'd set up a query to append them to the
permanent table.

To delete those that are already in the permanent table from the
temp table so that i could later append those that are left, I
would "DELETE * from temptable WHERE primarykey IN (SELECT
primarykey from permanenttable);

substitute primarykey with the real name of whatever field is
the one which defines the uniqueness of the record, and the real
names for temptable and permanenttable

Import the remaining (new) records to the permanent table
from the temporary table.
Delete all records from the temporary table, so you are set
for the next cycle.

You could do this faster and better with some VB code,
instead of a macro.