Paul  Kuriakides Paul Kuriakides - 2 months ago 22
SQL Question

How to execute SQL queries in VBA Excel

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

Answer
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

Comments