sql stored procedure results in a recordset questions

When I run a stored procedure out of data>import...>new database query by
pasting in {call procname} in the sql code box, the results returned include
the field names from the select * procedure .

When I assign the results of the stored procedure to a recordset out of VBA
I encounter several issues:
The code I use to assign the results to the recordset is as follows:

Set runspcmd = New ADODB.Command
runspcmd.CommandText = procname
runspcmd.CommandType = adCmdStoredProc
' execute the command.
Set runspcmd.ActiveConnection = cnnct
Set recset = runspcmd.Execute

1) The field/column names from the procedure results are not included in
recset. Anyway I can get the names as well?

2) recordcount, and certain of the Move commands don;t work --(presumably
because of the implicit cursor type?).
Is there any way I can easily count how may records are in the recordset?

3) I know how to place the recordset on a worksheet using copyfromrecordset.
Can copyfromrecordset be used to assign the recordset to an array? (I am
trying to populate a multi-column list box with the data in recset) Any other
suggestions on how to easily do this?

Thanks for any help anyone can provide.

I almost answered my own questions 2 and 3.
Using recset.getrow, I can assign my results to an array and then use ubound
to get the number of records. However,getrow seems to transpose the data in
the resultset (or maybe the resultset transposed the results of the stored
procedure)

E.g. the stored procedure results has 3 rows and 2 columns.
aa=recset.getrows has 2 rows and 3 columns.

Any way I can get aa to mirror the result set without having to scroll
though row by row, column by column?




"in-over-his-head-bill" wrote:

> When I run a stored procedure out of data>import...>new database query by
> pasting in {call procname} in the sql code box, the results returned include
> the field names from the select * procedure .
>
> When I assign the results of the stored procedure to a recordset out of VBA
> I encounter several issues:
> The code I use to assign the results to the recordset is as follows:
>
> Set runspcmd = New ADODB.Command
> runspcmd.CommandText = procname
> runspcmd.CommandType = adCmdStoredProc
> ' execute the command.
> Set runspcmd.ActiveConnection = cnnct
> Set recset = runspcmd.Execute
>
> 1) The field/column names from the procedure results are not included in
> recset. Anyway I can get the names as well?
>
> 2) recordcount, and certain of the Move commands don;t work --(presumably
> because of the implicit cursor type?).
> Is there any way I can easily count how may records are in the recordset?
>
> 3) I know how to place the recordset on a worksheet using copyfromrecordset.
> Can copyfromrecordset be used to assign the recordset to an array? (I am
> trying to populate a multi-column list box with the data in recset) Any other
> suggestions on how to easily do this?
>
> Thanks for any help anyone can provide.

Alright, the coffee finally kicked in and through rs().name I figured out how
to get the headers. And through lst.column() , I got the transpose problem
fixed. Down to one question that I will post in a separate thread. How do you
populate column headers in a list box from a recordset?

"in-over-his-head-bill" wrote:

> I almost answered my own questions 2 and 3.
> Using recset.getrow, I can assign my results to an array and then use ubound
> to get the number of records. However,getrow seems to transpose the data in
> the resultset (or maybe the resultset transposed the results of the stored
> procedure)
>
> E.g. the stored procedure results has 3 rows and 2 columns.
> aa=recset.getrows has 2 rows and 3 columns.
>
> Any way I can get aa to mirror the result set without having to scroll
> though row by row, column by column?
>
>
>
>
> "in-over-his-head-bill" wrote:
>
> > When I run a stored procedure out of data>import...>new database query by
> > pasting in {call procname} in the sql code box, the results returned include
> > the field names from the select * procedure .
> >
> > When I assign the results of the stored procedure to a recordset out of VBA
> > I encounter several issues:
> > The code I use to assign the results to the recordset is as follows:
> >
> > Set runspcmd = New ADODB.Command
> > runspcmd.CommandText = procname
> > runspcmd.CommandType = adCmdStoredProc
> > ' execute the command.
> > Set runspcmd.ActiveConnection = cnnct
> > Set recset = runspcmd.Execute
> >
> > 1) The field/column names from the procedure results are not included in
> > recset. Anyway I can get the names as well?
> >
> > 2) recordcount, and certain of the Move commands don;t work --(presumably
> > because of the implicit cursor type?).
> > Is there any way I can easily count how may records are in the recordset?
> >
> > 3) I know how to place the recordset on a worksheet using copyfromrecordset.
> > Can copyfromrecordset be used to assign the recordset to an array? (I am
> > trying to populate a multi-column list box with the data in recset) Any other
> > suggestions on how to easily do this?
> >
> > Thanks for any help anyone can provide.