rstADO.RecordCount returns wrong count

[N2Life]

I have a query (qryTexShipped) with a Criteria (#3/16/2012#) for a date field, and a Criteria (Like "CM*") for a text field. I run the query directly and see 11 records.

But in my VBA code when I try what you see listed below, the RecordCount is 0. I have done this sort of thing, successfully, many times in other programs, so am stumped as to why the problem occurs now. I am using Access 2010 under Windows 7. I have the following references set:
Visual Basic For Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Scripting Runtime
Microsoft DAO 3.6 Object Library
Microsoft Excel 14.0 Object Library

Private Sub btnHarvestShippingFiles_Click()
Dim RowKtr As Long
Dim ar() As arrShipped
Dim Ktr As Integer
Dim Ck As String
Dim TotRecs As Integer
Dim rstADO As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'GetShippingData:
rstADO.Open "qryTexShipped", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
TotRecs = rstADO.RecordCount
MsgBox TotRecs
...

 

[TheAceMan1]

How are ya N2Life . . .

I don't use ADO much but I remember someone using [blue]AbsolutePosition[/blue] to do this (something to do with the fact that [blue]ADO defaults to a Server side cursor[/blue]). Try the following:

[blue]   rstADO.Open "qryTexShipped", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
   rstADO.[purple][b]MoveLast[/b][/purple]
   TotRecs = rstADO.[purple][b]AbsolutePosition[/b][/purple] + 1[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[N2Life]

Thank you. I did not try AbsolutePosition, but I did try the rstADO.MoveLast. It gives the usual EOF/BOF error you get when there are no records. What is so frustrating is that I have used this same kind of code many times. I even re-booted my laptop in case Access had become "confused" but there was no effect.

 

[MazeWorX]

For no records trap the error

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[N2Life]

The returning of 0 records does not produce an error. The RecordCount is simply 0. The code takes that and moves on. Here are a few more lines of the code, which I maybe should have included:


'GetShippingData:
rstADO.Open "qryTexShipped", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
TotRecs = rstADO.RecordCount
MsgBox TotRecs
If TotRecs <> 0 Then
rstADO.MoveFirst

BUT the real RecordCount is actually 11, which I see when I run qryTexShipped from the keyboard.

 

[TheAceMan1]

N2Life . . .

Have a look here How To Retrieve a Record Count From an ADO Recordset

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[N2Life]

Thanks for the interesting reference. The table my query was built on was obtained by copying a linked ODBC table at work, and choosing the option to paste all the data into a local table so I could bring the data home. I had never made such a copy before because that option had not been available in Access XP. There must be something special about such a table that is causing my query not to work, even though I have played with several of the rstADO.settings. I tried other queries based on normal tables and there was no problem with RecordCount. In any case, I solved the problem by switching to a DAO approach, and everything is fine now. It will be interesting to see if I have a problem when I run this at work with the original linked table. I will probably adopt DAO in all cases for such a task. Again, many thanks.

 

[JoeAtWork]

If all you want to know is how many records the query returns, use simple SQL to get a count (Why would I want the overhead of loading a million records just to find out that is how many the query returns, for example?).

rstADO.Open "SELECT Count(SomeField) As RecCount FROM qryTexShipped", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not (rstADO.BOF And rsADO.EOF) Then
   TotRecs = rstADO("RecCount")
Else
   TotRecs = 0
End If
MsgBox TotRecs

Note, your code example suggests you don't really need to know the count, just whether there are any records, in which case I would simply change it this way:

'GetShippingData:
rstADO.Open "qryTexShipped", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
If Not (rstADO.BOF And rsADO.EOF) Then
    rstADO.MoveFirst

 

[N2Life]

Thank you JoeAtWork. This was more than just wanting to know if there were any records or not. My query actually had records but I wasn't seeing them with the ADO approach. That is what didn't make any sense. In subsequent lines of code, I wanted to do something with each record returned by the query.

 

[MazeWorX]

What you really need to think about is forcing the count. VBA doesnt automatically populate the rs count property so you can force it to populate by
rs.movelast
rs.movefirst

Then check the count

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[JoeAtWork]

In subsequent lines of code, I wanted to do something with each record returned by the query.

Are you doing the exact same update for every record? If so, I would scrap using a recordset altogether and just run a simple SQL statement, e.g.:

UPDATE MyTable SET SomeField = Something WHERE <whatever criteria your query uses>