Turn off "Microsoft Access can't append all the records in the append query" (1 Viewer)

DavRob

New member
Local time
Today, 21:35
Joined
Oct 19, 2019
Messages
27
Turn off "Microsoft Access can't append all the records in the append query"

Hi All

I have an Append Query to add records from "TblCustomerImport" to "TblCustomers" the problem that I have is duplication of the existing records in"TblCustomers" I have tried the SQL below:

INSERT INTO TblCustomers ( JobNo, CustName, Address, Date )
SELECT DISTINCT TblCustomerImport.F1, TblCustomerImport.F2, TblCustomerImport.F3, TblCustomerImport.F4
FROM Customer_Import
WHERE NOT Exists (SELECT 1 FROM Customers_Without_Duplicates WHERE Customers_Without_Duplicates.JobNo = TblCustomerImport.JobNo;

This works but I get "Microsoft Access can't append all the records in the append query".

Is there any way of answering yes to this, over ride it or not have it display.

DAvRob
 

isladogs

MVP / VIP
Local time
Today, 11:05
Joined
Jan 14, 2017
Messages
18,186
Re: Turn off "Microsoft Access can't append all the records in the append query"

You can disable all such messages permanently in the client settings section of Access options but I don't recommend it as it will hide other messages that you may want.
Or run that sql statement using code
Code:
CurrentDb.Execute "INSERT INTO …..", dbFailOnError
Doing that means messages are only displayed if there is an error
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
26,996
Re: Turn off "Microsoft Access can't append all the records in the append query"

Perhaps that query isn't doing what you think it is doing. You should be able to simplify it considerably.

Code:
INSERT INTO TblCustomers ( JobNo, CustName, Address, Date )
SELECT DISTINCT F1, F2, F3, F4
FROM [COLOR="Red"]Customer_Import[/COLOR] 
WHERE TblCustomerImport.JobNo NOT IN 
( SELECT JobNo From Customers_Without_Duplicates );

Question: Is that the right table name in the earlier FROM clause? (The one I highlighted in red.) Should it perhaps be TblCustomerImport? Because if it is correct, I see a disconnect. If correct, what is the connection between Customer_Import and TblCustomerImport?
 

DavRob

New member
Local time
Today, 21:35
Joined
Oct 19, 2019
Messages
27
Re: Turn off "Microsoft Access can't append all the records in the append query"

Thanks The_Doc_Man your code was the answer.

Thanks Isladogs for your reply
 

Users who are viewing this thread

Top Bottom