The following is a code chunk that I am using to retrieve a recordset from an
MS SQL Server using a function that I created on the server. The function
returns the records fine, but it doesn't return the column headers.

I would like to use the information to create/update a pivot table directly,
without having to paste the data to the spreadsheet via the copyfromrecordset
method.

Any ideas of what I'm missing.

Thanks in advance.

Mark

Set Cnxn = New ADODB.Connection
Cnxn.ConnectionTimeout = 0
Cnxn.Open strCnxn

'SQL to call from db
Set rs = New ADODB.Recordset
strSQL = "SELECT ClientID, Client, [Desc], Tool, Amount FROM
dbo.fnMyFunction('" _
& Format(DateValue(dtStart), "mm/dd/yyyy") & "','" _
& Format(DateValue(dtEnd), "mm/dd/yyyy") & "')"

Mark,

you can create a pivottable in code directly on top of your SQL, no
need to get the ADO recordset first.

You can either work through code in one go using a pivottable object,
but I suggest you first create a manual pivottable report, and then use
some code to set the parameters in the sql string, since pivottable
reports do not allow parameters.

The easiest way is as follows:

create a stored proc to wrap all SQL stuff up, XL uses ODBC and can be
picky on some SQL (like table functions!), but stored procs always
work. Start Macro recorder for later VBA code reference and create a
PVtable from external data, and in MS Query use File-execute SQL. Now
select your sp and fill in parameters directly. Run the PV it and stop
the recorder. Look at the SQL property and change that with VBA
according your parameters before each run.

DM Unseen

The column headers for an ADO recordset are in the field names, e.g.
rs.Fields(1).Name.

If you want to use this query to populate a pivottable, though, a more
direct way is to use the SourceData property of the pivottable object. You
can use this to specify your SQL connection string and query. From the help
file:

SourceData Property

Returns the data source for the PivotTable report, as shown in the following
table. Read-only Variant...

External data source: An array. Each row consists of an SQL connection
string with the remaining elements as the query string, broken down into
255-character segments.

"MChrist" wrote:

The following is a code chunk that I am using to retrieve a recordset from an
MS SQL Server using a function that I created on the server. The function
returns the records fine, but it doesn't return the column headers.

I would like to use the information to create/update a pivot table directly,
without having to paste the data to the spreadsheet via the copyfromrecordset
method.

Any ideas of what I'm missing.

Thanks in advance.

Mark

Set Cnxn = New ADODB.Connection
Cnxn.ConnectionTimeout = 0
Cnxn.Open strCnxn

'SQL to call from db
Set rs = New ADODB.Recordset
strSQL = "SELECT ClientID, Client, [Desc], Tool, Amount FROM
dbo.fnMyFunction('" _
& Format(DateValue(dtStart), "mm/dd/yyyy") & "','" _
& Format(DateValue(dtEnd), "mm/dd/yyyy") & "')"