Slim Jim

Sorry about preovious post (accidentally hit Send,
posting only half the message !)

Here goes again, hope someone can advise...



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 !!

Please help !!

Cheers

With your code, you are accessing a Linked Table.
Linked Table will not give RecrodCount, it will be
always -1.


"Counting Records (correct version)" <[email protected]>
wrote in message news:[email protected]...
> Sorry about preovious post (accidentally hit Send,
> posting only half the message !)
>
> Here goes again, hope someone can advise...
>
>
>
> 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 !!
>
> Please help !!
>
> Cheers
>
>

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.

--

Is there a .MoveLast method that can be used followed by a .RecordCount?


"Jamie Collins" wrote:

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
> --
>

Gary Brown wrote:
> Is there a .MoveLast method that can be used followed by a
..RecordCount?
>

Surely:

http://msdn.microsoft.com/library/de...hmovefirst.asp
Jamie.

--