Home -> Usenet -> c.d.o.tools -> Re: fastest way to extract Oracle data with VB?

Re: fastest way to extract Oracle data with VB?

From: Rick Losey <rlosey_at_hotmail.com>
Date: Tue, 1 Jun 1999 22:11:44 -0600
Message-ID: <Rb253.174$4r6.43592_at_news.uswest.net> 













Mitch,


 


If you use VB6, be sure to have SP2 or higher 
installed.


 


There are two ways to do this:


 


1.  Straight ADO (preferable over RDO).  This is the 
simplest method.  Beware that if you are using ADO 2.0 then you MUST use 
the MS ODBC driver for Oracle.  ADO 2.1 will NOT work reliably with Oracle 
at all.  ADO 2.1.1 will work.


 


2.  This is the faster method.  Use oo4o for your 
access to Oracle, then use ADO to populate your MSAccess 
table. 


     oo4o  (Oracle Objects for OLE) 
is faster because it does not use ODBC.  It talks directly to 
OCI.


 


Hope this helps.


 


Rick



    
Oracle NetNews wrote in message <7j28bn$gle$1_at_bgtnsc03.worldnet.att.net>...

    
Mitch, 

    
 

    
    Don't waste you're time 
    using the DAO. Convert all your DAO codes to RDO, if your system is web 
    enabled, convert it to ADO.

    
 

    
 

    

        
Mitch wrote in message <7j1qc3$h0q$1_at_news.doit.wisc.edu>...

        
 

        

--I need to extract about 1000 records from an Oracle 8 
        database into Access 97 using Visual Basic 6.  My current code uses 
        standard DAO methods to accomplish this task but it takes roughly 2 
        minutes to populate the Access 97 table with the Oracle records.  
        This seems too long to me...Is RDO or ADO faster?

        
 

        
I included some sample code.  Please feel free to suggest any 
        enhancements you can think of:

        
 

        

        

        

        
Private Sub Command_Click()

        
 

        
Dim SourceRcrdSet As Recordset, DestRcrdSet As Recordset
Dim 
        Connect as Connection

        
Dim Fieldloop As Integer

        
Dim DB as Database

        
 

        
Set DB = OpenDatabase("C:\Accting.mdb")

        
Set Connect = OpenConnection("Oracle", 
        "ODBC;DSN=ITS2;UID=test;PWD=test")

        
Set SourceRcrdSet = Connect.OpenRecordset("Select * from 
        OracleTable")

        
Set DestRcrdSet = DB.OpenRecordset("AccessTable")

        



        
With DestRcrdSet

        
        Do Until 
        SourceRcrdSet.EOF
                
        .AddNew

        
                    
        For Fieldloop = 0 To DestRcrdSet.Fields.Count - 1 

        
                           
        .Fields(Fieldloop).Value = 
        SourceRcrdSet.Fields(Fieldloop).Value
                    
        Next Fieldloop

        
                
        .Update

        
                 
        SourceRcrdSet.MoveNext
        
        
        Loop

        
Wend

        
End Sub

        

        

        

        
 

        
        
 

        
clear "delete" to 
mail

Received on Wed Jun 02 1999 - 06:11:44 CEST












Original text of this message