asked on

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

dbmullen

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.

creditpointe

ASKER

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.

creditpointe

ASKER

The vba code is running on Windows Server 64bit. Can this cause the issue?

dbmullen

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

ASKER CERTIFIED SOLUTION

creditpointe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial