0

I am an application developer that works on a windows forms app. Ive been developing on a machine that was windows 10 64 bit with oracle client 11g and MS Office 32 bit. My company is doing a lot of upgrades. I now have Office 64 bit. I no longer could connect with access to oracle using the 11g client, but my visual studio development worked just fine and I could connect to an oracle server. Now they are upgrading to Oracle client 12.1. There is a 64 bit an 32 bit version. If I install the 64 bit, my connection to oracle through ms access works fine, but now trying to connect in visual studio fails. So I have 32 bit oracle client installed as well. But it still doesnt work. I am not sure what to do. How can I get my dataset using my oracle connection to work again?

I can add the oracle server and see the tables in server explorer, but when i try to connect to oracle in my program, i get ORA-12154: TNS:could not resolve the connect identifier specified. My TNSNAMES.ora file is set up. How does it know which oracle to use 32 or 64 bit. I am use Oracle.ManagedDataAccess

Imports Oracle.ManagedDataAccess
    Dim conn As New OracleConnection
    Dim oradstr As String = EMS.My.Settings.ORAD_Conn
    Dim orapstr As String = EMS.My.Settings.ORAP_Conn

    constr = EMS.My.Settings.ORAD_Conn
    constr = constr.Replace("{username}", Trim$(UsernameTextBox.Text))
    constr = constr.Replace("{pwd}", PasswordTextBox.Text)
    My.Settings.RunTimeConnectionString_ORAD = constr

    conn = New OracleConnection
    conn.ConnectionString = My.Settings.ORAD_Conn
    conn.Open()
    conn.Dispose()
    conn.Close()
dk96m
  • 301
  • 3
  • 18

1 Answers1

0

You mix several issues. A statement like "still doesn't work" is not very helpful without any error message.

If you like to use 32-bit and 64-bit Oracle client then follow this instruction: BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

With such installation it works in either ways.

In your code you use the ODP.NET Managed Driver. Here it does not matter if you use 32-bit or 64-bit, so the error is a different one than above.

Problem is different drivers use different path to allocate the tnsnames.ora file (see Determining location of relevant tnsnames.ora file).

Most important difference between ODP.NET Managed Driver and many other drivers is: ODP.NET Managed Driver does not read your Registry whereas others read key HKLM\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN, resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

Define location of tnsnames.ora either by Environment Variable TNS_ADMIN or configure it in your .NET config file. I think the simplest way is the OraProvCfg.exe tool for this: 

set Oracle_x64=c:\oracle\product\12.1\Client_x64\odp.net
set OraProvCfg_x64=%Oracle_x64%\bin\4\OraProvCfg.exe

set Oracle_x86=c:\oracle\product\12.1\Client_x86\odp.net
set OraProvCfg_x86=%Oracle_x86%\bin\4\OraProvCfg.exe

set TNS_ADMIN=C:\oracle\network\admin

"%OraProvCfg_x64%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x64%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%
"%OraProvCfg_x86%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x86%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I am using ODP.NET Managed Driver, so shouldnt it be working. In environment variables, I have both the 64 and 32 bit paths in the path variable. A user of my application might still using 11g, some might be using 12.1 32 or 64 bit, so how do i compensate for that. I am so confused. I am not good with oracle stuff – dk96m Jan 20 '19 at 18:40
  • Ive added tns_admin to the environment variables. Still getting tns couldnt resolve error – dk96m Jan 20 '19 at 19:09
  • in the oraprovcfg you have set tns_admin set to network/admin, but which one, the one in the 64bit folder or the 32bit folder – dk96m Jan 20 '19 at 19:25
  • For `TNS_ADMIN` you should use one common location for 32 and 64 bit client. File `Oracle.ManagedDataAccess.dll` is exactly the same file, no matter if you take the 32 or 64 bit installation. You have.NET config files for 32-bit and 64-bit thus you may run different `OraProvCfg.exe` in order to configure according one. – Wernfried Domscheit Jan 20 '19 at 21:24
  • Having both path for 32 and 64 bit does not help - first comes, first serves. If folder of 32-bit comes first then a 64-bit application will fail (and vice versa) - unless you use the special setup from linked instruction. Otherwise you would have to modify your `%PATH%` every time before you start the application. – Wernfried Domscheit Jan 20 '19 at 21:27
  • Having more than one Oracle Client (i.e. one each for 32-bit and 64-bit) does not make any sense. Remove the 11g client (see https://stackoverflow.com/questions/8450726/how-to-uninstall-completely-remove-oracle-11g-client) and install just one, most likely 12.1 or 12.2. If you use the OLE DB driver in MS Access then it is even not possible to have more than one version (per architecture) installed due to [COM](https://en.wikipedia.org/wiki/Component_Object_Model) limitation – Wernfried Domscheit Jan 20 '19 at 21:30
  • So the application I am developing is 32 bit. I am using the manageddataaccess driver for it. If I have just 12.1 32bit installed, everything works fine, except MS access, because my MS Office is 64bit. So how do I get access to work then. It can't use the 32bit. I am so confused. Thank you for your help thus far, it is greatly appreciated. – dk96m Jan 21 '19 at 13:28
  • Read my answer - "If you like to use 32-bit and 64-bit Oracle client then follow this instruction: https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100" – Wernfried Domscheit Jan 21 '19 at 14:21