Change SQL into VBA query

Shanan212
Shanan212 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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.

Author

Commented:
Thanks!

What do you recommend then? How would I go on to accomplish this alternatively?
Top Expert 2010
Commented:
Better:

DoCmd.RunSQL "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"

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2010

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
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 :/
Top Expert 2010
Commented:
If the present approach, with "INSERT INTO TableA SELECT * FROM TableB", is working, you can use it.  It's not a best practice, but if it works, it works.

The main reason I do not recommend it is that if your table layout ever changes, or if the order of columns in TableA is different from that in TableB, you may get a bad result.

So, it's a matter of how important it is to you to do it the "right" way.

:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial