1

I'm running a VBA script I used to use when connecting to Oracle database through Excel 32-bit but now I'm using Excel 64-bit.

VBA code:

Set cn1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cn1.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=BR1P1;Uid=BR1USER;Pwd=myPass"
cn1.Open
rs1.Open Query, cn1

When I execute "cn1.open" I get this error: "Run-time error'-214746... [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Then I tried to solve adding a new data source: ODBC Data Source Administrator (64-bit)> File DSN> click in Add...> from list I select: Oracle em OraClient11g_home1 ("Oracle em" means "Oracle for", my OS in in portuguese)> Next> browse to the file: "C:\Windows\System32\odbcad32.exe"> Next> Finish

Doing so system will give me the driver name: "Oracle em OracleClient11g_home1"

Afterwards it will prompt for Service Name, User Name and Password. I inform server Name according to my TNSNAMES.ORA

Back to VBA I changed the code according to the driver name:

cn1.ConnectionString = "Driver={Oracle em OracleClient11g_home1}"

Now when execute "cn1.Open" in VBA I receive this new error message:

"Run-time error'-214746... [Oracle][ODBC]ORA-12560: TNS:protocol adapter error"

My TNSNAMES.ORA file is like this:

BR1P1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.com)(PORT = 1575))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR1P1)))

Well I'm just stucked on this. Please I appreciate any help on this. It's being a pain after I changed my MS Office to 64-bit version.

HK boy
  • 1,398
  • 11
  • 17
  • 25
Eliton
  • 31
  • 2
  • 3
  • Where did you store the `tnsnames.ora` file? Create an Environment Variable `TNS_ADMIN` pointing to this folder. – Wernfried Domscheit Nov 12 '18 at 12:38
  • File is located in "C:\oracle\product\11.2.0\client_1\Network\Admin\". And I already created a variable named "TNS_ADMIN" pointing to this exact path. Anyway, I've found the solution.Basically for my connection string I changed – Eliton Nov 12 '18 at 14:04
  • Sorry, see the full comment: File is located in "C:\oracle\product\11.2.0\client_1\Network\Admin\". And I already created a variable named "TNS_ADMIN" pointing to this exact path. Anyway, I've found the solution. Basically for my connection string I added DBQ, UID, and PWD like this: cn1.ConnectionString = "Driver={Oracle em OraClient11g_home1}; DBQ=BR1P1; PUID=myUser; Pwd=myPwd". And there is a main detail: I used "DBQ" word, not "Server" like I normally would. Not sure why but it worked. – Eliton Nov 12 '18 at 14:12
  • Yes, the connection string format for [Microsoft ODBC Driver](https://learn.microsoft.com/en-us/sql/odbc/microsoft/connection-string-format-and-attributes?view=sql-server-2017) is different to [Oracle ODBC Driver](https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1183) – Wernfried Domscheit Nov 12 '18 at 14:21
  • Great! thanks you so much Wernfried for the complete information regarding ConnectionString formats and off course all the help. – Eliton Nov 12 '18 at 17:03

1 Answers1

2

The Microsoft ODBC for Oracle exist only for 32-bit thus you cannot use it for 64-bit Excel.

If you like to use the 64-bit ODBC driver from Oracle then you must install it. Download the driver from 64-bit Oracle Data Access Components (ODAC) Downloads

The ConnectionString format of the Oracle ODBC Driver is different to the format of the Microsoft ODBC driver, see

So, for Oracle your ConnectionString 

Driver={Microsoft ODBC for Oracle};Server=BR1P1;Uid=BR1USER;Pwd=myPass

will be 

Driver={Oracle em OracleClient11g_home1};DBQ=BR1P1;Uid=BR1USER;Pwd=myPass
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Well.. I understand that and I already installed 64-bit ODAC. I created a new data source to connect using ODBC Data Source Administrator (64-bit) but it's no working. So my issue is to use 64-bit ODBC through VBA. – Eliton Nov 12 '18 at 12:29