Consulting

Results 1 to 9 of 9

Thread: export data from Excel to Oracle using ADO

  1. #1
    VBAX Newbie
    Joined
    Sep 2008
    Posts
    4
    Location

    export data from Excel to Oracle using ADO

    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?

  2. #2
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Are you using Oracle Drivers or Microsoft drivers for Oracle or an OLEDB Provider?

  3. #3
    VBAX Newbie
    Joined
    Sep 2008
    Posts
    4
    Location
    the Provider in the connection string is MSDAORA

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by hagar
    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

  5. #5
    VBAX Newbie
    Joined
    Sep 2008
    Posts
    4
    Location
    how do i obtain this driver?
    can you write the syntax in the connctions string?

  6. #6
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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;
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  7. #7
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  8. #8
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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]

  9. #9
    VBAX Newbie
    Joined
    Sep 2008
    Posts
    4
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •