SQL CopyFromRecordset

[Bret]

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

[AA2e72E]

"Bret" wrote:
> 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


"Bret" <[email protected]> wrote in message
news:[email protected]...
> 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
>