Append Query Fails: 'Key Violations' (1 Viewer)

LambtonWorm

Registered User.
Local time
Today, 08:15
Joined
Jun 24, 2012
Messages
12
Hi guys

An Access 2003 append query is failing due to 'key violations'. It is meant to append data from a linked csv file to a linked table in a SQL Server Database. The error message reads:
"MS Access...didn't add 1329 records to the table due to key violations.."
There are exactly 1329 records returned by the query, so this is all of them.

I cloned both the Access and SQL databases from the original production one, where presumably there is no such error. In particular I scripted the Primary and foreign keys from the original to the copy SQL SB.

I'm puzzled by the fact that in the SQL code nothing is being inserted into the target table primary key field (called 'Scrit_ID'). This must be the source of the problem, but why doesn't this cause a problem in Production?

Here's the SQL in Access
Code:
PARAMETERS [Please Enter Data Date (dd/mm/yyyy):] DateTime;
 
INSERT INTO DEV_dbo_tbl_0000_SCRITTURA ( Scrit_InputDte, Scrit_InputOwner, Scrit_DealReference, Scrit_DataDte, Scrit_CounterpartyLongName, Scrit_CounterpartyID, Scrit_CounterpartyReference, Scrit_SalesPerson, Scrit_TemplateName, Scrit_ProductCategory, Scrit_ProductType, Scrit_DocEvent, Scrit_TradeType, Scrit_TradeStatus, Scrit_TradeDate, Scrit_ArrivalDate, Scrit_EventType, Scrit_ConfirmationMedium, Scrit_Portfolio, Scrit_CurrentManualQueue, Scrit_OurPartyLegalID, Scrit_TradeAge )
 
SELECT Now() AS InputDte, [Enter Username:] AS UserName, iif(isnull([Deal Reference]),'',[Deal Reference]) AS Expr1, [Please Enter Data Date (dd/mm/yyyy):] AS DataDte, lnk_Scrittura.[Counterparty Long Name], lnk_Scrittura.[Counterparty ID], lnk_Scrittura.[Counterparty Reference], lnk_Scrittura.SalesPerson, lnk_Scrittura.[Template Name], lnk_Scrittura.[Product Category], lnk_Scrittura.[Product Type], lnk_Scrittura.Field6, lnk_Scrittura.[Trade Type], lnk_Scrittura.[Trade Status], lnk_Scrittura.[Trade Date], lnk_Scrittura.[Scrittura Arrival Date], lnk_Scrittura.[Event Type], lnk_Scrittura.[Confirmation Medium], lnk_Scrittura.Portfolio, lnk_Scrittura.[Current Manual Queue], lnk_Scrittura.[Our Party Legal ID], lnk_Scrittura.[Trade Age]
 
FROM lnk_Scrittura;

Any ideas?
thanks for reading
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:15
Joined
Jan 23, 2006
Messages
15,364
Key violations suggests these records already exist in your table.
Did you try to list the key values in your table before doing the Append?
 

LambtonWorm

Registered User.
Local time
Today, 08:15
Joined
Jun 24, 2012
Messages
12
hi jdraw, thanks for writing.

The records themselves don't exist, [I ran a query to check] but perhaps the primary keys that Access is assigning to the new records by default do exist? Probably 1,2,3,4,5,... I'm guessing.

The primary keys in the target table look like an autonumbers in terms of the data, however I don't believe that Access autonumbers exist in SQL server do they. I'm so confused about where these key numbers even come from.

I'm going to have a gentle play with the production DBs to see if/how it works there.
 

LambtonWorm

Registered User.
Local time
Today, 08:15
Joined
Jun 24, 2012
Messages
12
SOLVED:

I fixed the problem by just deleting the table from my copy of the database, and scripting the original properly to regenerate (instead of just 'importing').

Equally, I think I could have achieved the same thing by changing 'IsIdentity' in the 'Identity Specification' of the table properties in SQLS to Yes.
 

Users who are viewing this thread

Top Bottom