How can this be true??!?

Simply put, I'm opening a database, searching through a table and then
showing a message box with 2 numbers, one of which is the RecordCount of the
RecordSet, but I'm being told by the computer that I have -1 for a
RecordCount.
I've using ADO...

Here's the code...
The first sub opens the database
The second sub closes the database
The third sub tells me I have -1 counts

'=================================================
Public Cnxn As ADODB.Connection
Public strCnxn As String
Public rstLayers As ADODB.Recordset
Public Const AppName = "VBA::Set Layer"
'=================================================
Public Sub OpenDatabase()
On Error GoTo ErrMsg
' Open connection
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=S:\CADDStds\Menus\Data\Layers.mdb"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
Exit Sub

ErrMsg:
MsgBox "An error has occurred in modMain.OpenDatabase" & vbCrLf &
Err.Number & ":" & Err.Description, vbCritical + vbOKOnly, AppName
End Sub'=================================================
Public Sub CloseDatabase()
On Error Resume Next
rstLayers.Close

On Error Resume Next
Cnxn.Close
End Sub
'=================================================
Sub CountCurrent()
OpenDatabase

Dim rsSearch As Recordset
Dim SQL As String

Set rsSearch = New ADODB.Recordset
SQL = "SELECT * FROM Layers ORDER BY Layer_Name"
rsSearch.Open SQL, Cnxn
MsgBox rsSearch.RecordCount 'shows that I have -1 records??!?
Dim i As Integer

i = 0
Do Until rsSearch.EOF
If rsSearch!current = True Then i = i + 1
rsSearch.MoveNext
Loop
MsgBox "Of the " & rsSearch.RecordCount & " files in the Layers table, "
& i & " are currently being used."
CloseDatabase
End Sub
'=================================================

 

The RecordCount property is far from that straight forward.

I think there is a good explanation at www.aspfaq.com , go to the Database
listing.

(If I could give a coherent, detailed answer, I would...........I'll have to
leave it to the reference above and/or other database wizards here.)

 

The RecordCount is anything but reliable.

Use the BOF and EOF properties to determine if you have records and when
you've reached the end of the recordset.

 

I can't believe I'm gonna say this, but I never thought I'd have a Frank O
correction.
It *is* quite reliable actually but Microsoft didn't make it very obvious.

I checked out aspfaq.com from Tom D's reply. Here's what I found out:
http://support.microsoft.com/?id=194973

--
I support two teams: the Red Sox and whoever beats the Yankees.


| Matt W wrote:
| > Simply put, I'm opening a database, searching through a table and then
| > showing a message box with 2 numbers, one of which is the RecordCount of
the
| > RecordSet, but I'm being told by the computer that I have -1 for a
| > RecordCount.
|
| The RecordCount is anything but reliable.
|
| Use the BOF and EOF properties to determine if you have records and when
| you've reached the end of the recordset.
|
| --
| There are 10 kinds of people: those who understand binary and those who
| don't.

 

Change this line
Dim rsSearch As Recordset

To read like the following
Dim rsSearch As ADODB.Recordset

See what happens.

 

<quote>
When you request the RecordCount for a serverside recordset, a -1 may
return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later
when the CursorType is adOpenForwardonly or adOpenDynamic. This occurs
with ADO 1.5 only when the cursortype is adOpenForwardonly. Testing with
the OLEDB provider for JET and SQL Server produces varying results,
depending on the provider.

Providers may not support some CursorTypes. When you select a CursorType
that is not supported, the provider should select the CursorType closest
to what you request. Please refer to the documentation that comes with
your provider. Also, please note that not all combinations of LockType
and CursorType work together. Changing a LockType may force a change in
the CursorType. Be sure to use debug to check the value of CursorType
when conducting tests with your OLEDB provider.
</quote>

That's way too many variables for something to be considered reliable
from a programmatic standpoint.

 

I knew you were going to reply...

I agree that there are a lot of variables, however, the fact still remains
that it is reliable.

I don't like the fact that "This behavior is by design."
It certainly doesn't make it easy, but it's still reliable.

--
I support two teams: the Red Sox and whoever beats the Yankees.


| Matt W wrote:
|
| > It *is* quite reliable actually but Microsoft didn't make it very
obvious.
|
| <quote>
| When you request the RecordCount for a serverside recordset, a -1 may
| return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later
| when the CursorType is adOpenForwardonly or adOpenDynamic. This occurs
| with ADO 1.5 only when the cursortype is adOpenForwardonly. Testing with
| the OLEDB provider for JET and SQL Server produces varying results,
| depending on the provider.
|
| Providers may not support some CursorTypes. When you select a CursorType
| that is not supported, the provider should select the CursorType closest
| to what you request. Please refer to the documentation that comes with
| your provider. Also, please note that not all combinations of LockType
| and CursorType work together. Changing a LockType may force a change in
| the CursorType. Be sure to use debug to check the value of CursorType
| when conducting tests with your OLEDB provider.
| </quote>
|
| That's way too many variables for something to be considered reliable
| from a programmatic standpoint.
|
| --
| There are 10 kinds of people: those who understand binary and those who
| don't.

 

I'm kind of with Frank on this issue. I avoid using it for this very
reason. In most cases, you can whittle down your recordset significantly
with an appropriate SQL string. I usually put the resulting records into an
array and can get a count from that.

Again, keep in mind that I'm NOT an expert with ADO. Frank's not likely to
give bad advice.

 

First of all, use a client side cursor if you want to use the record count
property.

Set rsSearch = New ADODB.Recordset
rsSearch.CursorLocation = adUseClient
rsSearch..CursorType = adOpenDynamic 'or Static

That should do it.

If you want to stick to a server side cursor then you could get the
recordcount like this:

dim rstemp as ADODB.Recordset
dim recordcount as long

Set rstemp = Cnxn.execute ("SELECT count(*) FROM Layers",,adcmdtext)
if rstemp.eof or rstemp.bof then
recordcount = -1
else
recordcount=rstemp.fields(0)
end if

set rstemp = nothing

 

I agree it certainly is reliable you just have to know what you are doing. Here is a better explanation from ASP 101 website.
Regards - Nathan

So why isn't it working for you? Well it's supported only if the recordset supports approximate positioning (adApproxPosition) or bookmarks (adBookmark). I only mention this so that if you feel like testing for support you can do so using code like this:

If objRecordset.Supports(adBookmark) Or objRecordset.Supports(adApproxPosition) Then
' RecordCount will work!
End If

So what the heck does that mean?

Basically it's supported if you use the right kind of cursor. For the most part all client-side cursors support it as do server-side static and server-side keyset cursors. So the only two that don't are server-side dynamic and server-side forward only cursors. Unfortunately, most people don't specify the cursor type to use when they open a recordset and what you usually get when you don't specify one is a server-side, forward only cursor which is one of the ones that doesn't support a record count! So the trick is to specify what type of cursor you want.

 

Just a bit extra to what Nathan said and have been mentioned so far.

In order get record count, the database server must loop through the
retrieved recordset at least once. The reason of that two types of cursor
(dynamic and forward-only) do not return RecordCount is to optimize DB
Server output. In many operation, there is no need to know recorcount before
hand, so use this cursor type saves server resources. If you need to know
record count, simply choose other type of cursor that gives it. If there is
high user competition to connect to db server, what type of recordset to
open could be a key point to think of.

Here is a better explanation from ASP 101 website.

supports approximate positioning (adApproxPosition) or bookmarks
(adBookmark). I only mention this so that if you feel like testing for
support you can do so using code like this:

objRecordset.Supports(adApproxPosition) Then

part all client-side cursors support it as do server-side static and
server-side keyset cursors. So the only two that don't are server-side
dynamic and server-side forward only cursors. Unfortunately, most people
don't specify the cursor type to use when they open a recordset and what you
usually get when you don't specify one is a server-side, forward only cursor
which is one of the ones that doesn't support a record count! So the trick
is to specify what type of cursor you want.

 

Tip for you Tom - use a collection or dictionary. If you're not relying on
a recordcount to redim your array, then you are either adjusting as you go
which leads to unnecessary processing or you are dimming the array larger
than you need to at start. Also, if by chance you hit a null value, the
array's gonna pop an error where the collection won't.

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

Actually, you're both right and you're both wrong --- how's that for
sticking to the middle of the road =)

The key is whether you know what you're doing or not --- not you two but
the programmer in general. It is unreliable if you are using a server-side
cursor and/or don't understand how cursors & locking work. If you do
understand, or you are using a client-side cursor then it is reliable.

Personally, unless you are writing a web-based app or a stand alone
configurator, you shouldn't be using a server-side cursor. Performance is
great on the server side but you lose whatever advantage when you consider
the network latency and you run the risk of lockiing others out whilst your
app is doing whatever it does. I've rarely seen a cad-integrated app that
requires the server-side --- exception being Map/MapGuide.

-- Mike
___________________________
Mike Tuersley
CADalyst's CAD Clinic
Rand IMAGINiT Technologies
___________________________
the trick is to realize that there is no spoon...

 

Thanks for the tip, Mike. Going to collections is one of the things I'm
going to be doing.

 

I'm going to plead ignorance, Mike.

Thanks, again, for the info. I hadn't even considered the server/client
cursor aspect. Most of the DB stuff I've written is web-based, PHP stuff,
which is inherently server-side. Time to do a little more reading, I think.

 

Thanks, Jorge, for the Cursor info. It is most helpful.