PDA

View Full Version : export data from Excel to Oracle using ADO



hagar
09-11-2008, 03:09 PM
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.

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

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
09-11-2008, 06:04 PM
Are you using Oracle Drivers or Microsoft drivers for Oracle or an OLEDB Provider?

hagar
09-11-2008, 06:08 PM
the Provider in the connection string is MSDAORA

stanl
09-12-2008, 04:52 AM
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
09-12-2008, 06:45 AM
how do i obtain this driver?
can you write the syntax in the connctions string?

Oorang
09-12-2008, 07:54 AM
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
09-13-2008, 10:39 AM
My bad! :motz2: 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
09-13-2008, 11:50 AM
as an FYI - you can google OraOLEDB.Oracle.1 which can be downloaded. It recognizes the newer Oracle Data Types.


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

hagar
09-15-2008, 03:10 PM
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.