Key Violation Error on an Append Query

  • Thread starter Thread starter BC
  • Start date Start date
B

BC

I am having a problem running an append query that sends the data in
an Access database table to a SQL Server using a linked table. When I
activate the query I get a warning message saying that "(#)record(s)
have key violations" and if I want to run the query anyway. If I
select 'YES', nothing happens. There is only one key and that is not
a part of the append query. Any suggestions on what the problem might
be? I have used this query in the past and overridden the warning
message - I am not sure what has changed.

Thanks for your help.
 
When you append records, there is always a key in a properly designed table.
The violations can take several forms, but usually mean duplicate keys. If
you are using a number for a key, make sure it is a long integer or a
double, or it is possible that you will have used all the possible numbers
(an integer only allows -32768 to + 32767)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
The Access table primary key is in an auto incredment field that is a
long integer, and is set up little this:
DutDataId as AutoNumber(Long Int) - Primary Key
DutSerialNumber as Long Int
StationID as TEXT
RunNumber as Long Int
.....(all other fields are not required)

All of this data is going to a SQL table set up as:
DutDataId as INT - Primary Key
DutSerialNumber as INT
StationID as nvarchar
RunNumber as INT
.......(all other fields allow NULLS)

The query sends all of the Access data except for the primary key to
the SQL table. There shouldn't be any key violations since no data
from the key is in the query. Still not sure what the problem is....
 
Bryan Crouse said:
The query sends all of the Access data except for the primary key to
the SQL table. There shouldn't be any key violations since no data
from the key is in the query. Still not sure what the problem is....
Click to expand...

Is there another field on the SQL Server database which has a unique
index attribute set? Or combination of fields?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
StationID sounds like it might be a foreign key relating this table to
another table. If so, you'll get a key violation if you attempt to write a
value to that column that does not exist in the related table.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
--Here is my total append query in Access:

INSERT INTO dbo_TempCompDutData ( DutSerialNumber, StationId,
RunNumber, DutInfo1, DutInfo2, DutInfo3, DutUniversalRevision,
DutSpecificRevision, DutSoftwareRevision, DutHardwareRevision, DutTag,
Oven, Rack, Channel, Calibrator, CalibratorDue, Resistor, ResistorDue,
CompPassed, VerifyPassed, FinalCalPassed, Passed, Notes )
SELECT TempCompDutData.DutSerialNumber, TempCompDutData.StationId,
TempCompDutData.RunNumber, TempCompDutData.DutInfo1,
TempCompDutData.DutInfo2, TempCompDutData.DutInfo3,
TempCompDutData.DutUniversalRevision,
TempCompDutData.DutSpecificRevision,
TempCompDutData.DutSoftwareRevision,
TempCompDutData.DutHardwareRevision, TempCompDutData.DutTag,
TempCompDutData.Oven, TempCompDutData.Rack, TempCompDutData.Channel,
TempCompDutData.Calibrator, TempCompDutData.CalibratorDue,
TempCompDutData.Resistor, TempCompDutData.ResistorDue,
TempCompDutData.CompPassed, TempCompDutData.VerifyPassed,
TempCompDutData.FinalCalPassed, TempCompDutData.Passed,
TempCompDutData.Notes
FROM TempCompDutData;

--dbo_TempCompDutData is a linked table in Access, to the SQL table.
--TempCompDutData is the Access Table.


--Here is the total structure of the SQL Server 2000 table:

CREATE TABLE [TempCompDutData] (
[DutDataId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[DutSerialNumber] [int] NOT NULL ,
[StationId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[RunNumber] [int] NOT NULL ,
[DutInfo1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutInfo2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutInfo3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutUniversalRevision] [int] NULL ,
[DutSpecificRevision] [int] NULL ,
[DutSoftwareRevision] [int] NULL ,
[DutHardwareRevision] [int] NULL ,
[DutTag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Oven] [int] NULL ,
[Rack] [int] NULL ,
[Channel] [int] NULL ,
[Calibrator] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CalibratorDue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Resistor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ResistorDue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompPassed] [int] NULL ,
[VerifyPassed] [int] NULL ,
[FinalCalPassed] [int] NULL ,
[Passed] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblTempCompDutData] PRIMARY KEY CLUSTERED
(
[DutSerialNumber],
[StationId],
[RunNumber]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_TempCompDutData_TempCompRunData] FOREIGN KEY
(
[StationId],
[RunNumber]
) REFERENCES [TempCompRunData] (
[StationId],
[RunNumber]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am not sure how to discribe the Access table other than it is
supposed to mirror the SQL table....
 
Then the combination of StationId and RunNumber is a foreign key to table
TempCompRunData, and you'll get a key violation if the values assigned to
these two fields do not match the values of a record in TempCompRunData.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


BC said:
--Here is my total append query in Access:

INSERT INTO dbo_TempCompDutData ( DutSerialNumber, StationId,
RunNumber, DutInfo1, DutInfo2, DutInfo3, DutUniversalRevision,
DutSpecificRevision, DutSoftwareRevision, DutHardwareRevision, DutTag,
Oven, Rack, Channel, Calibrator, CalibratorDue, Resistor, ResistorDue,
CompPassed, VerifyPassed, FinalCalPassed, Passed, Notes )
SELECT TempCompDutData.DutSerialNumber, TempCompDutData.StationId,
TempCompDutData.RunNumber, TempCompDutData.DutInfo1,
TempCompDutData.DutInfo2, TempCompDutData.DutInfo3,
TempCompDutData.DutUniversalRevision,
TempCompDutData.DutSpecificRevision,
TempCompDutData.DutSoftwareRevision,
TempCompDutData.DutHardwareRevision, TempCompDutData.DutTag,
TempCompDutData.Oven, TempCompDutData.Rack, TempCompDutData.Channel,
TempCompDutData.Calibrator, TempCompDutData.CalibratorDue,
TempCompDutData.Resistor, TempCompDutData.ResistorDue,
TempCompDutData.CompPassed, TempCompDutData.VerifyPassed,
TempCompDutData.FinalCalPassed, TempCompDutData.Passed,
TempCompDutData.Notes
FROM TempCompDutData;

--dbo_TempCompDutData is a linked table in Access, to the SQL table.
--TempCompDutData is the Access Table.


--Here is the total structure of the SQL Server 2000 table:

CREATE TABLE [TempCompDutData] (
[DutDataId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[DutSerialNumber] [int] NOT NULL ,
[StationId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[RunNumber] [int] NOT NULL ,
[DutInfo1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutInfo2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutInfo3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DutUniversalRevision] [int] NULL ,
[DutSpecificRevision] [int] NULL ,
[DutSoftwareRevision] [int] NULL ,
[DutHardwareRevision] [int] NULL ,
[DutTag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Oven] [int] NULL ,
[Rack] [int] NULL ,
[Channel] [int] NULL ,
[Calibrator] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CalibratorDue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Resistor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ResistorDue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompPassed] [int] NULL ,
[VerifyPassed] [int] NULL ,
[FinalCalPassed] [int] NULL ,
[Passed] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblTempCompDutData] PRIMARY KEY CLUSTERED
(
[DutSerialNumber],
[StationId],
[RunNumber]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_TempCompDutData_TempCompRunData] FOREIGN KEY
(
[StationId],
[RunNumber]
) REFERENCES [TempCompRunData] (
[StationId],
[RunNumber]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am not sure how to discribe the Access table other than it is
supposed to mirror the SQL table....



Tony Toews said:
Is there another field on the SQL Server database which has a unique
index attribute set? Or combination of fields?

Tony
Click to expand...
Click to expand...
 
Back
Top