Error while trying to connect to Oracle DB using VBA

Trying to connect to Oracle DB using VBA. The machine has both oracle 10g and 11g.

When DSN is created for 11g, test connection is successful but when used in the following code

Set cn = New ADODB.Connection
    cn.Open "DSN=" &DSNname& ";UID=" &uid& ";PWD=" &pwd

throws error

Specified driver could not be loaded due to system error 193(Oracle in OraDB11g_home1)

if connection is setup without DSN as below

str1 = "Provider=OraOLEDB.Oracle;Data Source=;User ID=;Password=;"
    cn.ConnectionString = str1
    cn.Open

then it gives following error

OraOLEDBplus10.dll: The specified module could not be found.
Provider cannot be found. It may not be properly installed
creditpointeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbmullenCommented:
at the dos prompt, type the following
tnsping yourdatabasenamehere

if that works
then
sqlplus username/password@yourdatabasenamehere

that tells you two things
1)  the database is up and you can connect to it.
0
creditpointeAuthor Commented:
We had tried both these options and it is working fine. This problem is with only one machine. The code works fine on others.

When I use DSN it gives me a driver error for 11g but incase of other option without DSN, it gives error for 10g.
0
creditpointeAuthor Commented:
The vba code is running on Windows Server 64bit. Can this cause the issue?
0
dbmullenCommented:
you connected to the windows server in question and tried tnsping and sqlplus?
if no, start there...
if yes..  does that server have both 10g and 11g clients installed?
          which one is the "default"?
          you can tell this when tnsping is done
0
creditpointeAuthor Commented:
The problem was resolved by installing 32 bit DB rather than the 64 bit one. Some other options are under trial as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.