Access 2000 ADP & recordsetclone

We are trying to return a row count for a retrieval without querying SQL Server a second time.  The code below is what we are using.

Set rs = fm.RecordsetClone
If Not rs.EOF Then
   Debug.Print i
   tRows = rs.RecordCount
   fm!txtRowsReturned = tRows
   fm!txtRowsReturned = 0

Most of the time it works fine.  But sometimes we get a somewhat arbitrary '50' as the value.  Usually this happens when it is more than 100 rows coming back.

Any thoughts?
Who is Participating?
TextReportConnect With a Mentor Commented:
The RecordCount property of a recordset is only accurate once the recordset has been to EOF, basically it expects you to do a rst.movelast then rst.movefirst to get the info.

A select count(*) is probably the quickes way to do it as it is processed by the server, however, if you don't want to scroll through the recordset multiple times then the array is an option.

Personally I would query the database for the count or wait until I am finished with the recordset before relying on recordcount.

Cheers, Andrew
instead of creating another recordset - could you use an array (less overhead) -

arrTest - rs.GetRows()
tRows = ubound(arrTest)

- not positive on the syntax there but it should be close and you can get your recordcount as well.
Sorry, missed your movelast in the original question. This could be a limit of the way the form recordset is not fully populated at the time you are creating the recordsetclone.

On your form when you click on the goto last record button does it allways take you to the end of the recordset or does it only populate so many more records?

Cheers, Andrew
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


I think that textReport has got the crux of it ... that is that doing a select count statement is alot more sensable this is for several reasons..

1/ it is faster
2/ utilises a client server architecture leaving the client mostly free of processing needs.
3/ using SQL makes it more cross compatible with different sources (SQL server is a funny one and it seems the it does not much like the row count method as I have found recently. I ended up using the SELECT COUNT(*) to sort the problem myself..)
4/ the count should not use up much extra processing

Well give it a thought anyways...

Jonathan KellyCommented:
maybe the server is not returning the complete recordset.

are u using an ADP ?
if u are then the form can be set to return only a limited number of records. On the form properties set the MAx Records value = 0. this will return the complete recordset.

maybe the SQL statment is only returning the TOP 50 %?

FROM         dbo.Table1

I would agree however that SELECT COUNT(*) is the way to go

An new feature in access is that you can use the recordset directly from the form's recordsource, so someting like:

fm!txtRowsReturned = me.Recordset.recordcount

Should do the trick. No clone, no loop...

Also if this is a continous form you can do it directly in the form with the aggregate count() function.

set the txtRowsReturned 's colntrolsource to something like:

=Count([Myfield]) & " records in the form"

(if the textbox is in the footer)


i agree with this:

fm!txtRowsReturned = me.Recordset.recordcount

at least im fairly sure that works.
The RecordCount is still dependant on all the rows being returned by the data source before it is correct and the only way to guarentee the recordcount is correct is to have moved to the last record in the recordset. With a large recordset this takes a bit of time.

The Select Count will still be the most accurate.

Cheers, Andrew
<<The Select Count will still be the most accurate.>>
Dont quite agree...

It depends on the situation (as everyting else in the world), and I would say that most often is the bottleneck the "number of roundtrips" to the server. With select count (*) you need two, one for getting the data and one for counting the data. Also if you change the recordsource you need to change the select count(*).

just my 0.002$

I agree that it depends on the situation and my comment was relating to the original question.

Not sure I follow you point on roundtrips. My understanding with ODBC is that a query requires 1 connection and there is also 1 connection to which gives you the ability to cancel the comms to the server. So if you are executing SELECT * FROM table it uses 2 connection and if you also add SELECT COUNT(*) FROM table that adds 1 more connection not 2 more.

When you do a SELECT COUNT(*) FROM Table all that should be transfered from the PC to the Server is the SELECT statement and all that is returned in the number, the processing of the query is handled by the server, I do not see a possible bottleneck unless the server is not capable of running the SELECT COUNT for some reason.

