RonaldBiemans
asked on
VB SQL Insert in access database
When I use this statement directly in Access
INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr], ((([Expr1]-1)-(([Expr1]-1) Mod 12))/12)+1 AS Expr2, Sum([aanw]*-1) AS Expr1 FROM stiptheid GROUP BY stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0))
it inserts the records it can and the ones that are already in the database are ignored/discarded
But when I try to do the same thing from within a VB program
rst.Open "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr], ((([Expr1]-1)-(([Expr1]-1) Mod 12))/12)+1 AS Expr2, Sum([aanw]*-1) AS Expr1 FROM stiptheid GROUP BY stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0))", CNN, adOpenStatic, adLockOptimistic
it gives an error message and doesn't add any records
(error resume next doesn't work btw)
Is there a way in VB to get the same result as in Access
Thanks in advance
RB
INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr], ((([Expr1]-1)-(([Expr1]-1)
it inserts the records it can and the ones that are already in the database are ignored/discarded
But when I try to do the same thing from within a VB program
rst.Open "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr], ((([Expr1]-1)-(([Expr1]-1)
it gives an error message and doesn't add any records
(error resume next doesn't work btw)
Is there a way in VB to get the same result as in Access
Thanks in advance
RB
What is the error message?
you should NOT use arecordset object ot perform ACTION queries (an INSERT is called an Action query) as Action queries DO NOT return recordsets
ALL Action queries should be performed using the EXECUTE method of the Connection object, like this in you case:
CNN.Execute "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr],
((([Expr1]-1)-(([Expr1]-1) Mod 12))/12)+1 AS Expr2, Sum([aanw]*-1) AS Expr1 FROM stiptheid GROUP BY
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0)"
even better, create a Query String variable, assign the Text to that variable, and then use the variable in the Execute statement. That way, if you want to set a breakpoint and examine the string you can, before it is processed:
Dim strSQL as String
strSQL = "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr],
((([Expr1]-1)-(([Expr1]-1) Mod 12))/12)+1 AS Expr2, Sum([aanw]*-1) AS Expr1 FROM stiptheid GROUP BY
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0)"
CNN.Execute strSQL
Arthur Wood
ALL Action queries should be performed using the EXECUTE method of the Connection object, like this in you case:
CNN.Execute "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr],
((([Expr1]-1)-(([Expr1]-1)
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0)"
even better, create a Query String variable, assign the Text to that variable, and then use the variable in the Execute statement. That way, if you want to set a breakpoint and examine the string you can, before it is processed:
Dim strSQL as String
strSQL = "INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr],
((([Expr1]-1)-(([Expr1]-1)
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0)"
CNN.Execute strSQL
Arthur Wood
don't you need to use
YourConnection.execute "INSERT INTO items ( [enqueteursnr], cyclus....etc
YourConnection.execute "INSERT INTO items ( [enqueteursnr], cyclus....etc
ASKER
You may be right, but the problem and the error message remain the same.
runtime error
Cannot update table because adding the records would create
duplicate records
I know that some of the records are duplicates and like I said when I do this directly in Access it adds the records it can and the rest is discarded
runtime error
Cannot update table because adding the records would create
duplicate records
I know that some of the records are duplicates and like I said when I do this directly in Access it adds the records it can and the rest is discarded
It simplifies a lot of problems by using stored procedures with parameters instead of parsing and executing the SQL strings themselves.
The SQL server runs runs everything for the client with a speed boost besides.
Enjoy your work, P1
The SQL server runs runs everything for the client with a speed boost besides.
Enjoy your work, P1
OOPS, Wrong post for this question.
I agree with the others - for an action query you should use the .Execute method.
You will have to add a Where clause or an Inner Join to exclude those duplicate records.
Anthony
Anthony
Inner Join: Actually that should be a Left Join
Anthony
Anthony
Dear RonaldBiemans
All the previous comments are good :
1 - dont use resume next, write error handler
2 - use stored procedure
3 - use connection.execute
But your problem comes from the Data Definition
1 - you are updating the columns [enqueteursnr], cyclus, [aantal shifts] make sure they are not an unique values index, because the error says that you try to create duplicate value in one of this columns.
2 - you are working with access so check in debug mode the connection because the provider may limit you from doing things like this (the best for you is using OLEDB jet 4)
Thanks
Asaf
All the previous comments are good :
1 - dont use resume next, write error handler
2 - use stored procedure
3 - use connection.execute
But your problem comes from the Data Definition
1 - you are updating the columns [enqueteursnr], cyclus, [aantal shifts] make sure they are not an unique values index, because the error says that you try to create duplicate value in one of this columns.
2 - you are working with access so check in debug mode the connection because the provider may limit you from doing things like this (the best for you is using OLEDB jet 4)
Thanks
Asaf
ASKER
Look at my previous comment
ronald
does your table have a single unique key? If so then we can use NOT IN to exclude the duplicates
does your table have a single unique key? If so then we can use NOT IN to exclude the duplicates
INSERT INTO items ( [enqueteursnr], cyclus, [aantal shifts] ) SELECT DISTINCTROW stiptheid.[enqueteursnr],
((([Expr1]-1)-(([Expr1]-1) Mod 12))/12)+1 AS Expr2, Sum([aanw]*-1) AS Expr1 FROM stiptheid
WHERE stiptheid.[enqueteursnr] NOT IN (SELECT t2.enqueteursnr FROM items as t2)
GROUP BY
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0))
((([Expr1]-1)-(([Expr1]-1)
WHERE stiptheid.[enqueteursnr] NOT IN (SELECT t2.enqueteursnr FROM items as t2)
GROUP BY
stiptheid.[enqueteursnr] HAVING (((Sum([aanw]*-1))>0) AND ((Sum([aanw]*-1) Mod 2)=0))
ASKER
No it has key that consist of 2 vars. 1 of those vars
is Expr1:(Sum([aanw]*-1))
I can get around my problem by using a second table.
I was just wondering why I can't get the same result as in Access, where I don't have to use a second table
is Expr1:(Sum([aanw]*-1))
I can get around my problem by using a second table.
I was just wondering why I can't get the same result as in Access, where I don't have to use a second table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep that is what I did.