Solved

Rs.CursorLocation

Posted on 2002-03-18
6
913 Views
Last Modified: 2007-11-27
Hi everyone,

When i use CursorLocation adUseClient on opening of a table with large ammount of records, the opening is very very slow.
on the other hand, when i use CursorLocation adUseServer,
i get -1 value on the RecordCount and AbsolutePosition properties.

How can i know the RecordCount and AbsolutePosition properties on CursorLocation adUseServer ?

10x

0
Comment
Question by:mosheshitrit
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 6877796
Well, you need to understand the difference behind these cursorlocations.  When you specify adUseClient, it creates a "client-side" cursor, meaning that it retrieves all of the data for the recordset and transfers it back to your program to manage.  That is why it takes so long.

A "server-side" cursor simply has to open up and return you one buffer's worth of data.  However, since not all rows have been returned to the cursor, it doesn't yet know how many records there are.  So, the only way you can get an accurate record count is to read to the end of the cursor, and then, perhaps, the recordcount will reflect the number of records, or, you can look a the RS.AbsolutePosition property.

However, in reading to the end of the "server-side" cursor, you've just lost your performance advantage.

If you absolutely need the count at the beginning of returning records, but you absolutely want to use a server-side cursor, then you might consider issuing the same SQL Statement except instead of selecting columns, do a Select Count(*) from ..... get the count first, and then issue the normal select statement....
0
 
LVL 20

Expert Comment

by:hes
ID: 6877804
Before you check the recordcount do a Movelast then the recordcount will be populated
0
 

Author Comment

by:mosheshitrit
ID: 6879450
To Hes:

10x but the movelast action does not help
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mosheshitrit
ID: 6879455
to mdougan:

Thanks for the explanation.
I can't use the RS.AbsolutePosition property because it returns -1 value

0
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 6879872
What type of cursor are you using?  If you are using a foreward only cursor, then you will probably not have valid counts.  You could try opening an adKeyset or one of the dynamic cursor options and that should help.

I'd be surprised about the AbsolutePosition showing -1, are you sure that you were positioned on the first record?  Or on the last record?  Absolute position should tell you the absolute record number of the record that you are positioned on.  So, if you're on the last record then the AbsolutePosition should be equal to the RecordCount.
0
 

Author Comment

by:mosheshitrit
ID: 6880250
To  mdougan:

Thanks for the quick solution.

Use CursorLocation adUseServer with cursorType
adKeyset and you will see both the RecordCount and AbsolutePosition properties.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

713 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