append query key violations (access 2000)

Topic

New Reply

Hi.

I have a fairly simple append query where I want to append the records from one table into another, only if they don’t already exist in that table. The table that I am appending to is the Staff table and the primary key is Staff ID.

This is the sql I have

INSERT INTO Staff ( StaffID )
SELECT zOperaII.wn_ninum
FROM zOperaII LEFT JOIN Staff ON zOperaII.wn_ninum = Staff.StaffID
WHERE (((Staff.StaffID) Is Null));

By the nature of the query, I’m only appending records where the key field does not already exist in the destination table. However, I am receiving the error message “can’t append all records….. didn’t add 140 records .. due to key violations.

There are no other unique fields in the destination table (I checked by looking at the indexes).

Does any one have any suggestions?

Many thanks

Amanda

Reply | Quote

Replies

Does the Staff table contain other fields whose Required property is set to Yes, or fields with a Validation Rule?

Reply | Quote

Hi Hans,

That’s the thing, there’s no validation rules and no required fields. There are a couple of fields that won’t allow zero length, could that be it?

Many thanks

Amanda

Reply | Quote

I don’t think we can solve this without seeing the database. Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Hans,

Sorry I didn’t reply, I’ve been away. Unfortunately the problem remains. Something weird happened when I tried to send you a stripped version of the database. I created a new database file. Imported the 2 relevant tables and the append query, however when I ran the append query in the new database, it worked no problems. It still is not working however in my database. This leads me to think that it might be something to do with relationships, but I don’t know why.

Will I send you the stripped version, which is working fine?

Many thanks,

Reply | Quote

There’s not much point in attaching a version without the problem.

You could try the following:
– Create a new blank database.
– Import all database objects from the problem database into the new database.
– Set the startup options, and if necessary, non-standard references in the Visual Basic Editor.
If the problem doesn’t occur in the new database, use that one from now on.
If it does occur, try removing elements one by one, and try to find out which step makes the problem disappear.

Reply | Quote

Thanks for your help. I’ve just got to the bottom of it.

The source data did not have a department field, where as the destination table does have a department field – which is linked to a department table with enforced referentiall integrity. When I changed the link to remove referential integrity it worked fine.

Thanks again.

Amanda

Reply | Quote

I was just working on something like this yesterday and had the same problems.

Create a query using the “Find Unmatched” wizard. It will ask you to put in the two tables, create the link property, and then you can view the result.

Next, create yet another query, this time an Append query. Put the query from the wizard inside the append query. Put all the fields in that you want appended. It should work without errors.

Mark

Reply | Quote

Just tried it but it didn’t work. Thanks for the suggestion, but exactly the same thing happened.

Any other ideas?

Amanda

Reply | Quote

Well, once you create the find unmatched query using the wizard, try running it to see how many records it comes back with. Then check to see if any of those are in the destination table, there shouldn’t be.

Thanks,
Mark

Reply | Quote

Do you have any fields which have their index set to No Duplicates?
This one sometimes gets overlooked because the setting is near the bottom of the table design screen.

Reply | Quote

If there is an AUTONUMBER field, try removing it from the query.

Reply | Quote