Overwriting Records in Access 2000

You either have to:
Run a delete query to delete the records in the original table that are found in the records that you are appending, or
do an update query on the specific records, or , as in your case where I believe you are doing a BULK update, you could do it the other way around and append the records to a temporary and empty identical table, also with the primary key set, then append the records from the first table, delete all the records from the first table, and then fill the firsttable with all the records from the second table.

'Empty the temp table or create it new
.EXECUTE "DELETE * FROM TempTable;"

'Insert the new records
.EXECUTE "INSERT INTO TempTable......."

'Insert the records from the orig table - duplications will be ignored.
.EXECUTE "INSERT INTO TempTable SELECT * FROM OrigTable;"

'Empty the Original
.EXECUTE "DELETE * FROM OrigTable;"

'Add all records to the orig. table
.EXECUTE "INSERT INTO OrigTable SELECT * FROM TempTable;"

If your source is a table (or data imported to a temp table) and you are doing a bulk update, then use a query to delete duplicate primary key records in the existing table first. Then you can append or insert the data in the new table to the existing table.

If you want to permit users to "add" new records for existing primary keys, then I would suggest controlling it through the data entry form. Set up a separate data entry table that is a copy of the "real data" table (but with no data).

To use one form for editing/reviewing existing data and adding new data, have an "Add data" button. When clicked change the form's data source to the AddDataTable and allow additions.

Include a "Save record" button on the form. When clicked,the record will be saved in the RealData table, but only after checking for duplicate record and deleting the existing record so the revised or new record can be added.

Another possibility is to include onUpdate code after data is entered into the last primary key field. The code would check if a duplicate primary key exists and confirm with the user that he wants to replace the existing record. Or you just go to the existing record by turning the form's FilterOn to true and the Filter property to use the primary key. This would display the current data and let the user make any necessary changes--and would probably be cleaner and "safer".