0

Trying connect to an Oracle Database 12c Enterprise Edition 64-bit with Excel VBA.

The client machine has the following 32-bit drivers installed:

  • Microsoft ODBC for Oracle
  • Oracle in OraClient11g_home1
  • Oracle in OraClient12Home1_32bit

And the following 64-bit drivers installed:

  • Oracle in OraClient11g_home1
  • Oracle in OraClient12Home1

The file has the following reference:

  • Microsoft ActiveX Data Objects 6.1 Library

I've tried multiple formats of the connection string but nothing works.

'This gave the error "[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error"
cs = "Driver={Oracle in OraClient12Home1}; UID=myuid; PWD=mypwd; SERVER=myhostname/myservicename;"

'This gave the error "[Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified"
cs = "Driver={Oracle in OraClient12Home1}; UID=myuid; PWD=mypwd; SERVER=myhostname; DBQ=myservicename;"

'This gave the error "[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error"
cs = "Driver={Oracle in OraClient12Home1}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservicename)));UID=myuid; PWD=mypwd;"

'This would crash Excel
cs = "Driver={Oracle in OraClient11g_home1}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservicename)));UID=myuid; PWD=mypwd;"

I've also tried variations using the following drivers in the connection string

  • Microsoft ODBC for Oracle
  • OraOLEDB.Oracle
  • Oracle in OraClient12Home1
ptownbro
  • 1,240
  • 3
  • 26
  • 44

2 Answers2

1

Thanks for your help everyone and sorry for the delayed response (had multiple work related "fires" I was putting out).

Anyway... I found a solution that worked for me. I don't think the problem was having multiple drivers on the same machine because I'm now able to connect without changing anything except the connection string method I used.

While I'm still not sure why the "driver" method of my connection string didn't work, I was able to use a "provider" based connection string and that worked.

'Using OraOLEDB.Oracle.1.
cs = "Provider=OraOLEDB.Oracle.1;User ID=myuid;Password=mypwd;Data Source=myhostname/myservicename;"

'Using OraOLEDB.Oracle.
cs = "Provider=OraOLEDB.Oracle;User ID=myuid;Password=mypwd;Data Source=myhostname/myservicename;"

'Using OraOLEDB.Oracle as a TNS-less connection string.
cs = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostname)(PORT=myport)))(CONNECT_DATA=(SERVICE_NAME=myservicename)(SERVER=DEDICATED)));User Id=myuid;Password=mypwd;"

'Using variation of the above with a port included.
cs = "Provider=OraOLEDB.Oracle.1;User ID=myuid;Password=mypwd;Data Source=myhostname:myport/myservicename;"
ptownbro
  • 1,240
  • 3
  • 26
  • 44
0

I think it is a bad idea to have more than one version of Oracle Client installed, i.e. one 32-bit and one 64-bit.

For OLE DB (i..e "OraOLEDB.Oracle") it is even not possible to have more than one version due to limitation of COM where it is based on (unless you would register the DLL every time before you run an application).

In general you can use either ODBC or OLE DB, both should work.

The most important point is: the architecture (i.e. 32-bit or 64-bit) of your Excel must be the same as the architecture of your Oracle driver! The architecture of your database does not matter.

ODBC and OLE DB drivers are provided by Microsoft ("Microsoft ODBC for Oracle" and "MSDAO") and by Oracle (e.g. "Oracle in OraClient12Home1" and "OraOLEDB.Oracle")

The Microsoft drivers are old and deprecated, you should prefer the drivers from Oracle. Microsoft drivers exist only for 32-bit, so if your Excel is 64-bit then you are even forced to use the Oracle drivers.

Another note, the version of Oracle drivers (ODBC and OLE DB) has to be the same as the version of your Oracle client, most likely you messed it up when you installed three different Oracle clients.

In case you need to install 32-bit and 64-bit client, follow this instruction: BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Hm. First I've heard having 32-bit and 64-bit installed at same time. In contrast I've heard the opposite when connecting to multiple versions of Oracle databases. To clarify, I've only installed two versions: one for 11g and one for 12c and the 32-bit and 64-bit for each. And it all works perfectly except VBA (e.g. I can connect with Linked Servers in SQL Server, with Pivot Tables, in SQL Developer, and in SQL Workbench) – ptownbro Jan 15 '19 at 17:24
  • Typically an Oracle Client can connect to a database of +/- 2 major releases. For example with an 11.2 Client you can connect to database from 9.2 to 18 (with exception of 10.1), see [Client / Server Interoperability Support Matrix for Different Oracle Versions](https://support.oracle.com/epmos/faces/DocumentDisplay?id=207303.1) – Wernfried Domscheit Jan 15 '19 at 18:08
  • According to your list you have installed 4 different clients. 11g 32-bit, 11g 64-bit, 12c 32-bit and 12c 64-bit. The tools you listed are only using the core DLL `oci.dll` (the one which appears first in your `%PATH%`). However in Excel you have to use ODBC or OLE DB which need more files. Here you have a mismatch. – Wernfried Domscheit Jan 15 '19 at 18:21