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

  • Question

  • 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.

    Excel 2013 VBA references

    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.


    Saturday, March 15, 2014 12:59 PM

Answers