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

then it gives following error

OraOLEDBplus10.dll: The specified module could not be found.
Provider cannot be found. It may not be properly installed
Who is Participating?
creditpointeConnect With a Mentor Author Commented:
The problem was resolved by installing 32 bit DB rather than the 64 bit one. Some other options are under trial as well.
at the dos prompt, type the following
tnsping yourdatabasenamehere

if that works
sqlplus username/password@yourdatabasenamehere

that tells you two things
1)  the database is up and you can connect to it.
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.
creditpointeAuthor Commented:
The vba code is running on Windows Server 64bit. Can this cause the issue?
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.