Solved

Stop ADO query in the middle of execution in VB6

Posted on 2004-08-04
6
515 Views
Last Modified: 2013-12-25
Hi all

I am trying to do a query which returned around 10.000 rows and put it in the MSHFlexGrid.
Command is like

strsql = "select * from [table_name]"
rs.open strsql,[connection_name]

set mshflexgrid.datasource = rs

I would like to stop the query in the middle of execution and put the happen being executed rows in the mshflexgrid. (like in SQL Query analyzer)

Is that possible ?

Please advise.
0
Comment
Question by:joris_navius
  • 2
6 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 125 total points
Comment Utility
I guess it's not possible. But my question for you is: why get so many rows in your flexgrid ? It will take a lot of memory and time to get all those records.
0
 

Author Comment

by:joris_navius
Comment Utility
Hi Dhaest

I am creating small tool as an add-in in my applications (like Query Analyzer) so user can create their own query. Just in case the user select lot of rows and want to stop it, do you have any advise about this ?

thanks
0
 
LVL 19

Expert Comment

by:Rimvis
Comment Utility
You cannot expect that you will stop a query execution in the middle and it will return a half of results. Sure, for simple queries like "SELECT * FROM SomeTable" this might work, but in general, it depends on what execution plan SQL Server will use. Specially in complex queries with multiple JOINs and sub-selects.

I suggest you must limit amount of rows BEFORE the quety execution. So if user will try to execute query "SELECT Something FROM SOmeTable" with result limit of 10000 rows, you will have to do this:

sQuery = "SELECT TOP " & iResultLimit & Mid$(sQuery, 7) ' replace SELECT with SELECT TOP x
0
 
LVL 19

Expert Comment

by:Rimvis
Comment Utility
Or better, use MaxRecords property of Recordset object:

Private Sub Form_Load()
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    con.Open "<your connection string>"
   
    rst.MaxRecords = 10000 'YOUR RECORD LIMIT
   
    rst.Open"SELECT Something FROM SOmeTable", con
   
    Do While Not rst.EOF
       ' add rows to grid

        rst.MoveNext
    Loop
   
    rst.Close        
    con.Close
   
End Sub
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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
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…
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…

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

12 Experts available now in Live!

Get 1:1 Help Now