Append Query Error Message

[Big_Si]

Hi All

I have recently contructed an Access DB which is used to search for new records over an ODBC connection on our AS400. When it finds new entries it then appends them to a second Access DB at regular intervals for user input.

The query works fine in so far as it appends the appropriate records however due to the nature of how the AS400 tables are contructed it will on occasion find a duplicate entry on the linked table. This is not presenting any issues for the user DB as I have configured it with the correct primary keys to avoid this, however when it encounters these duplications it seems to halt the query pending a user response. I get a message which reads

"Microsoft Access can't append all the records in the append query

Microsoft access set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to valudation rule violations.
Do you want to run the action query anyway?
To idnore the error and run the query, click Yes."

This brings me to my question...

Is there a way of stopping the error message from coming up or a method to ignore these errors?

help as always is appreciated.

 

[neileg]

Try the unmatched query wizard to select only those records in the new entries table that don't have a match in the main table. Then append the records found in this query.

 

[Big_Si]

How do I get the wizard to check against entries in the external DB? It only seems to give me the options against Local tables and queries. I already perform the check for unmatched data locally but the problem occurs when it writes the records out to the external DB. Below is the SQL statement I use:

INSERT INTO Sequence_Reports ( JobID, SequenceID, Area, JobNumber, SequenceNumber, Engineer, SerialNumber, [Job Type], ApprovalName, VisitDate, VisitTime, CompleteTime, JobHours, ChargeHours, JobComplete, Warranty, Tasks, EquipmentType, TruckType, InstallDate, FleetNumber, Report1, Report2, Report3, Report4, Report5, Report6, Report7, Scrap, Abuse, Reason1, Reason2, PartsRequired, TechRep, Quot10, CalloutFee, Travel, Parts, Labour, Environmental, ReportSent, ReportSkipped, QuotedPrice, NewQuote, PartsDueDate, CompleteDate, IrisUpdate ) IN 'S:\Service\Process Development\IRIS Application\IRISrev3.mdb'
SELECT Sequence_Reports.JobID, Sequence_Reports.SequenceID, Sequence_Reports.Area, Sequence_Reports.JobNumber, Sequence_Reports.SequenceNumber, Sequence_Reports.Engineer, Sequence_Reports.SerialNumber, Sequence_Reports.[Job Type], Sequence_Reports.ApprovalName, Sequence_Reports.VisitDate, Sequence_Reports.VisitTime, Sequence_Reports.CompleteTime, Sequence_Reports.JobHours, Sequence_Reports.ChargeHours, Sequence_Reports.JobComplete, Sequence_Reports.Warranty, Sequence_Reports.Tasks, Sequence_Reports.EquipmentType, Sequence_Reports.TruckType, Sequence_Reports.InstallDate, Sequence_Reports.FleetNumber, Sequence_Reports.Report1, Sequence_Reports.Report2, Sequence_Reports.Report3, Sequence_Reports.Report4, Sequence_Reports.Report5, Sequence_Reports.Report6, Sequence_Reports.Report7, Sequence_Reports.Scrap, Sequence_Reports.Abuse, Sequence_Reports.Reason1, Sequence_Reports.Reason2, Sequence_Reports.PartsRequired, Sequence_Reports.TechRep, Sequence_Reports.Quot10, Sequence_Reports.CalloutFee, Sequence_Reports.Travel, Sequence_Reports.Parts, Sequence_Reports.Labour, Sequence_Reports.Environmental, Sequence_Reports.ReportSent, Sequence_Reports.ReportSkipped, Sequence_Reports.QuotedPrice, Sequence_Reports.NewQuote, Sequence_Reports.PartsDueDate, Sequence_Reports.CompleteDate, Sequence_Reports.IrisUpdate
FROM Sequence_Reports LEFT JOIN Sequence_Reports_Updated ON Sequence_Reports.SequenceID = Sequence_Reports_Updated.SequenceID
WHERE (((Sequence_Reports_Updated.SequenceID) Is Null));

 

[neileg]

I haven't used Access against an AS400 so I don't know. You could import the AS400 data into your database and then check for duplicates.