ADO recordset returns -1

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have a connection to the Northwind database from
Excel. My queries work find except when I request the
number of records I always get -1. Is there something
I'm missing? Here's a section of my code if this helps:

Thank you in advance, Dennis

*************start code****************

Dim myConnection As String
Dim rsData As ADODB.Recordset

'===================================
'Create a connection to Northwind DB
'===================================
myConnection = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data
source=C:\ProgramFiles\Microsoftoffice\" & _
"Office10
\Samples\Northwind.mdb;"

sSQL = "SELECT * FROM Categories"
Set rsData = New ADODB.Recordset
rsData.Open sSQL, myConnection
MsgBox rsData.RecordCount

************end code*****************
 
Hi Dennis

Recordsets comes with different "cursors". Some let you browse back and
forth, some are "forward only" (faster but no recordcount or moves except
movenext and movelast). It seems that default is forwardonly from what you
report here. Try

rsData.Open sSQL, myConnection, adopenkeyset, adopenreadonly

The speed issue between forwardonly and keyset depends on database brand and
size. For smaller Access apps I don't notice much difference, Oracle
likewise, but SQLserver is annoyingly slow using keyset. My personal
undocumented experiences only.

HTH. Best wishes Harald
 
Thank you for the reply. I tested your additional code
and received an error. Instead I wrote your first
suggestion, adOpenKeyset and it worked great:

rsData.Open sSQL, myConnection, adOpenKeyset

I will have to study up on this in the future.

Thanks, Dennis
 
Hi,

the default CursorLocation is server-side (adUseServer) with forward only
cursors.

To navigate ADO recorsets, and get information like "Recordset.Count" or
"Recordset.AbsolutePosition" you need to explicitly set it to adUseClient ...
same for default recordsets returned by code like this:

Set RS = Connection.Execute sSql

To read up on ADO see the MDAC SDK here at the MSDN Library here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursorlocation.asp

HTH

Philip
 
Philip said:
the default CursorLocation is server-side (adUseServer) with forward only
cursors.
Click to expand...

Yes, but you can specify something other than the default!
To navigate ADO recorsets, and get information like "Recordset.Count" or
"Recordset.AbsolutePosition" you need to explicitly set it to adUseClient
Click to expand...

Incorrect. Availability of such properties is determined by cursor
type, not location. Any cursor type other than 'forward only' will
support the RecordCount property.
get information like "Recordset.Count"
Click to expand...

The Recordset object does not have a Count property.

Jamie.

--
 
Back
Top