ADO connection to Oracle

Hi All,

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

http://www.excelforum.com/excel-prog...ransposed.html

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?

Thanks,

Dave