Solved

VB SQL Insert in access database

Posted on 2002-04-24
16
162 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:RonaldBiemans
  • 4
  • 3
  • 3
  • +5
16 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6966072
What is the error message?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6966134
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
0
 
LVL 18

Expert Comment

by:deighton
ID: 6966138
don't you need to use

YourConnection.execute "INSERT INTO items ( [enqueteursnr], cyclus....etc
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Author Comment

by:RonaldBiemans
ID: 6966239
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


0
 
LVL 4

Expert Comment

by:P1
ID: 6966525
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
0
 
LVL 4

Expert Comment

by:P1
ID: 6966533
OOPS,  Wrong post for this question.
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6966984
I agree with the others - for an action query you should use the .Execute method.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6967163
You will have to add a Where clause or an Inner Join to exclude those duplicate records.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6967171
Inner Join: Actually that should be a Left Join

Anthony
0
 
LVL 1

Expert Comment

by:asafmm
ID: 6967822
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
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 6967857
Look at my previous comment
0
 
LVL 18

Expert Comment

by:deighton
ID: 6968310
ronald

does your table have a single unique key?  If so then we can use NOT IN to exclude the duplicates
0
 
LVL 18

Expert Comment

by:deighton
ID: 6968324
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))


0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 6968330
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

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 6968591
The reason is that Access is using a temporary table.  This also allows it to undo the changes.

There is no way around it, you will have to use a Where condition with a subquery like deighton has suggested.  However, since there is more than one column it may be simpler to do a Left Join with Items with a Where condition of Items.KeyValue1 Is Null.  This will give you the non-duplicate rows.

Anthony
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 6968657
Yep that is what I did.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy a row 12 70
Add and format columns in vb6 7 71
I need help using System.Web.HttpUtility.HtmlEncode in my VB.Net application 3 110
MsgBox 4 71
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question