SQL CopyFromRecordset

[Guest]

My Excel program is a front end to a SQL backend.
Users run various reports that execute stored procedures in SQL server.
My question is I use the "copyfromrecordset" code to autofill the returning
recordsets into a worksheet however they dont return the column headers.
How do I get the column headers either from VBA Excel or from the SQL stored
procedures. The stored procedures are just simple "Select * from" statements.

here is the code that works but has no headers.
myConnection.Open
Set myCommand.ActiveConnection = myConnection
Set myRecordSet.ActiveConnection = myConnection
myCommand.CommandText = strSQL
myCommand.CommandType = adCmdText
myCommand.Execute
myRecordSet.Open myCommand

'Paste to spreadsheet
ThisWorkbook.Worksheets("Report").Range("a2").CopyFromRecordset
myRecordSet

thanks for any help

 

[Guest]

My question is I use the "copyfromrecordset" code to autofill the returning
recordsets into a worksheet however they dont return the column headers.
How do I get the column headers either from VBA Excel or from the SQL stored
procedures.

I am sure that I have seen an example in the Excel help files that has your
answer.
Try this:

for each fld in myRecordSet.Fields
name = fld.Name
.... code
next

where code is the Excel VBA code for writing each name to successive columns
on Row 1; I assume you can come up with the actual code.

 

[RB Smissaert]

Use this general purpose function:

Function fieldArrayFromRS(rs As ADODB.Recordset) As Variant

'gets the field names from an ADO recordset
'and puts them in a one dimensional 0-based array
'------------------------------------------------
Dim objField As ADODB.Field
Dim tempArray()
Dim n As Long

ReDim tempArray(0 To rs.Fields.count - 1)

For Each objField In rs.Fields
tempArray(n) = objField.Name
n = n + 1
Next

fieldArrayFromRS = tempArray

End Function

Or you could make the array such that it could be directly written to the
sheet:

Function fieldArrayFromRS(rs As ADODB.Recordset) As Variant

'gets the field names from an ADO recordset
'and puts them in a 2 dimensional 1-based array
'------------------------------------------------
Dim objField As ADODB.Field
Dim tempArray()
Dim n As Byte

ReDim tempArray(1 to 1, 1 To rs.Fields.count)

For Each objField In rs.Fields
n = n + 1
tempArray(1, n) = objField.Name
Next

fieldArrayFromRS = tempArray

End Function

With the second one you could use it like this:

dim arr

arr = fieldArrayFromRS(rs)

range(cells(1), cells(1, ubound(arr,2))) = arr


RBS