• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

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
Set rsPubs = Nothing
Set cnPubs = Nothing

Open in new window

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

Above should do it for you.

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. :)

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?

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now