append query overwrites all matching records

[Guest]

I have an append query in a database that must append records into
another database on a click() event. How can I make that append query
overwrite all records where two fields match?
The button click will be in database A and will append to database B.
I want the query in database A to take all of its records, and if the
fields [Part ID] and [Revision] are the same in database B, then that
record will be overwritten with the new one. Otherwise, if there is no
record in database B where both [Part ID] and [Revision] are the same,
then create a new record.

It's a little complicated since it's happening in a separate database,
so I can't just open a query in database B to do comparisons. Any ideas?

 

[John Vinson]

I have an append query in a database that must append records into
another database on a click() event. How can I make that append query
overwrite all records where two fields match?

An Append query *adds new records which do not exist*. In order to
overwrite - update the values in - existing records, you need an
Update query instead; join the two tables, and update the fields in
TableB to the fields in TableA.

 

[Guest]

An Append query *adds new records which do not exist*. In order to
overwrite - update the values in - existing records, you need an
Update query instead; join the two tables, and update the fields in
TableB to the fields in TableA.

Will that also put the fields that do not exist into the older table?
Also, can I update into a separate database as I can with an append query?

 

[John Vinson]

Will that also put the fields that do not exist into the older table?

No. It will only update the fields that you tell it to update; it will
not change either table's structure, just the contents of the fields
referenced on the Update To line.

Also, can I update into a separate database as I can with an append query?

Yes.

 

[Guest]

No. It will only update the fields that you tell it to update; it will
not change either table's structure, just the contents of the fields
referenced on the Update To line.




Yes.

Do you know how I could get the update query to go into a second
database? I get an error when I try to form the SQL like the append
query, i.e.
"UPDATE tblSuppliers INTO tblSuppliers IN 'Database B.mdb'" gives me a
syntax error.

Secondly, since it won't add the new records, how can I make my append
query NOT add already existing records? I need to do something like

"WHERE (tblDatasheet.[Supplier ID] <> tblDatasheet.[Supplier ID] IN
'Database B.mdb')"

but I don't think I can compare fields in two databases.

Nathan