Solved

Rs.CursorLocation

Posted on 2002-03-18
6
909 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 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

8 Experts available now in Live!

Get 1:1 Help Now