How to execute SQL queries in VBA Excel

Question

Sub Run()

  Call ConnectDB

    Dim Cmd As ADODB.Command
    Dim rcs As ADODB.Recordset
    Dim SQL As String
    Dim res() As String

    Set Cmd = New ADODB.Command
    Set Cmd.ActiveConnection = con

    SQL = "select tl.id, al.price_crossing, al.price_exchange_fees, tl.charges_execution, tl.charges_mariana, tl.charges_exchange, tl.trade_date, un.value, tl.nb_crossing  from mfb.trade_leg tl" & _
    "inner join mfb.trade t on t.id = tl.id_trade" & _
    "inner join mfb.instrument i on t.id_instrument = i.id" & _
    "inner join mfb.instrument_type it on it.id = i.id_instrument_type" & _
    "inner join mfb.options o on o.id_instrument = i.id" & _
    "inner join mfbref.mfb.underlying un on un.id = o.id_underlying" & _
    "inner join mfb.allocation_leg al on al.id_trade_leg = tl.id" & _
    "where tl.trade_date > '20160101' and t.state = 3"

    Cmd.CommandText = SQL
    Set rcs = Cmd.Execute()

Every time I execute this code an debug error appears saying incorrect syntax near 'tl'. I've tries executing the SQL in different ways but the error won't change

Perhaps you have missed a whitespace before inner join statements — user1326628
– user1326628, Commented Sep 20, 2016 at 12:57
I'm not about the spaes and whether you need to put the AS before tl. — Nebi
– Nebi, Commented Sep 20, 2016 at 12:57
It didn't seem to matter in this example, but in the future make sure you include what database software you are using. They have different syntaxes that can change the answer you get. (Again, this was a pretty universal issue, but it could bite you in the future) — SandPiper
– SandPiper, Commented Sep 20, 2016 at 13:13

Nebi · Accepted Answer · 2016-09-20 12:58:36Z

2

SQL = "select tl.id, al.price_crossing, al.price_exchange_fees, tl.charges_execution, tl.charges_mariana, tl.charges_exchange, tl.trade_date, un.value, tl.nb_crossing  from mfb.trade_leg AS tl " & _
"inner join mfb.trade t on t.id = tl.id_trade " & _
"inner join mfb.instrument i on t.id_instrument = i.id " & _
"inner join mfb.instrument_type it on it.id = i.id_instrument_type " & _
"inner join mfb.options o on o.id_instrument = i.id " & _
"inner join mfbref.mfb.underlying un on un.id = o.id_underlying " & _
"inner join mfb.allocation_leg al on al.id_trade_leg = tl.id " & _
"where tl.trade_date > '20160101' and t.state = 3 "

answered Sep 20, 2016 at 12:58

Nebi

3262 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Doug Coats · Accepted Answer · 2016-09-20 12:59:37Z

1

SQL = "select tl.id, al.price_crossing, al.price_exchange_fees, tl.charges_execution, tl.charges_mariana, tl.charges_exchange, tl.trade_date, un.value, tl.nb_crossing  from mfb.trade_leg tl " & _
"inner join mfb.trade t on t.id = tl.id_trade " & _
"inner join mfb.instrument i on t.id_instrument = i.id " & _
"inner join mfb.instrument_type it on it.id = i.id_instrument_type " & _
"inner join mfb.options o on o.id_instrument = i.id " & _
"inner join mfbref.mfb.underlying un on un.id = o.id_underlying " & _
"inner join mfb.allocation_leg al on al.id_trade_leg = tl.id " & _
"where tl.trade_date > '20160101' and t.state = 3;"

you left out tons of spaces

answered Sep 20, 2016 at 12:59

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

3 Comments

Paul Kuriakides Over a year ago

Thank you. This helped me so much

Doug Coats Over a year ago

give a guy a thumbs up! and dont forget to mark someones answer as "answered"

Doug Coats Over a year ago

also its worth nothing that due to spacing being an issue with embedded sql, its good to develop a pattern. I personally tyr to leave my spaces at the end of each line. Setting up a pattern of bgehavior will help you trouble shoot in the future. @PaulKuriakides

Tom Mathew · Accepted Answer · 2016-09-20 12:59:20Z

0

You should use "as" when assigning aliases to your SQL objects.

Try changing

     ...from mfb.trade_leg tl...
     ...join mfb.trade t...
     ...join mfb.options o ...

to

     ...from mfb.trade_leg as tl...
     ...join mfb.trade as t...
     ...join mfb.options as o ...

answered Sep 20, 2016 at 12:59

Tom Mathew

1541 silver badge11 bronze badges

1 Comment

onedaywhen Over a year ago

1) This isn't the root cause of the syntax error. 2) It's actually a range variable. 3) Opinion based e.g. I prefer to omit the AS because it encourages the wrong thinking i.e. table as x wrongly implies that x is an alias.

Collectives™ on Stack Overflow

How to execute SQL queries in VBA Excel

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related