Prevent Insert - can't append all the records in the append query

[yrstruly]

I have trouble inserting a row into a table with restrictions, how do insert a row.

Msg. "Sapo Databasis W40 can't append all the records in the append query.Sapo Databasis W40 set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 1 record(s) due to validation rule violations.


Do you want to run the action query anyway?"

CREATE TABLE TblBlocks (
    [s_Generation] LONG,
    [s_GUID] GUID,
    [s_Lineage] LONGBINARY,
    [Count] LONG,
    [Nr] TEXT(6) NOT NULL,
    [Block] TEXT(30) NOT NULL,
    [BlockUnitCodeOLD] TEXT(10),
    [BlockUnitCode] LONG,
    [Establishment] YESNO,
    [EstablishmentDate] DATETIME,
    [Maintenance] YESNO,
    [MaintenanceDate] DATETIME,
    [Location] TEXT(50),
    [PlantIDScion] TEXT(6),
    [PlantIDRoot] TEXT(6),
    [YearPlanted] TEXT(6),
    [BlockStatus] TEXT(20),
    [Trees] LONG,
    [Trees_Balance] LONG,
    [ReWorked] YESNO,
    [OtherVirus] TEXT(25),
    [Vlamsikte] TEXT(50),
    [AmPLP] TEXT(50),
    [EurPLP] TEXT(50),
    [Clold] TEXT(20),
    [Slold] TEXT(20),
    [Pnrsold] TEXT(20),
    [Pdvold] TEXT(20),
    [Gvflold] TEXT(20),
    [Apmvold] TEXT(20),
    [Gvlrold] TEXT(20),
    [TtFlower] YESNO,
    [TtFruit] YESNO,
    [SubYear] TEXT(4),
    [ResubYear] TEXT(8),
    [Supplier] TEXT(4),
    [PathLetter] DATETIME,
    [StatusReason] TEXT(20),
    [BlockType] TEXT(3),
    [OriginScion] TEXT(25),
    [OriginRoot] TEXT(25),
    [OrgBlockTypeScion] TEXT(3),
    [OrgBlockTypeRoot] TEXT(3),
    [CombinedBlockType] TEXT(3),
    [Contract] YESNO,
    [ConttractNo] TEXT(6),
    [ContractType] TEXT(12),
    [Date Signed] DATETIME,
    [LabelType] TEXT(2),
    [CultivarNoName] TEXT(25),
    [BlockAspect] SHORT,
    [SampleDate] DATETIME,
    [TestDate] DATETIME,
    [Virus] TEXT(50),
    [TestResult] TEXT(6),
    [TestComment] TEXT(255),
    [RefNo] TEXT(10),
    [Prefered BLock] YESNO,
    [AM] TEXT(10),
    [AMDate] DATETIME,
    [CommentAM] TEXT(255),
    [AGC] TEXT(10),
    [AGCDate] DATETIME,
    [CommentAGC] TEXT(255),
    [AP] TEXT(10),
    [APDate] DATETIME,
    [CommentAP] TEXT(255),
    [AR] TEXT(10),
    [ARDate] DATETIME,
    [CommentAR] TEXT(255),
    [BC] TEXT(10),
    [BCDate] DATETIME,
    [CommentBC] TEXT(255),
    [CF] TEXT(10),
    [CFDate] DATETIME,
    [CommentCF] TEXT(255),
    [CL] TEXT(10),
    [CLDate] DATETIME,
    [CommentCL] TEXT(255),
    [CR] TEXT(10),
    [CRDate] DATETIME,
    [CommentCR] TEXT(255),
    [FL] TEXT(10),
    [FLDate] DATETIME,
    [CommentFL] TEXT(255),
    [GCB] TEXT(10),
    [GCBDate] DATETIME,
    [CommentGCB] TEXT(255),
    [GD] TEXT(10),
    [GDDAte] DATETIME,
    [CommentGD] TEXT(255),
    [GF] TEXT(10),
    [GFDate] DATETIME,
    [CommentGF] TEXT(255),
    [GFL] TEXT(10),
    [GFLDate] DATETIME,
    [CommentGFL] TEXT(255),
    [GS] TEXT(10),
    [GSDate] DATETIME,
    [CommentGS] TEXT(255),
    [LP] TEXT(10),
    [LPDate] DATETIME,
    [CommentLP] TEXT(255),
    [LR] TEXT(10),
    [LRDate] DATETIME,
    [CommentLR] TEXT(255),
    [LRC] TEXT(10),
    [LRCDate] DATETIME,
    [CommentLRC] TEXT(255),
    [NS] TEXT(10),
    [NSDate] DATETIME,
    [CommentNS] TEXT(255),
    [PD] TEXT(10),
    [PDDate] DATETIME,
    [CommentPD] TEXT(255),
    [PL] TEXT(10),
    [PLDate] DATETIME,
    [CommentPL] TEXT(255),
    [PKRR] TEXT(10),
    [PKRRDate] DATETIME,
    [CommentPKRR] TEXT(255),
    [PKRV] TEXT(10),
    [PKRVDate] DATETIME,
    [CommentPKRV] TEXT(255),
    [PR] TEXT(10),
    [PRDate] DATETIME,
    [CommentPR] TEXT(255),
    [PRPM] TEXT(10),
    [PRPMDate] DATETIME,
    [CommentPRPM] TEXT(255),
    [PDW] TEXT(10),
    [PDWDate] DATETIME,
    [CommentPDW] TEXT(255),
    [QS] TEXT(10),
    [QSDate] DATETIME,
    [CommentQS] TEXT(255),
    [RL] TEXT(10),
    [RLDate] DATETIME,
    [CommentRL] TEXT(255),
    [RR] TEXT(10),
    [RRDate] DATETIME,
    [CommentRR] TEXT(255),
    [RW] TEXT(10),
    [RWDate] DATETIME,
    [CommentRW] TEXT(255),
    [SB] TEXT(10),
    [SBDate] DATETIME,
    [CommentSB] TEXT(255),
    [SD] TEXT(10),
    [SDDate] DATETIME,
    [CommentSD] TEXT(255),
    [SG] TEXT(10),
    [SGDate] DATETIME,
    [CommentSG] TEXT(255),
    [SP] TEXT(10),
    [SPDate] DATETIME,
    [CommentSP] TEXT(255),
    [SR] TEXT(10),
    [SRDate] DATETIME,
    [CommentSR] TEXT(255),
    [SS] TEXT(10),
    [SSDate] DATETIME,
    [CommentSS] TEXT(255),
    [TL] TEXT(10),
    [TLDate] DATETIME,
    [CommentTL] TEXT(255),
    [TP] TEXT(10),
    [TPDate] DATETIME,
    [CommentTP] TEXT(255),
    [VS] TEXT(10),
    [VSDate] DATETIME,
    [CommentVS] TEXT(255),
    [VY] TEXT(10),
    [VYDate] DATETIME,
    [CommentVY] TEXT(255),
    [YB] TEXT(10),
    [YBDate] DATETIME,
    [CommentYB] TEXT(255),
    [GVA] TEXT(10),
    [GVADate] DATETIME,
    [CommentGVA] TEXT(255),
    [GVB] TEXT(10),
    [GVBDate] DATETIME,
    [CommentGVB] TEXT(255),
    [PM] TEXT(255),
    [PMDate] DATETIME,
    [CommentPM] TEXT(255),
    [NoVirus] TEXT(50),
    [NoVirusDate] DATETIME,
    [CommentNoVirus] TEXT(255),
    [Date Created] DATETIME,
    [SelectBox] YESNO,
    [GPS_Block] TEXT(70),
    [Nursery_Nr] TEXT(6),
    [Graft_Ref_Nr] TEXT(70),
    [CapturedBy] TEXT(100),
    CONSTRAINT PrimaryKey PRIMARY KEY ([s_GUID])
);

[moke123]

That's a make table query not an insert or append

[Micron]

True, but changing that will still leave the issue of validation rule violation?

[yrstruly]

I want to insert, can't due to key violation.

[xps35]

So, find out what the problem is.

The key of the table created is s_GUID. It looks like you are trying to store a record with a s_GUID that already exists.

[yrstruly]

Even when i leave the auto numbers blank, it still gives that warning.

INSERTINTO TblBlocks (    s_Generation, [Count], Block, BlockUnitCodeOLD, 
    BlockUnitCode, Establishment, EstablishmentDate, Maintenance, MaintenanceDate, 
    Location, PlantIDScion, PlantIDRoot, YearPlanted, BlockStatus, Trees, 
    Trees_Balance, ReWorked, OtherVirus, Vlamsikte, AmPLP, EurPLP, Clold, Slold, 
    Pnrsold, Pdvold, Gvflold, Apmvold, Gvlrold, TtFlower, TtFruit, SubYear, 
    ResubYear, Supplier, PathLetter, StatusReason, BlockType, OriginScion, 
    OriginRoot, OrgBlockTypeScion, OrgBlockTypeRoot, CombinedBlockType, 
    Contract, ConttractNo, ContractType, [Date Signed], LabelType, CultivarNoName, 
    BlockAspect, SampleDate, TestDate, Virus, TestResult, TestComment, RefNo, 
    [Prefered BLock], AM, AMDate, CommentAM, AGC, AGCDate, CommentAGC, AP, APDate, 
    CommentAP, AR, ARDate, CommentAR, BC, BCDate, CommentBC, CF, CFDate, 
    CommentCF, CL, CLDate, CommentCL, CR, CRDate, CommentCR, FL, FLDate, 
    CommentFL, GCB, GCBDate, CommentGCB, GD, GDDAte, CommentGD, GF, GFDate, 
    CommentGF, GFL, GFLDate, CommentGFL, GS, GSDate, CommentGS, LP, LPDate, 
    CommentLP, LR, LRDate, CommentLR, LRC, LRCDate, CommentLRC, NS, NSDate, 
    CommentNS, PD, PDDate, CommentPD, PL, PLDate, CommentPL, PKRR, PKRRDate, 
    CommentPKRR, PKRV, PKRVDate, CommentPKRV, PR, PRDate, CommentPR, PRPM, 
    PRPMDate, CommentPRPM, PDW, PDWDate, CommentPDW, QS, QSDate, CommentQS, RL, 
    RLDate, CommentRL, RR, RRDate, CommentRR, RW, RWDate, CommentRW, SB, SBDate, 
    CommentSB, SD, SDDate, CommentSD, SG, SGDate, CommentSG, SP, SPDate, 
    CommentSP, SR, SRDate, CommentSR, SS, SSDate, CommentSS, TL, TLDate, 
    CommentTL, TP, TPDate, CommentTP, VS, VSDate, CommentVS, VY, VYDate, 
    CommentVY, YB, YBDate, CommentYB, GVA, GVADate, CommentGVA, GVB, GVBDate, 
    CommentGVB, PM, PMDate, CommentPM, NoVirus, NoVirusDate, CommentNoVirus, 
    [Date Created], SelectBox, GPS_Block, Nursery_Nr, Graft_Ref_Nr
)
SELECT 
    IIF(IsNumeric(B.s_Generation), Val(B.s_Generation), 0) AS s_Generation_Default_Zero,
    B.[Count], B.Block, 
    Nz(B.BlockUnitCodeOLD, "") AS BlockUnitCodeOLD_Safe, -- Changed to empty string if NULL/blank
    B.BlockUnitCode, B.Establishment, 
    IIF(Nz(B.EstablishmentDate, "")="", NULL, CDbl(CDate(B.EstablishmentDate))) AS New_EstablishmentDate,
    B.Maintenance, 
    IIF(Nz(B.MaintenanceDate, "")="", NULL, CDate(B.MaintenanceDate)) AS New_MaintenanceDate, 
    B.Location, B.PlantIDScion, B.PlantIDRoot, B.YearPlanted, B.BlockStatus, B.Trees, 
    B.Trees_Balance, B.ReWorked, B.OtherVirus, B.Vlamsikte, B.AmPLP, B.EurPLP, B.Clold, B.Slold, 
    B.Pnrsold, B.Pdvold, B.Gvflold, B.Apmvold, B.Gvlrold, B.TtFlower, B.TtFruit, B.SubYear, 
    B.ResubYear, B.Supplier, 
    IIF(Nz(B.PathLetter, "")="", NULL, CDate(B.PathLetter)) AS New_PathLetter,
    B.StatusReason, B.BlockType, B.OriginScion, 
    B.OriginRoot, B.OrgBlockTypeScion, B.OrgBlockTypeRoot, B.CombinedBlockType, 
    B.Contract, B.ConttractNo, B.ContractType, B.[Date Signed], B.LabelType, B.CultivarNoName, 
    B.BlockAspect, 
    IIF(Nz(B.SampleDate, "")="", NULL, CDate(B.SampleDate)) AS New_SampleDate,
    IIF(Nz(B.TestDate, "")="", NULL, CDate(B.TestDate)) AS New_TestDate,
    B.Virus, B.TestResult, B.TestComment, B.RefNo, 
    B.[Prefered BLock], B.AM, B.AMDate, B.CommentAM, B.AGC, B.AGCDate, B.CommentAGC, B.AP, B.APDate, 
    B.CommentAP, B.AR, B.ARDate, B.CommentAR, B.BC, B.BCDate, B.CommentBC, B.CF, B.CFDate, 
    B.CommentCF, B.CL, B.CLDate, B.CommentCL, B.CR, B.CRDate, B.CommentCR, B.FL, B.FLDate, 
    B.CommentFL, B.GCB, B.GCBDate, B.CommentGCB, B.GD, B.GDDAte, B.CommentGD, B.GF, B.GFDate, 
    B.CommentGF, B.GFL, 
    IIF(Nz(B.GFLDate, "")="", NULL, CDate(B.GFLDate)) AS New_GFLDate,
    B.CommentGFL, B.GS, B.GSDate, B.CommentGS, B.LP, B.LPDate, B.CommentLP, B.LR, 
    IIF(Nz(B.LRDate, "")="", NULL, CDate(B.LRDate)) AS New_LRDate,
    B.CommentLR, B.LRC, B.LRCDate, B.CommentLRC, B.NS, B.NSDate, B.CommentNS, B.PD, B.PDDate, B.CommentPD, B.PL, B.PLDate, B.CommentPL, B.PKRR, B.PKRRDate, 
    B.CommentPKRR, B.PKRV, B.PKRVDate, B.CommentPKRV, B.PR, B.PRDate, B.CommentPR, B.PRPM, 
    B.PRPMDate, B.CommentPRPM, B.PDW, B.PDWDate, B.CommentPDW, B.QS, B.QSDate, B.CommentQS, B.RL, 
    B.RLDate, B.CommentRL, B.RR, B.RRDate, B.CommentRR, B.RW, B.RWDate, B.CommentRW, B.SB, B.SBDate, 
    B.CommentSB, B.SD, B.SDDate, B.CommentSD, B.SG, B.SGDate, B.CommentSG, B.SP, B.SPDate, 
    B.CommentSP, B.SR, B.SRDate, B.CommentSR, B.SS, B.SSDate, B.CommentSS, B.TL, B.TLDate, 
    B.CommentTL, B.TP, B.TPDate, B.CommentTP, B.VS, B.VSDate, B.CommentVS, B.VY, B.VYDate, 
    B.CommentVY, B.YB, B.YBDate, B.CommentYB, B.GVA, B.GVADate, B.CommentGVA, B.GVB, B.GVBDate, 
    B.CommentGVB, B.PM, B.PMDate, B.CommentPM, B.NoVirus, B.NoVirusDate, B.CommentNoVirus, 
    IIF(Nz(B.[Date Created], "")="", NULL, CDate(B.[Date Created])) AS New_DateCreated, 
    B.SelectBox, B.GPS_Block, B.Nursery_Nr, B.Graft_Ref_Nr
FROM Block14 AS B
WHERE B.[Count] = -1048492341 AND B.Block = '14';

[CJ_London]

Is this sql server?

[yrstruly]

No, MS ACESS

[CJ_London]

Guid’s don’t make good PKs in access, better to use a long. You certainly can’t assign 0 to it as that is not a guid.

But to resolve your problem, divide and conquer. Start with the indexed fields, the add say 10 at a time until you get the error. Back up and do 5 then the other 5. In this way you can home in on the problem assignment- and don’t forget there could be more than one