Solved

Query Analyzer -> VB

Posted on 1998-11-22
14
613 Views
Last Modified: 2013-12-25
I was able to Declare a cursor and then do a fetch with a certain Index using SQL 7.0 Query Analyzer. The reason for  doing this is that I want to step through a huge table one row at a time.

Is there a way of doing this from VB?  Using ADO and record sets have been a nightmare.  The select is too complicated and SQL ends up doing a full Table scan.


I am using VB6 and SQL 7.0.
0
Comment
Question by:sbg
14 Comments
 
LVL 1

Expert Comment

by:ElvasLion
ID: 1497773
I could'nt understand your question.
If you don't mind, i woul'd like you to rephrase it.
0
 

Author Comment

by:sbg
ID: 1497774
Basically, I have to treat SQL table is an ISAM database.  I know that this is not the best thing to do but I have no choice.

I have a contact table of names that is huge.  I have an index on Lastname, firstname, middlename.  I have to be able to browse the table alphabetically.  I can't read the whole thing into a recordset, too large.  I want to display 10 names at a time.

So I went into MS SQL Query Analyzer and was able to do this by declaring a cursor, forcing the index and doing fetches.

I want to do this from VB.  I don't think I can do this with ADO so my only option may be ODBC.  I don't know much about it.

If you can help that would be great.
0
 
LVL 1

Expert Comment

by:wizard2072098
ID: 1497775
You are in for a nightmare. ADO can't do it as of yet -- at least not that I've found. You'll have to do extended fetch calls through ODBC which aren't simple.

However, if you use ADO with Server-Side cursors selected, you may be able to get something similar without hammering your client with a huge table. Just set the cursor location to adUseServer in the connection object. That should force the server to bear the brunt of the processing rather than the client.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:sbg
ID: 1497776
Is it really a nightmare using ODBC?  

The routine I have to use in ADO is not that simple either. My worst case scenario is when some browses 'John Smith'.  There can be thousands and thousands or worse if they just type in 'Sm'. I was hoping there would be a better way then working with recordsets.

I am still open to suggestions.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497777
Use RDO. Using an rdoQuery, you can specify the read-ahead buffer size (look at the KeysetSize and RowsetSize properties). Setting this to 1 should allow you to fetch each individual record, but I would recommend a value more like 100. This is all just a wrapper around ODBC, but RDO is easier to deal with than SQLExtendedFetch.
0
 
LVL 2

Accepted Solution

by:
majed100598 earned 400 total points
ID: 1497778
Try to use dbgrid useing the data control you have
0
 

Author Comment

by:sbg
ID: 1497779
I don't think dbgrid will help in this situation. If I have do a query and I end up with 50,000 John Smiths. How would dbgrid help?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497780
You have to love these tables with millions of rows. We get the same kind of thing with our EDI system. I still think RDO or the ODBC API calls will be your ticket.
0
 

Author Comment

by:sbg
ID: 1497781
Can RDO and ADO be used in the same program? I use ado to get detailed info once a person is chosen from the initial list.

If not, what about ODBC and ADO? Would this cause any problems.

0
 

Author Comment

by:sbg
ID: 1497782
Can anyone provide sample ODBC code or good books on the subject?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497783
RDO and ADO can both be used, no problem. One advantage of RDO is that it is a thin wrapper around ODBC. You can use RDO to create your environment and database context and save yourself a bunch of ODBC calls. RDO exposes the database context and statement handles so you can do your own SQLExtendedFetch calls without worrying about just connecting to the data source. I still think you could set things up with an rdoQuery/rdoResultset.

I am not an ODBC wizard, but the best reference I have found so far is MSDN. I could not find a decent book on the subject and would love to hear suggestions.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497784
As for a sample, hold onto your hat. Here is a module to open rdoConnections and rdoRecorsets. This is some test code I used to test the capabilities of an ODBC driver.

Option Explicit
Option Compare Text

Public rdoConn As RDO.rdoConnection
Public rdoRS As RDO.rdoResultset
Public FileNum As Integer

Public rdoenv As RDO.rdoEnvironment
Private Const m_DSN = "DSN=OR0T;"
'
Function OpenConn(ConnectString As String) As RDO.rdoConnection
    Dim tmpConn As RDO.rdoConnection
    Dim sDSN As String
    Dim sConnect As String

    sConnect = ConnectString
   
    Set tmpConn = New RDO.rdoConnection
    With tmpConn
        .Connect = sConnect
        '.CursorDriver = rdUseServer
        .CursorDriver = rdUseOdbc
        '.CursorDriver = rdUseIfNeeded
        '.CursorDriver = rdUseClientBatch
        '.CursorDriver = rdUseNone
    End With

    tmpConn.EstablishConnection rdDriverComplete, False
    Set OpenConn = tmpConn
    Set tmpConn = Nothing
End Function

' Open a resultset
Function OpenRS(Conn As RDO.rdoConnection, strSQL As String) As RDO.rdoResultset
    If Conn Is Nothing Then
        Set Conn = OpenConn(m_DSN)
    End If

    If (InStr(strSQL, vbCrLf)) Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, vbCrLf) - 1)
    End If
    If InStr(strSQL, ";") Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, ";") - 1)
    End If
   
'rdOpenForwardOnly, rdOpenKeyset, rdOpenDynamic, rdOpenStatic
'rdConcurReadOnly, rdConcurLock, rdConcurRowVer, rdConcurValues, rdConcurBatch
'                   FO KS DY ST
'------------------|--|--|--|--
'rdConcurReadOnly  |- |- |- |-
'rdConcurLock      |- |- |- |-
'rdConcurRowVer    |  |  |  |
'rdConcurValues    |- |- |- |-
'rdConcurBatch     |- |- |- |-

    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenDynamic, rdConcurValues)
    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenKeyset, rdConcurReadOnly)
    Set OpenRS = Conn.OpenResultset(strSQL, rdOpenStatic, rdConcurReadOnly)
    'Set OpenRS = Conn.OpenResultset(strSQL, rdOpenStatic, rdConcurValues)
    'Set OpenRS = Conn.OpenResultset(strSQL)
End Function

' Execute a statement
Function ExecSQL(Conn As RDO.rdoConnection, strSQL As String) As RDO.rdoResultset
    If Conn Is Nothing Then
        Set Conn = OpenConn(xlinkClink)
    End If
    If (InStr(strSQL, vbCrLf)) Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, vbCrLf) - 1)
    End If
    If InStr(strSQL, ";") Then
        strSQL = Mid$(strSQL, 1, InStr(strSQL, ";") - 1)
    End If
   
    Conn.Execute strSQL    
End Function

0
 

Author Comment

by:sbg
ID: 1497785
Looks a little different then ado but I will give it a try.
Thanks.
0
 
LVL 4

Expert Comment

by:tomook
ID: 1497786
If you want to try ODBC API calls, check out SQLExtendedFetch, SQLGetRowCount, and SQLExtendedFetch.

The following may provide some help:
http://premium.microsoft.com/msdn/library/sdkdoc/sql/odbc/odbc06_5l6b.htm

MSDN has the complete list of calls if you decide to try that route.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 115
Computer crashes, following error message in event manager 5 199
VBA filters 2 61
How to Add / Edit Windows Menu 4 60
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

773 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