0

I'm trying to connect my Excel to Oracle with this connection string in VBA:

And I get error msg below:

Run-time error '-2147467259 (800004005)': ORA-12154: TNS:could not resolve the connect identifier specified

Using: - Excel 2016 64-bit - Windows 10 64 bit - Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit - PL/SQL Release 12.1.0.2.0 - CORE 12.1.0.2.0 - TNS for Linux: Version 12.1.0.2.0 - NLSRTL Version 12.1.0.2.0

Excel 64-bit & oracle is provided by company, hope can settle it without change excel to 32-bit. Any help will be appreciated. I actually already tried to settle it for two days and see no hope at all. Thanks for your time.

Not sure if is the driver i used is 32-bit? Microsoft ODBC for Oracle, MSORCL32.DLL Oracle in Client_11_Std_x86_1, SQORA32.DLL

Dim cnPubs As ADODB.connection
Set cnPubs = New ADODB.connection
Dim strConn As String
With cnPubs
    strConn = "Provider=OraOLEDB.Oracle;" & _
    "Data Source=" & sSource & ";" & _
    "User ID=" & sAC & ";Password=" & sPW & ";"

    .Open strConn
    .CommandTimeout = 0
End With
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
user8729759
  • 3
  • 1
  • 2
  • Check your database name, i.e. `sSource`. The drivers should be fine as you get an **Oracle** error, i.e. the DLL's have been loaded successfully. Do you use an `tnsnames.ora` file? Where is it located? Did you set `TNS_ADMIN` variable? – Wernfried Domscheit Apr 03 '19 at 11:51
  • @Wernfried Domscheit Thanks for your reply. i check the sSource again, which i confirm it's fine. Previously i use PROVIDER=msdaora with eSource and it was working fine for windows 7 + 32-bit. I was able to find tnsnames.ora file in C:\app\oracle & C:\app\oracle_x86, but not sure how to use it. Not sure how to set tns_admin variable as well. Sorry think i am too noob in oracle. – user8729759 Apr 07 '19 at 07:58
  • I tried to add TNS_ADMIN in C:\app\oracle_x86\product\cl11gr2_std_x86\network\admin\sample, but seems not working still...[Setting the TNS_ADMIN environment variable](http://www-01.ibm.com/support/docview.wss?uid=swg21131629) – user8729759 Apr 07 '19 at 08:15
  • I doubt that the **sample** `tnsadmin.ora` file will work. – Wernfried Domscheit Apr 07 '19 at 09:57
  • "sSource" is usually just an alias for the full database tns which is typically rather long and inconvenient, e.g. `(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.0)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA12)))`. These aliases are defined in your `tnsadmin.ora` file. When you say is was working with msdaora providers then it should be fine. Just point value of `TNS_ADMIN` variable to the location of your `tnsadmin.ora` file. – Wernfried Domscheit Apr 07 '19 at 10:03
  • @Wernfried Domscheit I realised my system variable already set TNS_ADMIN in C:\app\oracle_x86. Is that for 64-Bit? Is there anyway i can skip tnsadmin.ora? Thanks a lot. – user8729759 Apr 08 '19 at 07:44
  • Well, if it is pointing to C:\app\oracle_**x86** then most likely it is 32-bit. However, `tnsnames.ora` is plain text file, it does not matter. Does this file contain the definition for `sSource`? Yes, you can skip the `tnsnames.ora` file, just put the full address (like `(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.0)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA12)))`) instead of the alias to your connection string. – Wernfried Domscheit Apr 08 '19 at 08:09

1 Answers1

0

Looks like you missed some basics, let's explain. When you want to connect to an Oracle database, no matter whether sqlplus or VBA or anything else then actually you would have to do it like this:

sqlplus MyUserName@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.0)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA12)))

However, nobody is willing or able to remember such a long and complex database name. So, you would prefer to use an alias, i.e. a shortcut for (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.0)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA12)))

There are different methods to define and resolve such aliases (see Managing Network Address Information), the most common and easiest way is to use a local tnsnames.ora file.

In your tnsnames.ora file you can add a line like this:

MY_DB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.0)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA12)))

Then you can connect to your database simply with

sqlplus MyUserName@MY_DB

Now the next question is: How does my application find the tnsnames.ora file on my system? The answer is not as trivial as you might assume, see Determining location of relevant tnsnames.ora file

The most secure solution is: Define an environment variable TNS_ADMIN and put as value the directory name where your tnsnames.ora file is located.

Provider "msdaora" uses a different pattern to find the tnsnames.ora file than "Oracle OLE DB" provider does. msdaora finds your file, Oracle OLE DB does not. Settings an environment variables TNS_ADMIN makes it working for both.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks a lot, i am not sure why, but is really the sSource shall change. Not too sure why previously it works, changed to 64-bit the same name not working. Based on your detail explanation, I checked through tnsnames.ora and managed to find correct sSource. Really appreciated :) – user8729759 Apr 08 '19 at 10:16