?
Solved

INSERT / UPDATE if syntax

Posted on 2005-05-09
5
Medium Priority
?
1,136 Views
Last Modified: 2008-01-09
Hi Experts

In SQL server 7 you have the use of IF statements

e.g (Please note I have only typed this out. There may be errors)



IF (SELECT COUNT(UserID) FROM QuestionTracking WHERE UserID = 10 < 1)
BEGIN
INSERT INTO QuestionTracking (UserID) VALUES (10)
 END
ELSE BEGIN
UPDATE QuestionTracking SET Correct = 1 WHERE UserID = 10
 END


How would I do this in MS Jet Access?

thanks
0
Comment
Question by:stef4s
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 13958194
Hi stef4s,
You would have to do this in VBA code.
Jet sql does not have procedural statements.

dim strsql, strsql1
strsql ="INSERT INTO QuestionTracking (UserID) VALUES (10)"
strsql1 = "UPDATE QuestionTracking SET Correct = 1 WHERE UserID = 10"

if nz(Dcount("*", "Questiontracking","Userid = 1")) >0 then
currentdb.execute strsql1, dbfailonerror
else
currentdb.execute strsql, dbfailonerror
end if

Pete
0
 
LVL 5

Author Comment

by:stef4s
ID: 13966198
Hmm isnt there a trick to perform?

Like  (I know this is invalid SQL)

"INSERT INTO QuestionTracking (UserID) VALUES (10) WHERE (SELECT COUNT(*) FROM QuestionTracking WHERE UserID = 10)


0
 
LVL 5

Author Comment

by:stef4s
ID: 13966207
The problem is that I am sending sql statements from a socket app and generating XM to send back via the socket. So I want to minimise round trips.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 13966258
You can do the insert as you suggest but you cannot also include an Update statement in the same SQL action.

There is simply no If..then..else feature in Jet SQL

Pete




0
 
LVL 5

Author Comment

by:stef4s
ID: 13968651
I miss SQL Server TSQL ..sniff
0

Featured Post

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!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

840 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