Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-16-2014, 09:13 AM   #1
LambtonWorm
Newly Registered User
 
Join Date: Jun 2012
Posts: 12
Thanks: 11
Thanked 0 Times in 0 Posts
LambtonWorm is on a distinguished road
Append Query Fails: 'Key Violations'

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

LambtonWorm is offline   Reply With Quote
Old 07-16-2014, 09:39 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,573
Thanks: 57
Thanked 1,875 Times in 1,825 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Append Query Fails: 'Key Violations'

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?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
LambtonWorm (07-17-2014)
Old 07-17-2014, 01:47 AM   #3
LambtonWorm
Newly Registered User
 
Join Date: Jun 2012
Posts: 12
Thanks: 11
Thanked 0 Times in 0 Posts
LambtonWorm is on a distinguished road
Re: Append Query Fails: 'Key Violations'

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 is offline   Reply With Quote
Old 07-17-2014, 09:10 AM   #4
LambtonWorm
Newly Registered User
 
Join Date: Jun 2012
Posts: 12
Thanks: 11
Thanked 0 Times in 0 Posts
LambtonWorm is on a distinguished road
Re: Append Query Fails: 'Key Violations'

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.

LambtonWorm is offline   Reply With Quote
Reply

Tags
append , key , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Key violations with Append query Sam Summers Queries 9 02-17-2014 05:19 AM
Append Query Validation Rule Violations Jayne Queries 1 07-31-2013 12:34 PM
Append query failed due to key violations klar Queries 5 03-09-2012 09:16 PM
Append query key violations sal Queries 3 06-17-2011 08:13 PM
Append Query Key Violations - Hairy One... cclambie Queries 6 07-03-2006 07:15 AM




All times are GMT -8. The time now is 11:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World