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})

Answer 2

There are no possibility to connect using Oracle ODBC driver without TNS alias already configured in tnsnames.ora file.

All configuration steps mentioned in Oracle ODBC Driver documentation requires it:

1. Connecting to DataSource
2. Configuring theDataSource
3. Oracle ODBC Driver Configuration Dialog Box

Also in documentation for SQLDriverConnect implementation DBQ parameter noted as required, and passing a name of TNS alias in this parameter is only method to specify server to connect to.
Because it's a common API function used by all clients of Oracle ODBC Driver, there are no possibility that some other interface (COM-object, configuration dialog or something else) may accept different parameters for connection.

There are no such possibility in Oracle Objects for OLE too.

---

I can't find anything about cancelling of Oracle Provider for OLEDB support. It has been released as a part of latest version of ODAC: "ODAC 12c Release 4 (12.1.0.2.4)" and this release is a top news topic at related section of Oracle site at the moment.
There are some improvements in latest versions and support for usage from .NET applications.

Also, it works (at least for me):

Const hostName = "server_host"
Const portNo = "1521"
Const srvSID = "ORASERVERSID"
Const usrID = "login"
Const usrPwd = "password"

Sub con_Oracle_OLEDB()

  strDriver = "Provider=OraOLEDB.Oracle;"
  strParams = "Data Source=(DESCRIPTION=(CID=MyVbaApp)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + ")))(CONNECT_DATA=(SID=" + srvSID + ")));"

  strCon = strDriver + strParams + strUser

  ' Open   the above connection string.
  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  con.ConnectionString = strCon
  con.Open

End Sub

Based on the above, I recommend to stay with OLEDB Provider, just update ODAC to a latest version.

---

If you don't want to use OLEDB at all, there are a variant with Microsoft ODBC driver("Driver={Microsoft ODBC for Oracle};") with detailed server parameters specification in CONNECTSTRING:

Const hostName = "server_host"
Const portNo = "1521"
Const srvSID = "ORASERVERSID"
Const usrID = "login"
Const usrPwd = "password"

Sub con_Microsoft_ODBC_for_Oracle()

  strDriver = "Driver={Microsoft ODBC for Oracle};"
  strParams = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + "))(CONNECT_DATA=(SID=" + srvSID + ")));"
  strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";"

  strCon = strDriver + strParams + strUser

  ' Open   the above connection string.
  Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  con.ConnectionString = strCon
  con.Open

End Sub

Of course, this variant has its own drawbacks. At least, there are a much more probability of support cancellation from Microsoft's side than from side of Oracle.

Answer 3

Thanks for the answer. I was trying to use MSDAORA with 64 bit. I am using OLEDB as per the example. Except that the oledb example doesn't populate strUser, but the next example does: strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";" I am getting an invalid user id password, but I am sending the correct one. This is what I am sending (I replaced the password with xxx for security reasons) Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.23.32)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=rmsuat)));Uid=rms;Pwd=XXX;