INSERT / UPDATE if syntax

Posted on 2005-05-09
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)
INSERT INTO QuestionTracking (UserID) VALUES (10)
UPDATE QuestionTracking SET Correct = 1 WHERE UserID = 10

How would I do this in MS Jet Access?

Question by:stef4s
    LVL 77

    Expert Comment

    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
    currentdb.execute strsql, dbfailonerror
    end if

    LVL 5

    Author Comment

    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)

    LVL 5

    Author Comment

    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.
    LVL 77

    Accepted Solution

    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


    LVL 5

    Author Comment

    I miss SQL Server TSQL ..sniff

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now