Error in Excel VBA ODBC connection to a remote Oracle Database using Microsoft ODBC Driver.

When you installed the 11g client, did you select the ODBC Drivers?  I don't believe they are installed by default.

Rerun the installer and install them.  Then create a DSN using the Oracle drivers.

We have pre-packaged installers in our organization. I am unable to change anything. But my best guess is oracle drivers must be there. Because about a year ago we were using oracle driver for the same purpose, we had changed it to Microsoft because that wasn't very stable then!

Is there a way for me to verify whether Oracle driver is there or not. Secondly how does on create DSN. I am not an expert in this at all.

When the Oracle Drivers are installed, you can use tnsping to test the connection to your database.

>>When the Oracle Drivers are installed, you can use tnsping to test the connection to your database.

tnsping has nothing to do with what drivers are installed.  It is a utility that might be installed with the client depending on what options you selected.  It isn't always installed.

>>Secondly how does on create DSN. I am not an expert in this at all.

With the ODBC administrator but reading the original question again, you aren't using them.

>>Is there a way for me to verify whether Oracle driver is there or not.

You can however use the ODBC admin tool to see if the Oracle ODBC driver is installed.

If you are running 64Bit, there are two.  You need to run the correct one for the Bit version of Excel you are using.

https://support.microsoft.com/en-us/kb/942976

If you see the Oracle ODBC drivers installed, just change:
Driver={Microsoft ODBC for Oracle};

and use the verbiage you see for it.

Thanks, in ODBC admin tool, in drivers tab, I see OracleClient11g_Home. Is this a confirmation that Oracle driver is installed?

Yes, the ODBC driver seems to be installed.  

The 64 in the driver concerns me.  The Bit version installed needs to match the Bit version of Office you have installed.

If it does, then try:
"Driver={OraClient11g_Home64}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL = TCP)(HOST=lnfxdwp1.uk.db.com)(PORT=1669))(CONNECT_DATA=(SID=LNFXDWP1)));uid=USERNAME; pwd=PASSWORD"

where OraClient11g_Home64 is the exact driver name you see in the ODBC admin tool.

slightvw, I need serious help with the new query using native Oracle client.  If you or someone can paste the exact connection string here, I will be obliged.

I tried using your suggestion literally and replaced
Driver={Microsoft ODBC for Oracle};
with
Driver={Oracle in OraClient11g_Home64}
but that didn't work. "Data source name not found and no default driver provided".
I do not have time to tinker with this, so if someone uses the string in my question, modifies it and tells me the new string, that will be great. If it helps, I am putting a bit more code here.

sConn = Range("sConn").Value    
    Set connObj = CreateObject("ADODB.Connection")
    connObj.Open sConn

Select allOpen in new window

>>If you or someone can paste the exact connection string here, I will be obliged.

I don't know the exact connect string for you to use because I don't know your systems.  There isn't a generic connect string that can be used across all systems.

>>but that didn't work. "Data source name not found and no default driver provided".

Are you running 32 or 64  Bit Office?
When you ran the ODBC admin tool, did you run the correct Bit version?
The 64 in the driver you showed tells me that it might be the 64Bit Oracle driver.  If it is, you need to be using 64Bit Office.
If you are running 32Bit Office, you need the 32Bit Oracle drivers.

ok, reached next step. Thanks for emphasizing on 64 vs 32. I was presuming that excel 2007 is 64 bit but it isn't. So I launched the 32 bit version of odbc admin tool. From there I found driver name as {Oracle in OraClient11g_Home32}

It did seem to find the driver but first time it gave some error like it couldn't initialize the driver while loading or something. I shut the excel and re-opened. And this time the error message was frightening.
"Catastrophic error"

What do I do now?

Might be an attribute difference.
Change CONNECTSTRING=
to
SERVER=

There is a great site for connection strings:
https://www.connectionstrings.com/oracle/

Changing CONNECTSTRING to SERVER didn't work.

But it least I have been able to capture what error comes. On a fresh excel launch, when I click my macro button
I get these two popups one after the other.  
After clicking Debug it goes to the same line.

connObj.Open sConn 

Select allOpen in new window

If I try to execute this line again, then I get "catastrophic error".
Has something gone corrupt in my Oracle?

Check your machines PATH statement.  Make sure c:\app\Oracle\Product\11.2.0\client32\BIN is before any other Oracle folders.

No! Another Oracle path (client_64) is before this!

But unfortunately, I can't edit system paths. Unless you suggest a work-around, I will have to postpone this, until I get
hold of IT support guys to change it for me.

I have the right to change environment variables for my own account - if that helps.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.