Re: fastest way to extract Oracle data with VB?

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