I have read countless postings on connecting to Oracle and I am still stumped. One problem may be that I am running a 64 bit machine with 32 bit Oracle installed trying to make everything work. When I look in the BIN folder, i see a bunch of DLL's for Oracle 11. I don't see OraOLEDB.dll though. Just OraOLEDB11us.dll. I try to register that one and my system does not like it. 32 bit?
My Connection String looks like this:
Set oCon = New ADODB.Connection
sPW = InputBox("Please enter the password.", "Password Req'd")
Set Connection = CreateObject("ADODB.Connection")
.Provider = "OraOLEDB.Oracle"
.Properties("Data Source") = "SchemaName" 'From TNSNames file
.Properties("User ID") = "username"
.Properties("Password") = sPW '"password"
The checked items in my Excel references are:
VB for Apps
Microsoft Excel 14.0 Object Lib
MS Office 14.0 Object Lib
MS ActiveX Data objects 2.8 Lib
MS ADO Ext 2.8 for DDL and Security
MS Data Access Components Installed Version
MS OLE DB Service Component 1.0 Type Lib
MS OLE DB Simple Provider 1.5 Lib
and the error I get is still:
Runtime Error 3706. Provider cannot be found, when it hits .Properties("Data Source") = "SchemaName".
Any suggestions?? This same code works on other machines, but I don't know why.
The program can't find the provider. Either it's not on your system or it's a 32-bit / 64-bitt issue.
- Try compile and run your program in the other mode 32 -> 64 and vice versa.
The file should be named OraOLEDB11.dll I think. Read more here and for download of the provider,
Okay... I double checked the fact that my OraOLEDB11.dll is registered. I had to register it from my SysWOW32 folder. I am pretty sure that I installed a 32 bit version of Oracle because we were having these problems. Problem is that I am trying to connect from Excel (VBA) directly to Oracle. So, the code is not actually compiled by me and I cannot find an option to compile VBA as 32 or 64 bit. Unless I am missing something.
It is still failing on the same point. I think it is 32 bit vs 64 bit somehow. I will keep testing.
did you ever find a solution to this problem? my searching has lent me here.
the current code works on office 2010 computers, but not on my offcie 2013 (32 bit) computer.