ADO Stored Procedure Issue - VBA

I cannot figure out how to load an ADO Recordset with a Stored Procedure????  Any help would be appreciated.  Every time I run the below code I get -1 as the result????


' Create a connection object.
Dim cnPubs As New ADODB.Connection
Dim cmd1 As New ADODB.Command
 
' Provide the connection string.
cnPubs.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=testtest;Initial Catalog=TimberOps;Data Source=ServerName\SQLExpress"
 
' Create a recordset object.
Dim rsPubs As New ADODB.Recordset
cmd1.ActiveConnection = cnPubs
cmd1.CommandText = "spUnitHarvestCosts_ByYear"
cmd1.CommandType = adCmdStoredProc
Set rsPubs = cnPubs.Execute("exec spUnitHarvestCosts_ByYear")
 
'Set rsPubs = cmd1.Execute()
 
'rsPubs.Open "exec spUnitHarvestCosts_ByYear", cnPubs
 
MsgBox rsPubs.RecordCount
 
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

Open in new window

CFrasnellyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
   Set rsPubs = New ADODB.Recordset
    rsPuubs.LockType = adLockOptimistic
    rsPubs.CursorType = adOpenKeyset
    rsPubs.Open cmdl


Above should do it for you.


Kelvin
0
CFrasnellyAuthor Commented:
That didn't work so I tried it with a different stored procedure and it DID work?!?!?  The stored procedure I want it to work with only has 3 fields and seems simple.  Any thoughts on why it would work with one SP and not another?????  I have literally just copied and pasted the sp name over so it's not that. :)

Thanks!!!
0
CFrasnellyAuthor Commented:
Figured it out - there was a GROUP BY clause in the SP.  As soon as I removed it, it worked fine.  Is that normal behavior for ADO to return a -1 for any SP's that contain the GROUP BY clause?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Just to make clear - when you say
>> "I cannot figure out how to load an ADO Recordset with a Stored Procedure"
you had absolutely achieved that.
Just because you were getting -1 as the record count doesn't mean the recordset wasn't loaded. :-)
The RecordCount property is just that - a property of the recordset.  It's not a physical count of the rows, but something that ADO exposes for your convenience.

The method suggested by Kelvin was such that you are able to open a different cursor type on your recordset.  (In ADO the cursor type is fundamental in determining the behaviour and functionality that your recordset will expose).
By allowing an Execute method you'd opened a ForwardOnly cursor type recordset.  This doesn't support the RecordCount property (not in a meaningful way at least, it returns -1 always).
The revision offered allowed you to open a Keyset.
This supports a RecordCount, though a complete accurate count isn't guaranteed (if it's something you need accurately then you could include it in your stored procedure and return it as a subsequent recordset or as an Output parameter).
You found it to fail on an aggregate procedure and that is a possibility.
However ensuring that you have a fully loaded recordset (and by that I mean of data not just keys) should force the issue.
You achive this by setting your cursor connection to be client side prior to opening it.
rsPubs.CursorLocation = adUseClient
None the less, using the stored proc is the more robust way.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.