Append Query Key Violation

[sudo]

I've read other threads in this forum to do with key violations, but none of them help me with my particular problem.

I import data from a text file into a temporary table in my Access database. I use a temporary table because the number of columns and titles in my text file do not match the Access target table. So, I thought I'd use an append query to transfer some of the columns and all of the rows from my source table (the temporary imported data table) to the target table.

My target table has a primary key that is autonumber, but the append query does not try to add anything into this field - meaning I let Access fill in this field by itself. The temporary source table does not have a primary key. Still, when I run the query, I get the key violation message and no records are appended. I narrowed the problem down to one field in my target table, called MsdnID. This is not an autonumber field or a primary key - it's a regular text field with indexing set to "Yes (duplicates ok)". The append query tries to append data from one of the source fields (a text field). When I remove the column in the query that tries to append to the target MsdnID field, the append query works, appending data for all fields except MsdnID, which is left blank. MsdnID is a field that I use to set relationships between several tables. As mentioned, in the target table, MsdnID is not a primary key and allows duplicate values. However, in another table (not directly referenced by the append query), MsdnID is a primary key.

When the append query didn't work, I thought I needed to specify a join between my source and target tables. So, I added the target table to append query and added a join between the source field and MsdnID. I tried both the one-to-one join and the many-to-one join. Netiher worked. I still get the key violation message. I'm out of ideas.

How do I fix this? I don't understand why I get a key violation message when the target field isn't a primary key in the target table.

Thanks

 

[EMP]

Most likely you have set up Enforce Referential Integrity in the one-to-many relationship and the temporary table happens to contain some MsdnIDs that do not exist in the one-side table.

To resolve the problem, try appending the new MsdnIDs to the one-side table first.

 

[sudo]

Thanks EMP. Your comment made me realise it was indeed the relationship issue that caused my problem and why. Instead of getting the target table MsdnID data from the temporary source file, I tried getting it from another permanent table that has the MsdnID field as it's primary key. Just so it's a bit clearer what's going on, the temporary source table can have many rows, but will only ever have 1 value in the field that I wanted to transfer to the target MsdinID field. It's the other fields in the temporary table that have differing values. In a previous data transfer step, I transfer data (including the MsdnID field) from the temporary table into this other permanent table (the one where MsdnID is the primary key and that has a one-to-many relationship with my target table). In other words, my value for MsdnID is already in another table in the database and I adjusted my append query to get the value from that table rather than the temporary source table. All the other fields being appended still get their info from the temporary source table. And......TA DA......it works fine now. Also, in the append query, I realised I didn't need to create a join between my source table and the target table, so I took that out. The append query now only shows the temporary source table.

Thanks again.

PS: the reason I described this in such detail is that it may help someone else who runs into the same problem. I only hope I've described it in a way that's not too confusing. Cheers.

 

[avacess]

Sudo,

Thank you for your last post. Indeed you helped other users (me) with your post. I had the same problem with the Referential Integrity. I couldn't update a field from my table (which is a primary key field) into my target table since referential integrity was still valid. Deleting it, I was able to append the required field.

thanks

 

[avacess]

Thanks Sudo, you helped me with your last post.