ADODB RecordSet Question = Row Headers?

[bblevins]

Hi All,

I um using a UserForm to build dynamic queries based on user input. Based on what the user selections are, the columns returned can be different. There will be anywhere from 1 to 4 columns returned. Once the query is built, I have the following function that will write the results of the query into an array "arrData". This all works as expected. Once I have the data in the array I need to output it to another sheet. The only thing missing is that I cannot figure out how to get the column names into the RecordSet/Array. This has to be possible, but I have been unable to figure it out.

An example query would be as follows:

SELECT DISTINCT [MP], [CE], [Vendor], [Store Name] FROM [External Views].[dbo].[Performance Reporting Scorecard Wireless Monthly]

So in this instance, I want to see the column names for each of the four columns in the first row of each array column. Thoughts?

Public Function GetData(strSQL As String)

Dim arrData() As Variant
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim DBPath As String, sconnect As String

'Establish the connection
    If Conn.State <> 1 Then
        Conn.Open "Provider=SQLNCLI11.1;Password=MyPassword;Persist Security Info=True;User ID=MyUser;Initial Catalog=External Views;Data Source=xxxxxxxxxxxx;"
    End If
    

'Open the recordset
    RS.Open strSQL, Conn

    
'Load the Data into an array
    arrData= RS.GetRows
     
    
'Close Recordset
    RS.Close

'Close Connection
    Conn.Close

End Sub

 

[bblevins]

OK, I do see that I can access the field names "Headers" like this:

For i = 0 To rs.Fields.Count - 1
    Cells(1, i + 1).Value = rs.Fields(i).Name
Next

So as far as identifying what columns are there that handles it. Perhaps this is all I need. Then I can locate the correct column in the destination worksheet for that column of data from the array and dump the data in the correct column onto the worksheet. Any way to dump 1 entire column of the array on the worksheet without looping through? I need to do it column by column because my data is simply serving as a ref lookup in formulas elsewhere on the page. So I need them to be in the right place every time.