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@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

------------------------------------------------------------------------ =
       

        
        Mitch Abaza
        mabaza25_at_DELETEusa.net
        
        clear "delete" to mail

Received on Tue Jun 01 1999 - 23:11:44 CDT