append query overwrites all matching records

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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.
 
John said:
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.
Click to expand...

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?
 
Will that also put the fields that do not exist into the older table?
Click to expand...

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?
Click to expand...

Yes.
 
John said:
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.
Click to expand...

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
 
Back
Top