SQLTeam.com | Weblogs | Forums

Primary key on 2 text fields causing problems when inserting through Microsoft Access interface


#1

I have primary key on t_Id and TestNo ( both are text fields). Even though my data is unique it is failing giving me error: Call failed Error 3146.

When I try to insert using insert statement it says ..
Microsoft access can’t append all the records in the append query.
It did ‘t add 9 records to the table due to key violations.
Code to create the table ...
GO

CREATE TABLE [LMP].[tbl_TAMP_Main](
[T_ID] nvarchar NOT NULL,
[TestNo] nvarchar NOT NULL,
[vial_No] [int] NULL,
[MN_Ct] [float] NULL,
[CH_Ct] [float] NULL
PRIMARY KEY CLUSTERED
(
[T_ID] ASC,
[TestNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

data..
T_Id TestNo
3000759189-1 Test 1
3000759189-2 Test 1
3000759189-3 Test 1
3000759197-4 Test 1
3000759197-5 Test 1
3000759197-6 Test 1
3000760970-1 Test 1
3000760970-2 Test 1


#2

likely the table already has rows with some of those keys