Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODAC 11.2.0.1.2 + VBA ADODB Connection + Oracle.OraOleDB does not work

820114Dec 1 2010 — edited Jan 28 2011
Hi,

I have installed ODAC 11.2.0.1.2 on client machine.

I am trying to populate data in excel (2002) using VBA code. I used VBA ADODB.Connection & OraclOleDb provider with connection string - Provider=OraOLEDB.Oracle.1;Data Source=<Full TNS string>;User Id=xxx;Password=xxx;

I have verified and found registry enteries for OraOleDB11.dll.

However when i run the code i get the following error - Provider cannot be found. It may not be properly installed.

On further investigation i found the code works fine on machine which have Oracle client (9i/10g) installed. I had thought that ODAC do away with the need to install the full Oracle client as it installs the Instant client and my code should have worked fine.

Can someone please provide some insights on it or any other options.
Note - The code runs without any issues if I use MSDAORA.1 but is very slow as compared to performance of OraOleDB provider. (1 min (OraOleDB) vs 8-9 mins (msdaora) to retireve 15,000 records with 25 colums)

Thanks
Kamlesh

Comments

gdarling - oracle
Hi,

The most common cause of "provider cannot be found.." these days is having 32 bit oracle client installed when the app is 64 bit, or vice versa. It doesnt sound like that's the case here though.
Do you have multiple Oracle homes installed? Make sure the home oraoledb is getting loaded out of is the first in your PATH environment variable.
Short of that, there's no reason I can think of you shouldnt be able to install just 112102 odac and use oledb with 32 bit app. You may want to open a SR with support so we can troubleshoot your particular installation.
You may also want to try using Process Monitor from sysinternals.com to monitor dlls being loaded to see if you can figure out where it's looking, for what, and why it failed.

Hope it helps, but realize it probably doesnt.

Greg
820114
Thanks Greg.

"Do you have multiple Oracle homes installed? Make sure the home oraoledb is getting loaded out of is the first in your PATH environment variable."
[KB]: I am trying to run on the machine where there is no oracle client machine installed. Is my understanding correct that we can use Oracle OleDB driver from VBA by installing the ODAC only on client machine?

You may want to open a SR with support so we can troubleshoot your particular installation.
[KB]: Can you please point me to some instruction page to raise a SR. This is first time that i will be raising a SR.

Thanks
Kamlesh
835057
Hi Greg,

Wondering if you could help.

Facing the "Provider cannot be found" message on a Win2008 Svr R2 64bit when attempting to launch the third-party software. DB patched to 10.2.0.5.0

The sw installed on an XP gets launched fine.

Fact is, I ignorantly took the client installer for the XP and installed it on the server. I also made the mistake of not looking at the install log in its entirety before proceeding - I saw InstantClient was selected and did the same, later realising the DBA who installed on the XP later uninstalled InstantClient and reinstalled Administrator-type.

So I did the same. But I was later told I should have installed the 64bit version of client installer.

So it was reinstalled by DBA, but this error is bugging me. And this error is also happening on the DB server itself, after installing the software there.

Well, hope you can provide some insights into what could be wrong and how to fix it, thanx


Rgds,
SM
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 25 2011
Added on Dec 1 2010
#oracle-provider-for-ole-db, #windows-and-net
3 comments
4,367 views