Oracle 11g ado connection strings for ODBC (not OLEDB) using excel VBA 64 bit (DSN Less and tnsnames)

Question

Please help. I have researched this for hours. I get some parts to work but not others.

What I am trying to do is write all the connection strings in excel VBA to connect to Oracle 11g database. I don't want to set up the User DSN in ODBC Administrator and I don't want to have to maintain a tnsnames.ora file.

I can get this to work for OLEDB connection strings but I believe this is no longer supported by Oracle so I want to use the Oracle ODBC Driver commands only.

This is what I have got to work (which requires a tnsnames.ora file)

DRIVER={Oracle in OraClient11g_home1};DBQ=MyTNSnamesALias;UID=xxxx;PWD=xxxx

I have tried this also but I get a TNS Protocol error

DRIVER={Oracle in OraClient11g_home1};
Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=xxxx)))
(CONNECT_DATA=(SERVICE_NAME=xxx)(SERVER=DEDICATED)));UID=xxxx;PWD=xxxx

Other useful information might be that i get my connections to work just fine when I use the DSN name as per the ODBC Administrator.

Any suggestions would be greatly appreciated

Thanks

Answer 1

OLEDB Provider from Oracle (Provider=OraOLEDB.Oracle) is still supported, just the provider from Microsoft (Provider=msdaora) is deprecated. Microsoft recommends to use the Oracle provider.

Microsoft provider msdaora does even not exist for 64 Bit.

I think your connection string for Oracle ODBC must be this (without the line breaks):

Driver={Oracle in OraClient11g_home1};
   DBQ=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=xxxx)))(CONNECT_DATA=(SERVICE_NAME=xxx)(SERVER=DEDICATED)));
   Pwd=xxxx;
   Uid=xxxx

Note, for the Oracle driver you must use DBQ instead of Server.

Server is the attribute for the Microsoft ODBC driver (e.g. Driver={Microsoft ODBC for Oracle})