Append Query Key Violation

[oleBucky]

I am specifically interested in help regarding what causes a key violation. After numerous attempts, my append query continually fails as a result of this incidious key violation. Many, many trials at resolving the violation have failed indicating I am clearly on the incorrect path. Might someone offer insight as to the cause of this problem as I feel confident there is something basic I am missing?

Here's the background:



I am attempting to append a table with data from Excel. I created a table by importing the data with the "Get External Data" tool. The new data table has 56 records each with 33 fields.

I made certain all the fields I want to append (I am not appending all the fields in the recipient table) are the same Data Type, down to the specific field properties, in the donor and in the recipient tables. Both tables have independent primary keys with an AutoNumber data type. In the append query, I did not include either primary key per the action query instructions on the MS website. This was all to no avail.

Every time I attempt another iteration, I get the same error: "... and it didn't add 56 record(s) to the table due to key violations...". I always choose not to run the action query anyway.

Signed,
Totally Frustrated oleBucky

[jzwp11]

If there is a required field(s) in the receiving table and the imported data does not have a value or the field is not present, the append will fail. Also, you will want to check if there are any joined fields between the receiving table and other tables in your database. You will also want to check any fields where duplicates are not allowed.

[oleBucky]

Thanks so much for the reply.

None of the fields in the recipient table are required. I double checked again. Should the primary key in the recipient table be required?

The recieving table does have two fields joined to other tables (Is this the field property titled "Indexed"?). Neither is "Required" and both allow duplicates. However, I am not attempting to append data to these joined fields. Might this be an issue?

[jzwp11]

None of the fields in the recipient table are required. I double checked again. Should the primary key in the recipient table be required?

The primary key should be a required field but since you said it is autonumber is should handle the append with no problems.

The recieving table does have two fields joined to other tables (Is this the field property titled "Indexed"?). Neither is "Required" and both allow duplicates. However, I am not attempting to append data to these joined fields. Might this be an issue?

Depending on the type of joins with the other tables, then it could be an issue. You could temporarily remove the joins in the relationship window and run the query to see if the append will work. As a word of caution, you may not be able to re-establish the joins if there is nothing in the respective fields.

[oleBucky]

Great! That worked. I did have the issue with re-establishing the relationships as you suggested. However, it only forced me into some db maintenance I've been deferring. After the maintenance, the relationships are back up and working.

Thanks a million and SOLVED!

[jzwp11]

You're welcome, glad to help out.

[navyjax2]

This might help someone.

Had this, too - the way I troubleshot it - I started entering data from the spreadsheet that was failing into each field in the same record, one by one, making sure to click in the next empty record below between each field entry, to commit the field/row to the database. I found it fail on a field that was linked by relationship to 2 other tables - I thought it failed because of that relationship, at first, but on further investigation, I found that field was also set up to not allow duplicates (which is really why it did not allow the entry).

I don't know what would've been easier - checking every field to see if any denied duplicates, or doing what I did, but it worked to find my error, and I think is good when you're pulling your hair out on this one (which is probably pretty easy to do).

-Tom