# Database Discussions > Microsoft Access >  Append query key violations

## trandel

Hi

Am at the end of my knowledge!

I am getting the dreaded "key violations" message on an Append query - every single record on the input table is rejected.

I have checked all the indexes on the target table and they are all OK.

Does anyone have any ideas? Its amazing how many hits you get surfing but I havent found one that works for me.

This is so simple - no VBA, no macro --- just a simple append  query of a few fields on the input data to a large master table

Any ideas very welcome

Regards

Tony Randell

----------


## GolferGuy

Are the keys from the input table already in the destination table?  Are you supplying the entire key (if multiple fields in the key) from the input table?  If neither of these ideas solves your problem, then you next post should be what the key fields are in the destination table, what the fields are from the input table that are being used to fill the key fields in the destination table.  Give us both the field names and the field definitions.

----------


## Allan Murphy

Please post the exact message displayed. From memory I think the message shows how many records were not appended for various conditions. You maybe appending blank fields, incorrect data type etc.

----------


## trandel

Hi

The full msg is attached as a screen shot.

The input table does not supply any key as the object table has an Autonumber key.

Regards

Tony Randell

----------


## GolferGuy

I don't see any attachment to the post you just posted.

----------


## trandel

Hi

The msg was attached I thought via manage attachements - trying again.

The no of records rejected equals the entire file

Regards


Tony Randell

----------


## trandel

How do you attach a jpg?

----------


## GolferGuy

Use the "Go Advanced" button just below the "Quick Reply" area.  In the "header" area of the message field, you should see a paper clip icon.  Click the paper clip icon.  Then in the manage attachments form, click the "Browse..." button.  Navigate to the image on your computer, select it and click the "Open" button.  Then, before leaving the "Manage Attachments" form, click the "Upload" button.  I have done all that is documented here, and there should be an attachment to this post which will be a small image of Snoppy.  Just to be sure, I clicked the "Submit Reply" button that is below the "Additional Options" area rather than the "Submit Reply" button that is below the typing area for your post.

----------


## trandel

Trying again

----------


## GolferGuy

If you preview your post you will be able to see if anything is attached or not.  Of course, you could also, carefully, retype the message you are trying to attach.  That might just be easier for all for you to take the time to do that typing of the message if the attachment process is not working for you.

----------


## trandel

Again and again - why do I have to sign in all the time?

----------


## GolferGuy

Tony, let's just stick to one problem at a time.  Signing in type of stuff should be directed to the forum administrator.

----------


## trandel

Its the last time

BTW all it says is that the entire file has been rejected due to key violations

File attachment does not work me?

----------


## trandel

It worked!

----------


## GolferGuy

What worked?  The query or the attachment?
By the way, having seen the attachment, there is a ton of information in the error message rather than what you "quoted."  The message states that Access "can't append all the records in the append query."  That means that not ALL the records were appended.  It does not mean that ALL the records were rejected.  When it states that "not all the records were appended." that means that some records were not appended.  Then the message gives you the count of the records that were not appended and why they were not appended.  That is why I asked for you to "carefully" retype the message so we could see what the message actually says.  It is impossible (usually) to help someone with only a small portion of the information.  And even harder to help someone that is not sharing ALL the information that is being requested.  If I am sounding a little unhappy here, it is because I have been using my time to try to help you, but you have not been careful of my time.

Now, going back to something I asked for in my first reply to you.


> If neither of these ideas solves your problem, then you next post should be what the key fields are in the destination table, what the fields are from the input table that are being used to fill the key fields in the destination table. Give us both the field names and the field definitions.


  If you want help from me, then answer the questions.  If you don't care about me helping, then don't answer the questions.

----------


## trandel

Hi

Thanks for helping me and I am sorry that I have misled you in any way:

1. There 2027 records in the input table

2. The output table has an autonumber primary key and should simply add records - even if I had run the append query twice I would then have duplicates

3. The append query fails 2027 records with a key violation - therefore none were added.

I made a simple copy of the two files involved but stripped out most of the records. Cust na in the target file and Append input is the input file. Now you should be able to see all the field definitions. BTW, all the fields on the input are used to update the Cust na.

File will be sent soonest as I need to zip it 

It worked simply meant that following your instructions I finally added the JPG

Regards

Tony Randel

----------


## trandel

A zipped copy of the two files is attached.

----------


## Allan Murphy

Please attach a zip copy of your tables - DO NOT DELETE any records. Also, the queries or code used to append the records to Cust na.

----------


## trandel

Hi 

There seems to be 100k limit on attachments - I tried to send them as a zipped small db but with full tables but they are way in excess of the limit. 

The Cust master is 4899 records and the input is 2027 records. There 2 queries to select and make the input table and then one more to actually do the append. Total zipsize is 1.3MB The original file for importing is 3245 before stripping some duplicates out.

How can I get it up to you?

Regards

Tony R

----------


## Allan Murphy

Tony

Would you post a ZIPPED copy, in access 2003 format, of the full tables and queries to my email address below? Also, list the order that you use the queries.

I will have a look at it during the next few days.

----------


## trandel

Hi

I just tested the append on the reduced size db and it is working. There must be a problem with relationships on the main database.

I will need to investigate more ............... pse ignore this problem for now

Regards
Tony Randell

----------


## GolferGuy

Tony,
Having read the posting today, I'm thinking that you might have a field in the master table, other than the primary key, that is indexed, and will not allow duplicates.  Look at the index properties window to see what fields are indexed in the table.  To see the Index properties window, put the table into design mode, then use the View menu, and the Indexes option.

----------


## trandel

Hi

Thanks so much for your help - it was a relationship problem on another indexed field (numeric) in the Customer and was not needed in this application - it allowed duplicates as well. I added it to the input file with a default value and the append now works.

All your ideas helped me to rethink this after realising that the stripped down version worked - it just had to be a relationship issue!

Regards

Tony Randell

----------


## Allan Murphy

I am glad that you have solved your problem.

----------

