How to connect Oracle Database using 64-bit Excel in Windows 10

0
votes

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
1
excelvbaoracle
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
@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
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 – user8729759
I doubt that the sample tnsadmin.ora file will work. – Wernfried Domscheit
"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

1 Answers

0
votes

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.