Avatar of Shanan212
Shanan212
Flag for Canada asked on

Change SQL into VBA query

Hi,

INSERT INTO Temp
SELECT PROBILLS.*
FROM PROBILLS
WHERE PROBILLS.IDNUMBER Not In (select IDNUMBER from [temp]);


How do I change the query above into VBA such that I can run it via the line below

 DoCmd.RunSQL "QUERY HERE"

Thanks for help. I tried few ways and all gave me an error/not-working query.
Microsoft Access

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

DoCmd.RunSQL "INSERT INTO Temp " & _
    "SELECT PROBILLS.* " & _
    "FROM PROBILLS " & _
    "WHERE PROBILLS.IDNUMBER Not In (select IDNUMBER from [temp]);"

Open in new window



That said, I highly recommended NOT using SELECT * with INSERT.
Shanan212

ASKER
Thanks!

What do you recommend then? How would I go on to accomplish this alternatively?
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

Basically, enumerate the columns that are in Temp in parentheses in the INSERT clause, and in the SELECT clause explicitly name each column from PROBILLS that should be part of the insert.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

<No Points wanted>

You may also wish to store the SQL in a variable
(in case you need it again, or you want to display it to double-check your syntax)

You may also want to use currentdb.execute instead:
Resulting in:

Dim strSQL as string
strSQL ="INSERT INTO Temp (Col1, Col2, Col3) " & _
    "SELECT p.ColX, p.ColY, p.ColZ " & _
    "FROM PROBILLS p LEFT JOIN " & _
    "Temp t ON p.IDNUMBER = t.IDNUMBER " & _
    "WHERE t.IDNUMBER Is Null"
'Opt, Display SQL to check syntax:
'msgbox strSQL
currentdb.execute strSQL, dbfailonerror

(Again, no points wanted as matthewspatrick has answered your direct question here.

;-)
Jeff
Shanan212

ASKER
Thanks Jeff and Matthew!

The problem is, it got 60 columns! I am too lazy go and track each. Then comes the problem of spaces among them and brackets, legal names, this and that

Is my thinking bad or can I survive with that :/
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.