+ Reply to Thread
Results 1 to 1 of 1

ADO connection to Oracle

  1. #1
    Registered User
    Join Date

    ADO connection to Oracle

    Hi All,

    I have some code that people here helped me with earlier in this thread:


    Some new problems have popped up that I was hoping someone could help me with.

    I tried to transfer the workbook to a coworker and he couldn't get the connection to oracle to work. So I was playing around with different connect strings with the file on my machine hoping to find another one that works with our setups so I could try it on his machine.

    When I first set this code up I was using

    Driver={Microsoft ODBC Driver for Oracle}

    and everything was working great. In my experimentation I changed the driver to this:

    Driver={Oracle in OraClient10g_home1}

    and discovered some new problems:

    The connection still works, but now when my macro executes this step:

    ws.Range("A2").CopyFromRecordset sqlresults

    returns only the first row of the resulting query results. Whereas before I changed the connect string, it would put the entire query, multiple rows, into the range starting at A2.

    It seems very odd that the behavior of that would change depending on the connect string used to connect to oracle, but it did because I didn't touch anything else in the code.

    It gets worse, I didn't like this so I changed the connect string back to using Driver={Microsoft ODBC Driver for Oracle} and now it won't connect at all. I get an error message saying

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    What the heck? It's like something in my machine outside of excel changed when I tried that other connect string and it broke whatever was making the original connect string work.

    So now I'm stuck with either a macro that doesn't function because it can't connect to oracle, or a macro that doesn't function because it returns only the first row of a query instead of all of them.

    Can anyone help?


    Last edited by DaveF; 07-09-2010 at 02:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1