ADO RecordCount Doesn't Work

[Scott Dovey]

Can anyone help me with this?

The problem is that testing the number of records in an ADO recordset by
using the RecordCount property returns zero, even though opening the same
query directly returns several records. It seems this happens when using an
asterisk with the "Like" string operator in the query or SQL statement.

For example:

I have a test table named "tblPersons" which has two fields: lngPersonID
(Type = AutoNumber) and strName (Type = Text). The table contains two
records for "Wally" and "Guido".

Next, I have created a query ("qrySelectPerson") which uses the "Like"
operator to select records based on matching names. The SQL of the query is
as follows:

SELECT tblPersons.strName
FROM tblPersons
WHERE tblPersons.strName Like "wally";

When this query is run it returns one record.

The following procedure uses the RecordCount property to count the number of
records returned by the query. It also shows one record in the recordset:

Sub Test()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.Open "qrySelectPerson", CurrentProject.Connection, adOpenStatic, _
adLockReadOnly, adCmdStoredProc
MsgBox "Records: " & .RecordCount
.Close
End With
Set rst = Nothing
Exit Sub

End Sub

The problem arises when I alter the query by adding an asterisk to the Like
operator to select a close rather than exact match:

WHERE tblPersons.strName Like "wally*";

The query returns one record, but the Test procedure based on the same query
shows zero records in the recordset.

This sure looks like a bug to me. Any ideas?

Thanks

Scott Dovey

[Brian Coiley]

I don't know about ADO, but in DAO you have to MoveLast to the end of a
recordset to make sure that the RecordCount is complete.

HTH

Brian

Scott Dovey <sco...@bigpond.com> wrote in message
news:kMUz6.11614$45.6...@newsfeeds.bigpond.com...

[Don P. Mellon]

I think Brian is right. You need to use MoveLast before you can get a
record count. In your finished version, you might want to test for EOF
before you use MoveLast, however.

"Scott Dovey" <sco...@bigpond.com> wrote in message
news:kMUz6.11614$45.6...@newsfeeds.bigpond.com...

[Brian Coiley]

Yes, that's right. Interestingly, I've always tested for RecordCount = 0
before doing the MoveLast, but Scott's experience suggests that EOF would be
safer.

Brian

Don P. Mellon <wig...@3rivers.net> wrote in message
news:_C0A6.4233$6y1.1...@e3500-chi1.usenetserver.com...

[David Suttin]

I'm no expert but in this case the problem has nothing to do with MoveLast.
This would be a possibility with DAO but not with ADO.

The problem is with the use of the * wildcard. As an experiment I tried:

.Open "SELECT strName FROM tblPersons WHERE tblPersons.strName Like 'wal*'",
_
CurrentProject.Connection, adOpenStatic, adLockOptimistic

instead of referencing the already existing query...same result. Sorry I
don't have a solution but I thought I would let you know the solution does
not have to do with MoveLast.

David

"Scott Dovey" <sco...@bigpond.com> wrote in message
news:kMUz6.11614$45.6...@newsfeeds.bigpond.com...

[INUKA]

In DAO it will look something like this:

Private Sub CountRecs()

Dim db as Database
Dim rs as Recordset
Dim NumberOfRecords as Long

Set db = CurrentDB
Set rs = db.OpenRecordset("tblPersons")

' === Initialize ===
NumberOfRecords = 0

With rs
Do until .EOF
If (!strName = "Wally") or (!strName = "Guido") Then
NumberOfRecords = NumberOfRecords + 1
End If
.movenext
Loop
End With
rs.close

set db = nothing

End Sub

HTH

INUKA

"Scott Dovey" <sco...@bigpond.com> wrote in message
news:kMUz6.11614$45.6...@newsfeeds.bigpond.com...

[Lyle Fairfield]

like wally%

"Scott Dovey" <sco...@bigpond.com> wrote in

<kMUz6.11614$45.6...@newsfeeds.bigpond.com>:

--
Lyle
http://www.cyriv.com/

[David Suttin]

Yup...that was it.

Works perfectly.

"Lyle Fairfield" <lyle...@yahoo.com> wrote in message
news:Xns907D86CCEly...@24.9.0.131...
> like wally%
>

[Scott Dovey]

Its true that in ADO all of the MoveLast / EOF / etc workarounds don't
work - this might be okay in DAO, but as far as ADO is concerned the
recordset has no records. But the solution puzzles me. If I use "Like wal%"
in the query I get no records, but the same syntax in an SQL statement in a
VBA procedure works fine. Why is this, and how the hell did you guys figure
this out?

Scott

David Suttin <Davi...@aol.com> wrote in message
news:XW8A6.42891$wx.11...@typhoon.austin.rr.com...

[Lyle Fairfield]

Persons migrating from DAO to ADO may benefit from reading
http://support.microsoft.com/support/kb/articles/Q225/0/48.ASP
and the documents to which it refers.

"Scott Dovey" <sco...@bigpond.com> wrote in

<CRgA6.12311$45.7...@newsfeeds.bigpond.com>:

--
Lyle
http://www.cyriv.com/