error null value and duplicate primary key when run append query (1 Viewer)

masoud_sedighy

Registered User.
Local time
Today, 15:39
Joined
Dec 10, 2011
Messages
132
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
 

Minty

AWF VIP
Local time
Today, 22:39
Joined
Jul 26, 2013
Messages
10,355
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.
 

masoud_sedighy

Registered User.
Local time
Today, 15:39
Joined
Dec 10, 2011
Messages
132
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?
 

isladogs

MVP / VIP
Local time
Today, 22:39
Joined
Jan 14, 2017
Messages
18,186
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
 

masoud_sedighy

Registered User.
Local time
Today, 15:39
Joined
Dec 10, 2011
Messages
132
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?
 

Minty

AWF VIP
Local time
Today, 22:39
Joined
Jul 26, 2013
Messages
10,355
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?
 

Users who are viewing this thread

Top Bottom