Solved

Looking for alternative to ADO data control

Posted on 2002-07-18
7
301 Views
Last Modified: 2013-11-23
I'm a novice VB6 programmer developing a front end to an Oracle database. I remember in an introductory course an instructor saying "here is the ADO data control and this is what it can do...but nobody uses it because it is so slow." I'm trying to use it and finding that to be the case.

Can anyone point me in the direction I need to go to learn how to access the Oracle database and create recordsets, etc. without using the data control? My application has to be faster.

Thank you

Eric Olsen
0
Comment
Question by:iameric
7 Comments
 
LVL 4

Accepted Solution

by:
gencross earned 50 total points
ID: 7163311
The fastest way is to use SQL Statements to return/update/delete data in the database.  This is a little more difficult to use, but much faster.  You will want to open an ADO database connection and use a SQL statement to return a recordset.

Example.  This example opens a SQL Server database.  You will need to modify the connection string and provider for Oracle(there is help in the VS help or Microsoft's site or the web for the correct provider and connection string)...

Dim objDBConn as ADODB.Connection
Dim rstTemp as ADODB.Recordset

With objDBConn
        .CursorLocation = adUseServer
        .ConnectionString = "driver={SQL Server};server=ehssqldev;uid=sa;pwd=pass;database=newdb"
        .Provider = "sqloledb"
        .CommandTimeout = 60 'Time out in one minute
        .Open

        If objDBConn.State <> adStateOpen Then
            'Connection not open successfully
            Set objDBConn = Nothing
        End If

        'Once the connection is open a recordset with data in it
        set rstTemp = .execute("SELECT * FROM myTable")
End With

'Go through all records of the recordset
With rstTemp
    Do while not rstTemp.EOF
        debug.print .Fields("myField").Value
        .MoveNext
    Loop
       
    .Close
End With

       
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7163505
As gencross has pointed out, the alternative to the data controls is writing code.  But it should also be pointed out, that the advantage is not only in speed, but also in far better control.  Trust me, once you dump the Data Control there is no looking back.

Anthony
0
 
LVL 4

Expert Comment

by:AlonHirsch
ID: 7164244
I agree with everyone - the data control is an 'easy' solution for lazy people.
The fastest results will be obtained by using stored procedures in Oracle, or if you donot want to use stored procedures, then use prepared commands with ADO :
<from gencross example above>
Dim objDBConn as ADODB.Connection
Dim rstTemp as ADODB.Recordset
dim cmdSelect as ADODB.Command

With objDBConn
       .CursorLocation = adUseServer
       .ConnectionString = "driver={SQL Server};server=ehssqldev;uid=sa;pwd=pass;database=newdb"
       .Provider = "sqloledb"
       .CommandTimeout = 60 'Time out in one minute
       .Open

       If objDBConn.State <> adStateOpen Then
           'Connection not open successfully
           Set objDBConn = Nothing
       End If
End With

set cmdSelect = new adodb.command

with cmdSelect
   .ActiveConnection = objDBConn
   .prepared = True
   .CommandType=adCmdText
   .CommandText="SELECT * FROM MyTable"
end with

set rstTemp = cmdSelect.Execute

'Go through all records of the recordset
With rstTemp
   Do while not rstTemp.EOF
       debug.print .Fields("myField").Value
       .MoveNext
   Loop
       
   .Close
End With

The reason this will speed up your application over the previous approach is that each time you issue the SQL SELECT, Oracle will have to compile the sql and then execute it. When using command objects and prepared statements, the compile only happens the first time you query the recordset. All subsequent SELECTS will use the precompiled (prepared) version.

Ideally, the command object should be declared as a module level variable and defined when the form is loaded.
When you click on the 'Populate' button, you simply use the recordset.

I've done this in dozens of projects and it works wonders in terms of performance.
You can use command objects for SELECT, INSERT, UPDATE and DELETE statements and can also pass parameters :
SELECT * FROM MyTable WHERE sName LIKE ?
This is done to enable searching for example ...

HTH,
Alon
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.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7164846
AlonHirsch,

Not all providers support the "Prepared" property.  Further, even if it is supported, if the SQL statement is called infrequently, there is not much to gain as will take a hit the first time the call is made.  But your points are well made and should be taken in account when developing an application.  One of the challenges with Oracle is that it is difficult to return resultsets from stored procedures.  A workaround I have seen, is precisely the one you advocate:  Prepared statements.

Anthony
0
 

Author Comment

by:iameric
ID: 7188989
Thank you for the valuable feedback. I now have a much better idea how to attack the problem.

Eric
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8014013
Hi iameric,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept gencross's comment(s) as an answer.

iameric, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8096065
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 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

17 Experts available now in Live!

Get 1:1 Help Now