Paul  Kuriakides Paul Kuriakides - 1 year ago 67
SQL Question

Displaying an SQL query in VBA to appear on a spread sheet

I'm trying to make this SQL query display in an excel sheet but each time I run the macro nothing happens. I've tried adding a range with cells in so it could display there but nothing happens. There are no error messages when i run the code as well.

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, 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 t on = tl.id_trade " & _
"inner join mfb.instrument i on t.id_instrument = " & _
"inner join mfb.instrument_type it on = i.id_instrument_type " & _
"inner join mfb.options o on o.id_instrument = " & _
"inner join mfbref.mfb.underlying un on = o.id_underlying " & _
"inner join mfb.allocation_leg al on al.id_trade_leg = " & _
"where tl.trade_date > '20160101' and t.state = 3 "

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

End Sub

Answer Source

You've got a good start, but in the end you are left with an opened recordset full of data and... you don't do anything with it. It's not going to magically show up in your worksheet. You can use the range object's method .copyFromRecordset to quickly dump the recordset to a worksheet range.

At the end of your code, before END SUB put:

 Sheet1.Range("A1").CopyFromRecordset rcs

It will then dump the results of your query into Sheet1 at Cell A1.

Updated to work with the code mentioned in your question's comments. After you do your Set R = Range("A2:A6") you can use that .copyfromrecordset method to dump to R:

 R.CopyFromRecordset rcs

For the sake of thoroughness, you can also iterate through your recordset, and even the fields. After you have loaded your recordset you can do things like:

 Dim f as Adodb.Field

 'loop through each record
 While now rcs.EOF 

     'loop through all the fields in this record:
     For each f in rcs.fields
         'get the fields name and value:
         debug.print, f.value
     Next f

     'refer to a specific field (instead of looping through fields)
     debug.print rcs.Fields("price_crossing").name, rcs.Fields("price_Crossing").value

     'go to the next record
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download