Paul  Kuriakides Paul Kuriakides - 2 months ago 8
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 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 "

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


End Sub

Answer

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.name, 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
     rsc.movenext
 Loop