Cheers, Andrew
Sorry..with #roundtrips i mean # of times you transfer data over the net..(english is not my native laguage so pleas bear over with me)

The connections is constant, unless you add a new of course. It is not the connections I tried to point at.

But you have to *ask* the server for information twice.
First to get the data.
Second to get the number of records in the data you've already got.

And that IS the bottleneck I'm talking about.

What takes time (often..NOT always!) is not to display the data (Client) and not to get/find the data(Server) but the transport of the data between the client and the server.

And what if the user set a filter on the form. The the selct count(*) will be wrong..

Agreed, but if you count then select where both will be based on the same data regardless of any new records will not provide a bottleneck as the count will be quick and it can be presented to teh user when the form opens.

Once they are applying filters etc then absolutely but this is far beyond the question from PeterJayNelson. If they are applying filters to forms based on large recordsets then surely in a client server scenario this is a large bottleneck in itself. The theory is to move the minimum amount of data between the Client and Server. You could alway do an OnApplyFilter <g>

I thinks we are basically agreed though that it is very dependant on what the users wants.

The basic problem the user has is still that the form recordset has not been fully populated by the time the recodsetclone is doing a movelast and the recordsetclone can only report on what is in the form recordset. Due to ODBC setting the form may take some time to get to the final record. Therefore the select count will work for this and is not a bottleneck if the server is handling the count.

Cheers, Andrew

Cheers, Andrew
PeterJayNelsonAuthor Commented:
Firstly, thank you ALL for your thoughtful comments!  I was not able to look into this further over the last 24 hrs but hope to over the next 48.  A few comments

1.  Two inquiries vs. One
We WERE trying to minimize the number of times we accessed the Server while loading the form.  Perove is correct in that assumption.  We thought it would be more efficient, from a network usage perspective, to review the already retrieved "recordset" rather than go back to the well a second time just to count.

2.  Max Rows form property
Another colleague of mine brought this up and I have to research this.  I am pretty sure it is still set at 100.

Some more information......
Since I am developing off-site (and this is where the problem occured for me) I can compare things to my on-site set-up.  Turns out that the code in question DOES WORK accurately at the client site.  While I"m relieved of this from a "bug to fix" perspective, I am still troubled by not knowing the root cause of this irregularity in my off-site work environment.  Yet another colleage suggested that this may boil down to a timing issue - sort of like what TextReport hinted at.  On-site, I am running across a large corporate network with inherit latencies.  The access is still darn fast, but there's those built-in network speed bumps.  At my off-site location I am working DIRECTLY on my server.  So there is virtually zero latency there.  Perhaps my off-site is just too fast and the recordsetclone isn't "catching up" properly.

Again, thank you all for your comments and I'll research this more over the coming days.

sure wish i remembered where i got this one from

SELECT     TOP 100 PERCENT, i.rows
FROM         dbo.sysobjects o INNER JOIN
                      dbo.sysindexes i ON =
WHERE     (i.indid < 2)
PeterJayNelsonAuthor Commented:
EE Gang,

Thank you all for your input and suggestions.  We have decided to go with a second query to the server to ascertain these values.  We tested this at the client and it did not introduce an appreciable delay, so we'll go with this.  We will fire one query to do a distinct count at a case level.  We will then either:  a.) Fire a 2nd query to count ALL rows retrieved, b.) use the me.recordsetcount that Perove and _Holly_ suggested.

So I'm going to take Perove's answer yet still noting the valuable input all of you gave to this question.  Thank you again.

All the best!
PeterJayNelsonAuthor Commented:
EE Gang,

While I typed Perove, I meant to type txtReport.  He's (she's?) really the first one moving to the requerying of the server.  Sorry Perove for the faux award.  However, your input was very valuable as well.  If I could give partial credit I would send some points your way too!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: TextReport  
Please leave any comments here within the
next seven days.


Per recommendation, force-accepted.

CS Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.