Append Query Fails: 'Key Violations'

[LambtonWorm]

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

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]

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]

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]

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.