export data from Excel to Oracle using ADO

[hagar]

Hi
I have an excel application and I am starting to work with Oracle.
when the user pushes a button the data in the sheet need to be updated in the Oracle DB. I created a loop on all records in the excel sheet, and I am using a recordset.

[VBA]dim RS as ADODB.Recordset

for lRow = 1 to 10,000
RS.AddNew
RS(1) = cells(lRow,1)
RS(2) = cells(lRow,2)
...
RS(200) = cells(lRow,200)

RS.Update

next lRow
[/VBA]
I used such code in the past with SQL server and it was very fast.
now that i use it for Oracle it takes almost 2 minutes for 10,000 records. that is a lot of time for my application. is ther a way to make it faster?

[stanl]

Are you using Oracle Drivers or Microsoft drivers for Oracle or an OLEDB Provider?

[hagar]

the Provider in the connection string is MSDAORA

[stanl]

the Provider in the connection string is MSDAORA

hmmm. in my experience with Oracle, I have not felt the Provider was very efficient, and believe it or not I got better performance from the Microsoft Driver than Oracle's. Just .02

[hagar]

how do i obtain this driver?
can you write the syntax in the connctions string?

[Oorang]

How to get the driver: http://support.microsoft.com/kb/175018
How to write the connection string: http://www.connectionstrings.com/?carrier=oracle

Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

[stanl]

My bad! The OP was already using the driver; I think the version in the snapshot I posted is MDAC 2.6 or greater where MS made some improvements. Stan

[stanl]

as an FYI - you can google OraOLEDB.Oracle.1 which can be downloaded. It recognizes the newer Oracle Data Types.

[vba]
oracleConn.ConnectionString = "Provider=\"OraOLEDB.Oracle.1\";
User ID=scott;Password=tiger;
Data Source=yourSource;
Extended Properties="";
Persist Security Info=False";

[/vba]

[hagar]

Thanks. I tried this:
Provider="OraOLEDB.Oracle.1";
and in the example I gave in the original message I was able to reduce the speed from 1:05 to 00:27 seconds!
Some parts of my code which worked before are not working with the new driver but I hope i can get them all to work.