Oracle ODBC TNS-less connection string (for 64-bit Excel)

excel oracle odbc connection-string tnsnames
Josh picture Josh · Oct 8, 2014 · Viewed 13.2k times · Source

Is there a 64-bit Oracle ODBC driver that supports TNS-less connection (or EZ-Connect) convention? I used to use "Microsoft ODBC for Oracle" which allowed a TNS-less connection string, but that has been depreciated for a long time and there is no 64-bit version (i.e. it won't work for 64-bit Office/Excel).

I've had the experience that user's in my organization sometimes have a missing or invalid tnsnames.ora file. I want to use a DSN-less connection-string in that does not require tnsnames.ora that can be successfully executed on any computer with the proper Oracle drivers installed. This is an example of my old DSN-less, TNS-less connection string that used the depreciated "Microsoft ODBC for Oracle" driver (still works for users on 32-bit office):

DRIVER={Microsoft ODBC for Oracle}; UID=myun; PWD=mypass;
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
       (HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SID=mysid)));

I have been trying to connect with Oracle in OraClient11g_home1 ODBC drivers from Oracle but no matter how I try (including EZ_Connect) I can not get it to work without it needing tnsnames.ora, I constantly get this pop-up asking me to specify a service name anyway:

enter image description here

I have tried using EZ-Connect and many other variations of specifying the Oracle server IP/hostname directly like below but have had no luck:

Driver={Oracle in OraClient11g_home1};Server=//OracleIPAddress:1521/MySID;
Uid=myUsername;Pwd=myPassword;

Answer

Wernfried Domscheit picture Wernfried Domscheit · Oct 8, 2014

For the service name you can simply insert the whole TNS-String, e.g. SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SID=mysid)));

Enclose it by double quotes and remove any carriage-returns and space charaters.

Related questions

Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified in ms odbc administrator

I want to add two service names in one tnsnames.ora file and getting error in below 2 image in "Microsoft ODBC Driver Configuration".

Read More
How to escape comma and double quote at same time for CSV file?

I am writing a Java app to export data from Oracle to csv file Unfortunately the content of data may quite tricky. Still comma is the deliminator, but some data on a row could be like this: | ID | FN | LN | …

Read More
Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include: Set up an Oracle Instant Client …

Read More