Turn off confirm in Access

you can use code and the iserr function to continue past the error?
you could not append the offending records/fields?

I have had similar conditions and found you must fix the issue of the KEY VIOLATION. Your primary or foreign keys do not match. Once this is fixed, create a macro to run your append query and start the macro with the SET WARNINGS action to NO. This allows operation of a macro without having to confirm each action in the macro.

I'm getting a key violation message.
A key violation occurs if:

You attempt to run a query that appends, deletes, or updates records in a way that violates the rules of referential integrity for related tables.
Your query attempts to append or update records that contain primary key values that already exist in the destination table.
If you run such a query, it will not modify the records that cause the key violation.

To modify records in a way that will violate referential integrity, you can break the relationship between the affected tables or turn referential integrity off, and then modify the affected tables one at a time. However, the resulting data in the two tables may then conflict.