Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-27-2017, 01:47 AM   #1
masoud_sedighy
Newly Registered User
 
Join Date: Dec 2011
Posts: 117
Thanks: 55
Thanked 1 Time in 1 Post
masoud_sedighy is on a distinguished road
error null value and duplicate primary key when run append query

after migration to sql when i run the macro for running append queries i got alot of error like "you can not set null value...." and error "primary key could not be duplicate" , ....

i have tried to correct these queries with using NOT IN (SELECT.....) in the append queries but i still got error about these append queries and i can't manage the errors and i have doubt when i try to import a excel file

before in ms access i did not have these errors if it had, ms access continues job until finished appending records and for duplicate or null value does not terminate the program.

is there any option in sql server when running append queries does not terminate the execution for the null value or duplicate primary key like what ms access does in the macro "set warning false"
Reply With Quote Reply With Quote

masoud_sedighy is offline   Reply With Quote
Old 09-27-2017, 02:29 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: error null value and duplicate primary key when run append query

You would need to run the queries from VBA and use

CurrentDB.Execute "YourQueryName" , dbSeeChanges

If you do want to see failures add + dbFailOnError as another option.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

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.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
masoud_sedighy (09-28-2017)
Old 09-27-2017, 04:21 AM   #3
masoud_sedighy
Newly Registered User
 
Join Date: Dec 2011
Posts: 117
Thanks: 55
Thanked 1 Time in 1 Post
masoud_sedighy is on a distinguished road
Re: error null value and duplicate primary key when run append query

about the "YourQueryName" parameter , it should be pass-through query or Access query.

when i put pass-through query speed of execution is okey but some times have errors, when i put ACCESS query speed of execution is very slow. i would like to know what is the different between them and what is the reason?

which one i have to use?

i would like to use pass-through query name but some times have errors and i have doubt it works or no?

masoud_sedighy is offline   Reply With Quote
Old 09-27-2017, 04:54 AM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,063
Thanks: 37
Thanked 483 Times in 448 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: error null value and duplicate primary key when run append query

1. Regarding your primary key issue, if the PK field is autonumber do not include it in your query. Let it be populated automatically.
If its not an autonumber use a filter in the append query so it only appends records where the pk field value doesn't exist in the destination table
I.e an unmatched append query

2. You could use append or pass through.
Append is easier to setup.
Pass through should in theory be faster as the process is done by the server.
However unless you have a very large number of records to append I doubt you'll notice the difference
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
masoud_sedighy (09-28-2017)
Old 09-27-2017, 06:37 AM   #5
masoud_sedighy
Newly Registered User
 
Join Date: Dec 2011
Posts: 117
Thanks: 55
Thanked 1 Time in 1 Post
masoud_sedighy is on a distinguished road
Re: error null value and duplicate primary key when run append query

when i change my append query to pass-through query it does in 5 seconds.

when i use append query Access it takes 7 minuted for execution 6 append queries this time is alot, i do not know what is the problem, before migration tables to sql server it was fast but after migration it takes time execution append queries.

i do not want to change to pass-through query because it shows error some times even when i have used filter for appending and not null values.
how i can use ms access append queries but more speed, what is the problem these append queries are very slow after migration to sql?
masoud_sedighy is offline   Reply With Quote
Old 09-27-2017, 06:44 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: error null value and duplicate primary key when run append query

There are many possible reasons - let's start with some more facts.
How many records are you adding? How many fields are involved? What indexes are there on the tables?

How are you connected to the server? What is your network speed like? Gigabit everywhere?

__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

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.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
masoud_sedighy (09-28-2017)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Msg 'Duplicate Values in Index, Primary Key or relationship' access7 Tables 1 09-23-2011 11:05 AM
Change duplicate values Error Msg in the index, primary key, or relationship Khalid_Afridi Modules & VBA 4 05-25-2011 11:16 PM
Catch a null primary key error BobJones Forms 2 07-21-2006 04:50 AM
Unmatched Append Query with a Compount Primary Key tmort Queries 2 01-12-2005 11:38 AM
Error trapping Null or zero-length Primary Keys Alexandre Modules & VBA 3 04-17-2001 08:24 AM




All times are GMT -8. The time now is 06:35 PM.


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

Sponsored Links

How to advertise

Media Kit


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