Loading recordset into array...

[bfreescott]

I have a function that I pass string variables to, to make an ADODB connection to another workbook and run a query against. The variables provide the column and row for the table I am querying, so I am trying to understand how I can read the data from the recordset into an array that will be useful when it is returned. How does the array know which field is which?

rs.Open "SELECT * FROM [RCsheet$A1:BK94] WHERE Award = '" & p & "'", Conn, adOpenDynamic

This select statement will always return only one record/row from the table. If I loaded that recordset into an array, how would I identify the fields that the values are from when the array is returned to the sub? Furthermore, there may be fewer fields in the table than variables I am passing, so I'd like to avoid the 3265 error.

 

[Norie]

You can use GetRows to return an array from the recordset but all you'll get is the data.

 

[bfreescott]

Thanks Norie,

That approach won't work because I need to be able to id the Field when the data is returned.

Let's switch gears... if I already know the record and field I want from the table, what is the fastest way to return that value?
The code I have is way too slow because for each row that matches my "p" variable, I have dozens of fields to For-Each through.

rs.Open "SELECT * FROM [RCsheet$A1:BK94] WHERE Award = '" & p & "'", Conn, adOpenDynamic

            For Each fld In rs.Fields
               If fld.Name = c Then
                    Get_WB_Numbers = rs(c)
               End If
            Next

 

[Norie]

Not quite sure what you mean but you can get the value from a specific field, c, like this.

Get_WB_Numbers = rs.Fields(c)

Which on further review of your code is what you have here, though you are using shorthand by not using Fields.

Get_WB_Numbers = rs(c)