Please help with append data

[Al]

I have a table in sql 2000 backend (be) and Access front end. I deleted the
data from the "be" table. When I tried to append the same data again I got
error message saying that:" Microsoft office Access did not add 1262
Record(s) to table due to key violations". What does that mean?
here is my sql statment:
*************************************
INSERT INTO tblLocationTracking ( ProjCode, DeptCode, ActivationPhaseID,
MovePhaseID, LocationType, AssignedTo, FirstName, LastName, MiddleInitial,
FullName, JobTitle, JobCode, HRPositionID, Originbl_id, Originfl_id,
OriginLocation, OriginWorkStationNo, OriginSqFt, Destbl_id, Destfl_id,
DestLocation, DestWorkStationNo, DestSQFT, Emp_Comments, sysRowOwner,
sysCreateUser, sysCreateDate, sysModUser, sysModDate )
SELECT tblDataCleanup.ProjCode, tblDataCleanup.DeptCode,
tblDataCleanup.ActivationPhaseID, tblDataCleanup.MovePhaseID,
tblDataCleanup.LocationType, tblDataCleanup.AssignedTo,
tblDataCleanup.FirstName, tblDataCleanup.LastName,
tblDataCleanup.MiddleInitial, tblDataCleanup.FullName,
tblDataCleanup.JobTitle, tblDataCleanup.JobCode, tblDataCleanup.HRPositionID,
tblDataCleanup.Origintbl_id, tblDataCleanup.Originfl_id,
tblDataCleanup.OriginLocation, tblDataCleanup.OriginWorkStationNo,
tblDataCleanup.OriginSqFt, tblDataCleanup.Destbl_id,
tblDataCleanup.Destfl_id, tblDataCleanup.DestLocation,
tblDataCleanup.DestWorkStationNo, tblDataCleanup.DestSqFt,
tblDataCleanup.Emp_Comments, tblDataCleanup.sysRowOwner,
tblDataCleanup.sysCreateUser, tblDataCleanup.sysCreateDate,
tblDataCleanup.sysModUser, tblDataCleanup.sysModDate
FROM tblDataCleanup;
******************************
when I tried to run a sql script using the sql query analyzer, I got the
following error:
"Server: Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.".
Could some one please help.
Thanks
Al

 

[Pendragon]

My encounters with key violations usually relate to my primary key, though
when appending data in a query (as opposed to Add Record on a form) I
typically don't encounter the error.

Try compacting and repairing the back end database to reset the primary key.
You should be able to run the append query. If not, then you have some
index with no duplicates allowed creating the key violation.

 

[John W. Vinson]

I have a table in sql 2000 backend (be) and Access front end. I deleted the
data from the "be" table. When I tried to append the same data again I got
error message saying that:" Microsoft office Access did not add 1262
Record(s) to table due to key violations". What does that mean?
here is my sql statment:
*************************************
INSERT INTO tblLocationTracking ( ProjCode, DeptCode, ActivationPhaseID,
MovePhaseID, LocationType, AssignedTo, FirstName, LastName, MiddleInitial,
FullName, JobTitle, JobCode, HRPositionID, Originbl_id, Originfl_id,
OriginLocation, OriginWorkStationNo, OriginSqFt, Destbl_id, Destfl_id,
DestLocation, DestWorkStationNo, DestSQFT, Emp_Comments, sysRowOwner,
sysCreateUser, sysCreateDate, sysModUser, sysModDate )
SELECT tblDataCleanup.ProjCode, tblDataCleanup.DeptCode,
tblDataCleanup.ActivationPhaseID, tblDataCleanup.MovePhaseID,
tblDataCleanup.LocationType, tblDataCleanup.AssignedTo,
tblDataCleanup.FirstName, tblDataCleanup.LastName,
tblDataCleanup.MiddleInitial, tblDataCleanup.FullName,
tblDataCleanup.JobTitle, tblDataCleanup.JobCode, tblDataCleanup.HRPositionID,
tblDataCleanup.Origintbl_id, tblDataCleanup.Originfl_id,
tblDataCleanup.OriginLocation, tblDataCleanup.OriginWorkStationNo,
tblDataCleanup.OriginSqFt, tblDataCleanup.Destbl_id,
tblDataCleanup.Destfl_id, tblDataCleanup.DestLocation,
tblDataCleanup.DestWorkStationNo, tblDataCleanup.DestSqFt,
tblDataCleanup.Emp_Comments, tblDataCleanup.sysRowOwner,
tblDataCleanup.sysCreateUser, tblDataCleanup.sysCreateDate,
tblDataCleanup.sysModUser, tblDataCleanup.sysModDate
FROM tblDataCleanup;

I would guess that the data you're adding contains invalid values for some
foreign keys - ProjCode, DeptCode, ActivationPhaseID, MovePhaseID, etc.

If you try to add a record with a ProjCode of "XYZ" to a table, and there is
an enforced relationship to a table of ProjCodes which does not have a record
containing XYZ, you'll get this error.

I hope you have a backup of the backend before the deletion... :-{(

John W. Vinson [MVP]

 

[Al]

Yes John, thanks. I found out that I was trying to insert data into a column
that
is longer than it's defined length, thus if the insert were performed the
data for that column would be truncated so the insert was rejected. I fixed
the problem and it worked. thanks again.
Al