5

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

dim cn As New ADODB.Connection
cn.Open "Provider=OraOLEDB.Oracle;Data Source=source;User Id=userid;Password=pwd;"

I'm getting this error:

Run-time error '3706':
Provider cannot be found. It may not be properly installed.

Relevant references:

Microsoft ActiveX Data Objects 6.1 Library
OraOLEDB 1.0 Type Library
  • Excel is MS Office Professional Plus 2010 64-bit
  • Windows is 7 Enterprise, service pack 1, 64 bit
  • Oracle client is 11.2.0 32 bit (I think)
  • Oracle server is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Now... I have scoured the net and cannot figure out what the issue is, but my best guess is there's some 32 bit vs 64 bit conflict with drivers I'm using. I'm trying to get a sense of what path I should be taking before I involve my company's IT department to try installing stuff for me. Does the Oracle client have to be 64 bit if MS Office is? Or do I just need the 64 bit Oracle Data Access Components? I've tried a bunch of different connection strings with no success, including using MSDAORA instead of OraOLEDB.Oracle...

Paul Exter
  • 85
  • 1
  • 2
  • 7
  • 2
    If you're using 64-bit Excel (and you probably should use the 32-bit version of Office unless you *really* need 64-bit) then you also need the 64-bit Oracle client and 64-bit drivers. – Tim Williams Mar 03 '15 at 18:41
  • Thank you, that's the sense I got but I wanted to confirm before I proceeded. They kind of upgraded us to 64 bit Office over the last few months without a good reason. Maybe they can move me back - although, if I want other people to be able to use the macro I'm writing, I guess they would have to be rolled back too... – Paul Exter Mar 03 '15 at 19:09
  • 1
    Guidelines from MS on which version to use: https://support.office.com/en-sg/article/Choose-the-32-bit-or-64-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261?ui=en-US&rs=en-SG&ad=SG Using 64-bit Office will give you a lot more compatibility issues, and if you don't really need it probably best to stay on 32-bit. – Tim Williams Mar 03 '15 at 19:13

3 Answers3

3

Yes, when you use 64-bit Excel you must install also 64-bit Oracle Client and 64-bit OLE DB Provider.

OLE DB Provider from Microsoft (i.e. MSDAORA) does not exist for 64-bit. It exists only for 32-bit and has been deprecated for very long time already.

I don't know whether you need your 32-bit Oracle Client for something else, so maybe you cannot simply remove it from your PC. Here is an instruction how to install both 32-bit and 64-bit Oracle Client on one machine. Install Oracle Client x86 and x64

Community
  • 1
  • 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks, you reconfirmed what Tim commented above. I talked to my IT department about the best path forward, and they suggested I just remote into a terminal where Excel and Oracle are both 32 bit, which I wish I thought of myself. I did so and my macro worked fine from there. – Paul Exter Mar 03 '15 at 21:58
1

I too had the same issue and the solution is to set the oracle drivers for the excel session to 32 Bit drivers and initiate the excel.

I use the following Bat file to open the excel and it connects to oracle without any issues:

# 
Set Temp=C:\Temp
Set TMP=C:\Temp
Set Oracle_Home= #YourDriveLetterhere - >M:\ORA12C\product\12.1.0\client_1
Set Path=C:\Windows;C:\Windows\System32;M:\ORA12C\product\12.1.0\client_1\bin;M:\ORA12C\product\12.1.0\client_1\nls\mesg;M:\ORA12C\product\12.1.0\client_1\network\admin;

START "" "C:\MacroFileNameWhichConnectsToOracle.xlsm"
#
NeatCoder
  • 43
  • 1
  • 5
-2

The oracle server matters not.

Try opening a dos window and type c:\ tnsping yourservername

look towards installation issues.

Larry
  • 16