Solved

Access 2000 ADP & recordsetclone

Posted on 2002-07-09
18
318 Views
Last Modified: 2012-08-14
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
   rs.MoveLast
   Debug.Print i
   tRows = rs.RecordCount
   rs.Close
   fm!txtRowsReturned = tRows
Else
   fm!txtRowsReturned = 0
endif

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?
0
Comment
Question by:PeterJayNelson
  • 5
  • 3
  • 3
  • +6
18 Comments
 
LVL 5

Expert Comment

by:funke
ID: 7141686
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.
0
 
LVL 28

Accepted Solution

by:
TextReport earned 100 total points
ID: 7141852
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
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7142700
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
0
 
LVL 2

Expert Comment

by:Griffon
ID: 7142731

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...


Griffon
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 7142756
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 %?

SELECT     TOP 50 PERCENT Field1
FROM         dbo.Table1

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




0
 
LVL 9

Expert Comment

by:perove
ID: 7142840
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)

perove

0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7144943
i agree with this:

fm!txtRowsReturned = me.Recordset.recordcount

at least im fairly sure that works.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7145279
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
0
 
LVL 9

Expert Comment

by:perove
ID: 7145443
TextReport
<<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$

perove
 
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:TextReport
ID: 7145479
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
0
 
LVL 9

Expert Comment

by:perove
ID: 7145541
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.


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

0
 
LVL 28

Expert Comment

by:TextReport
ID: 7145665
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
0
 

Author Comment

by:PeterJayNelson
ID: 7145849
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.

Peter
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7163854
sure wish i remembered where i got this one from


SELECT     TOP 100 PERCENT o.name, i.rows
FROM         dbo.sysobjects o INNER JOIN
                      dbo.sysindexes i ON o.id = i.id
WHERE     (i.indid < 2)
ORDER BY o.name
0
 

Author Comment

by:PeterJayNelson
ID: 7164871
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!
0
 

Author Comment

by:PeterJayNelson
ID: 7164876
EE Gang,

*** SORRY ABOUT THIS ***
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!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7260164

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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7296317
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now