Receiving error in VBA Excel 2013 ORA-12154: TNS:could not resolve the connect identifier specified

Question

_{Saturday, March 15, 2014 12:59 PM}

Hello,

I'm trying to connect Excel 2013 VBA on a Windows 8 laptop to Oracle XE 11 on a Windows server.  I also have Apex on that server. The server seems to be configured properly, and is accessible via browsers from many clients.  I also have this Excel workbook functioning properly when run from Excel 2007 VBA on the Windows server. 

On the Windows 8 laptop, I installed Oracle Client 12.1.0.

The TNSNAMES.ORA on the laptop looks like this:

XE =
 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.yyy.com)(PORT = 1521))  
  (CONNECT_DATA = 
     (SERVER = DEDICATED) 
     (SERVICE_NAME = XE) 
   )
  )
EXTPROC_CONNECTION_DATA = 
 (DESCRIPTION = 
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
   ) 
   (CONNECT_DATA = 
     (SID = PLSExtProc) 
     (PRESENTATION = RO) 
   ) 
 )
ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

On this laptop, I can use SQL Plus and connect to the database on the server using MY_NAME/MY_Password@XE when prompted by SQL Plus.  I can see users, tables, etc through SQL Plus.  Therefore I don't think the issue is with a firewall, virus protection or router setting.

In Excel's user interface, I setup a data connecting using: Data / From Other Sources / From Data Connection Wizard / ODBC DSN / odbc_excel / And provided parameters for Microsoft Parameters for ODBC Connect:

Username: (UserName in Oracle XE

Password: (UserName's password)

Server: XE

After providing that, I see the complete unfiltered list of tables,  I can select a table and have it populate an Excel spreadsheet.

The problem comes when I go to use the VBA code in Excel 2013.  This exact code worked well in Excel 2007 when it ran from the server hardware.  It doesn't work on the Windows 8 laptop with Oracle client and Excel 2013 VBA.  Here are the references I've selected, which is reflective of the references used in Excel 2007 VBA.

The code looks something like this:

Option Explicit
Public lngStartRow As Long
Public lngEndRow As Long
Public strCycle As String

Sub InsertMassLogTimeinOracle()

'DECLARE VARIABLES
Dim objConnection As ADODB.Connection
Dim objRecordSet As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String

'Load the UserForm called RowsToLoad
RowsToLoad.Show
Unload RowsToLoad

Set objConnection = New ADODB.Connection
Set objRecordSet = New ADODB.Recordset
 
strConnection = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=odbc_excel;Password=my_password"
objConnection.ConnectionString = strConnection
 
 
'LOGIC
 
'Open the database connection
objConnection.Open

When the objConnection.Open statement is executed, I get the following error: in the VB editor:

Thanks for taking a look at this.

All replies (1)

_{Tuesday, March 18, 2014 7:34 AM ✅Answered}

Hi Budget Programmer,

Here are some articles I searched which may give you some help:

“ORA-12154: TNS: could not resolve the connect identifier specified” error while creating a linked server to Oracle

How To Troubleshoot an ASP-to-Oracle Connectivity Problem

By the way, since this question is also related to Oracle you could also consider to post this question to OTN Community > Windows and .NET > ODBC to get more help.

Regards,

Jeffrey

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.