Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag 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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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.
Avatar of Shanan212

ASKER

Thanks!

What do you recommend then? How would I go on to accomplish this alternatively?
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
<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
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial