Solved

VB6 Recordset is empty when records exist in my View because of Group By

Posted on 2009-04-14
4
318 Views
Last Modified: 2013-11-27
Hi
I'm programming with VBA for Excel, and I have a SELECT instruction that returns -1 RecordCount.

Here is my Slect Instruction :
RsPortes.Open "Select SONumber, NumSection, Qty, UniteMesure, ItemNumber, ItemName, DFTPICKINGLISTOPR FROM V_Portes WHERE V_Portes.SONumber='" & NumJob & "'", MaConnectionAxaptaData, adOpenDynamic, adLockOptimistic

The V_Portes is an SqlServer2005 view that is build with a Group By, it is working fine and getting the right results.

How can I get my recordset lines within VBA Excel code ?

Thanks  


0
Comment
Question by:venmarces
4 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
Comment Utility
RsPortes.RecordCount is unreliable when you use adOpenDynamic.

If you just need to see whether there are rows, use RsPortes.EOF.

You can change to an adOpenStatic and adLockReadOnly and get RecordCount to behave -- assuming you don't need to write back to the recordset.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>that returns -1 RecordCount
that happens because you open on ServerSide instead of ClientSide cursor location, usually.

anyhow, do you really need the recordcount itself, or just if there are records or not => use bof and eof instead.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
And in case you did not get the message:  The RecordCount property should not be used.
0
 

Author Closing Comment

by:venmarces
Comment Utility
When I needed to get the RcordCount value of my Cursor I changed for adOpenStatic, adLockReadOnly and I was able to get the right value
Thanks for everybody  
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

763 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

10 Experts available now in Live!

Get 1:1 Help Now