sql stored procedure results in a recordset questions


G

Guest

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.
 
Ad

Advertisements

G

Guest

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?
 
Ad

Advertisements

G

Guest

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top