Error while trying to connect to Oracle DB using VBA

Posted on 2008-11-10
Last Modified: 2013-11-27
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
Question by:creditpointe
    LVL 10

    Expert Comment

    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.

    Author Comment

    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.

    Author Comment

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

    Expert Comment

    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

    Accepted Solution

    The problem was resolved by installing 32 bit DB rather than the 64 bit one. Some other options are under trial as well.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now