Excel ODBC Connection Issues

I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:

I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.

If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.

Also, if I attempt to establish another connection following the  "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.

I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
I also use this data source in Microsoft Access without any problems.

Any thoughts why I'm having these connections issues?
Thanks in advance.

This is a long shot as I have not used an Oracle data source, but using an iSeries ODBC access I lose the ability to bring data in a second time when the connection times out. i don't actually think this is an ODBC driver timeout, more a timeout on the iSeries (or Oracle box in your case). Do you have to sign on to your oracle app after a few minutes of idle for example.

Just a thought

Nick,

I do not need to sign into the Oracle DB after a few minute of idle activity so I wouldn't think that would be the case. 

OK

Have you checked if there are any updates to the Oracle driver on their site and what version of excel are you using?

I would also look at the properties of the data connection and just up any timeouts there may be for example.

If you have a fairly recent version of Excel you may be able to use OLEDB as opposed to ODBC if that works for you. Of course you may get the same problems but I alternate between these depending on what I find with the iSeries

Hi Schulermx,  

I've had similar issues with Oracle in the past where Toad would function fine, but any direct connections from VBA code would crap out (that's the technical term).

We finally traced it down to an upgrade on the Oracle Client software inconsistent Environment Variable values between the two installations (My Computer > System Properties > Advanced > Environment Variables). From what I can recall, some of the path settings pointed to the old installation whilst others pointed to the upgraded installation. This may be worth investigation.

Regards - Marcus from London

@Marcus:  My Environmental Variables are consistent. Good thing to check though

@Nick: I'm using Excel 2003 and using Oracle drivers provided with the Oracle "instant client" for Oracle 9.

Do you have any idea if I would need to download different drivers to utilize OLEDB? I'd like to have this Excel application on a couple other machines and I'd like to avoid having to install a large Oracle client.

However, I'm more than willing to try a different approach I'm just unaware of how OLE can be utilized. 

You can invoke any OLEDB drivers loaded via Import External Data>Import data... this will throw up a dialog for existing connections, press new and see if anything looks like an Oracle driver from there.

You can see the Oracle driver if you follow along with my site here:

http://www.nickhodge.co.uk/gui/datamenu/importexternal.htm

Not sure about the version you need, best check the Oracle site and see there

Success!

 This seems to do what I'm looking for. Thanks for the help.

Great, I've marked it as answered, let us know if that changes