• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2222
  • Last Modified:

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
0
creditpointe
Asked:
creditpointe
  • 3
  • 2
1 Solution
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now