Search code examples
excelvbaoracle-databasedatabase-connectionoracle12c

Excel Connect to Oracle 12c Using VBA


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

Solution

  • 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;"