Append Query with Key Violation

[chuckduarte]

I'm appending to a new table and I get a key violation. It's not the data from the source table nor is it the destination table to same columns. I created the destination table from the source so everything matches. If I do not include any referential links where each table stand alone. Everything works. If I add the primary key it still works. I can add the relationship PK from Main Table and place the FK in the new table with the exception that the Enforce Referential Integrity check box (Unchecked) it works. Once I add the FK to the destination table. I get the key violation. I have no keys in my source table. Only in my destination table. So why is this happening? I also have my FK set to "Index = Yes (Duplicates OK). I don't know what i'm missing? Also compacting and repair has no affect; thought I'd try it. Don't know if it matters the source data is imported from Excel into the source table.

 

[The_Doc_Man]

Not sure where you are adding the record, but if this is a JOIN query, you might be trying to re-add (re-insert) the PK from the main table. If that is not what you are doing, try to describe better what it is that you ARE doing because it is possible that it confused me.

 

[GPGeorge]

I'm appending to a new table and I get a key violation. It's not the data from the source table nor is it the destination table to same columns. I created the destination table from the source so everything matches. If I do not include any referential links where each table stand alone. Everything works. If I add the primary key it still works. I can add the relationship PK from Main Table and place the FK in the new table with the exception that the Enforce Referential Integrity check box (Unchecked) it works. Once I add the FK to the destination table. I get the key violation. I have no keys in my source table. Only in my destination table. So why is this happening? I also have my FK set to "Index = Yes (Duplicates OK). I don't know what i'm missing? Also compacting and repair has no affect; thought I'd try it. Don't know if it matters the source data is imported from Excel into the source table.

Often, SEEING the SQL in question goes a long way to providing required information for potential responders to respond. Without that, it's a guessing game. So, if you can provide the SQL from the query, it could help.

Thank you.

 

[gemma-the-husky]

Maybe it's a different key. Maybe it's a blank lookup value for example.

What's the specific error and code?

Can you run this as a query so that most records get added, and then find which record is missing.

 

[chuckduarte]

Ok Sorry for the confusion without pics.

Let me reexplain: The "ImportExcelTemp" Table is an import from excel using the "ImportExcelSpreadsheet" Form. When I Close the form (VBA Shown) it runs the Append Query "TempBingoTransfer (SQL Shown) to move the data from"ImportExcelTemp" table to the BingoUpload table. If I do not check "Enforce Referential Data" it will work however I don't get my reference table ID (TCTODataID). If I check the box that is when I get the Key Violation.

Hope this helps make things clearer.

Let me know if there are additional questions. Thanks

 

[Gasman]

Well you do not appear to be populating TCTODataID??

 

[chuckduarte]

Well you do not appear to be populating TCTODataID??

That is correct and I can't figure out why...

 

[jdraw]

Make BingoUploadId the PK of the table as a start
Every table should have a PK that identifies each record in the table.

 

[chuckduarte]

Make BingoUploadId the PK of the table as a start
Every table should have a PK that identifies each record in the table.

Ok BingoUploadID is there so the ImportExcelTemp doesn't have a key since it's importing data from a spreadsheet. When I append to BingoUpload table I'm only transferring the 4 columns. It's only temp data then I delete the data. The only time I have the key violation is with the FK TCTODataID. Maybe I'm missing something not sure...

 

[jdraw]

??? What do you expect as the result of running your query??

You saying these are linked/joined on TCTODataID, but there is no TCTODataID in your incoming table??
I think you have us all confused.

 

[ebs17]

TCTODataID in BingoUpload is primary key or foreign key in the desired relationship?

Your append query only serves one of the tables according to the relationship, but the key doesn't get any values assigned.

Of course, a primary key field must not remain empty, as this would logically result in a key error. A foreign key field should remain empty even if referential integrity is set (but that makes no sense in terms of content). However, if you have set a default value of 0 for the foreign key field, this 0 becomes the key with the new data records, and if there is no primary key 0 in the associated table, the set referential integrity promptly criticizes this as an error and displays it.

 

[Gasman]

That is correct and I can't figure out why...

Well that is easy. You are not supplying any value for that field?

 

[chuckduarte]

Well that is easy. You are not supplying any value for that field?

Exactly when I close the import form to run the query it's not passing the FK TCTODataID for the data to associate it with the BingoUpdate form to which it goes. As in what Member EBS17 stated I did have a default 0 set. Once I removed that. It appended to the table and the key violation went away.
Now I just need to have the FK passed to the data appended.
I'm thinking I need to run the append query from the Bingo Upload Form and use the close form event from the ImportExcelSpreadsheet to trigger it. I'm not sure how to do that.

 

[Gasman]

I would probably set a TempVar() due to your SQL code and use that in the SQL code.
However was was pointing out the fact that, that value was not even included in that code, so could not be used?

 

[chuckduarte]

Thanks I was able to get everything resolved and working perfectly

 

[Gasman]

Thanks I was able to get everything resolved and working perfectly

Well please show your code or say what you did.
It might help someone else in the future, and that is what these forums are about?

 

[Gasman]

Now crossposted at https://www.utteraccess.com/topics/2064452?post=unread