Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2019, 11:07 PM   #1
DavRob
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
DavRob is on a distinguished road
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

DavRob is offline   Reply With Quote
Old 10-19-2019, 11:27 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,289
Thanks: 115
Thanked 3,090 Times in 2,808 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
DavRob (10-19-2019)
Old 10-20-2019, 06:57 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,718
Thanks: 93
Thanked 1,712 Times in 1,585 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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 Customer_Import 
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?

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
DavRob (10-20-2019)
Old 10-20-2019, 04:07 PM   #4
DavRob
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
DavRob is on a distinguished road
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

DavRob is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
"can't append all the records" but no sign of common causes Philocthetes Queries 13 03-05-2018 12:42 PM
Creating "append to table" type of query in access 2013 web app? adi2011 Access Web 2 02-18-2016 04:25 AM
Only Suppressing "Do you want to append XX records?" Warning? Stang70Fastback Modules & VBA 4 12-02-2014 08:00 AM
Need to ignore Microsoft Access can't append all the records in the append query DNASok Queries 7 03-21-2013 06:22 AM
"Access can't append all the records..." through VBA johan_a83 Modules & VBA 2 08-03-2011 02:56 AM




All times are GMT -8. The time now is 05:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World