Depending on your stored proc uou might also have to use the NoCount option

create proc somethingorother
as
begin
SET NOCOUNT ON
'proc code here
SET NOCOUNT OFF
END

Robin Hammond
www.enhanceddatasystems.com

"Jamie Collins" wrote in message
oups.com...

Counting Records (correct version) wrote:
I am connecting to a SQL Server stored procedure in Excel
VBA.

However, I am having difficulty COUNTING the number of
records returned from that Stored Procedure.

I have this code...

Dim conDB As New ADODB.Connection
Dim rstSP As New ADODB.Recordset
Dim strConnect As String

strConnect = "driver={sql server};server=
(local);database=mydb;uid=;pwd=;"


With conDB
.ConnectionString = strConnect
.Open
End With

'open the SP into recordset
conDB.storedprocname, rstSP

Now, I need to work out how many records have been
returned from the stored procedure into the recordset...

RecordCount DOES NOT WORK (it always returns -1), and I
need to be able to loop through the recordset but I can't
because I don't know how long it is !!

Take a look at ADO's CursorType property. Because you didn't specify
one for your recordset, you got the default according to your
Connection object's CursorLocation ... which you didn't specify either.
So you got a server side cursor by default which gives you a forward
only cursor by default which does not support the RecordCount property.

The simplest tweak would be to add the line:

.CursorLocation = adUseClient

just above your .Open line. With a client side cursor you get a static
cursor by default, which means the RecordCount property will be
available.

However, I'd encourage you to consider which properties you do and
don't need; you may have to create a recordset and explicitly set some
properties rather than use the defaults.

Note with a forward only cursor you can still loop through the
recordset, testing for .EOF as you .MoveNext on each iteration.
Jamie.

--