Link to home
Start Free TrialLog in
Avatar of PeterFrb
PeterFrbFlag for United States of America

asked on

Creating a DSN-less connection to Oracle source in VBA


On my machine at work, I do not yet have an ODBC driver for Oracle, which is on order.  I have the TOAD program, which somehow creates a DSN-less creation to the oracle resource without the use of the ODBC driver.  Until I get that driver, I would like to leverage the technique that TOAD uses for gaining data access without the ODBC driver.  I am very unfamiliar with Oracle protocols, but I do know that TOAD leverages the tnsnames.ora file to resolve its settings.  I've been experimenting with several connection strings, but have thus far failed to create one that works.  

Looking for, but not finding, the right syntax has been the product of research yesterday, and here's a snippet of my various failed attempts making a connection, both with and without an Oracle driver.  When the driver is installed on my machine, then I should be able to implement strSources(0).

    Const iUseSource as integer = 8    

    ReDim strSources(0 To 8)
    strSources(0) = "Driver={Microsoft ODBC for Oracle}"
    strSources(1) = "Provider=msdaora"
    strSources(2) = "Provider=OraOLEDB.Oracle"
    strSources(3) = "driver={oracle in oradb10g_home1}"
    strSources(4) = "Data Source=myOracleDB"
    strSources(5) = "Server=(DESCRIPTION = (ADDRESS = (PROTOCOL = ...)(HOST = ...)(PORT = ...)))"
    strSources(6) = "Server=" & strDB
    strSources(7) = _
        "Data Source =(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = ...)(HOST = ...)(PORT = ...))) " & _
            "(CONNECT_DATA =(SERVER = ...)(SERVICE_NAME = ...)))"
    strSources(8) = "Data Source=" & strDB

    Set cn = New ADODB.Connection

    With cn
        .ConnectionString = _
            strSources(iUseSource) & ";" & _
            "Uid=" & strLoginID & ";" & _
            "Pwd=" & strPassword & ";"
    End With

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

To connect to an Oracle database, you need an Oracle driver.

The one exception is if you use a JDBC Thin connection.

The tnsnames.ora file is just a place to look up information about the database you are connecting to.  Nothing more.

What Oracle client drivers do you currently have installed before you get the ODBC drivers?

I assume "which is on order" means for your internal company's process to have it installed?

Oracle clients are free.  There is no ordering.  You just register (free) and download them.
Avatar of PeterFrb


This is good information, and you are correct.  I have put a request that they be installed on my machine from the company tech support; and, as I don't have admin rights on my machine, an internal resource is responsible for supplying me with this access.  To your points:


I don't know what an Oracle Client driver is.  Could you please clarify?


What must I do to register?  If I can do some legwork on my end, it may save time.


I know that TOAD succeeds in creating a connection to Oracle without the benefit of an ORacle ODBC driver, because no driver exists.  I've included the image below, if that is of use.


How does JDBC work, and can VBA leverage that information?


(for TOAD users) Is there any way to translate the screenshot below into a connection string?  I don't see the equivalent listed.
Sincerely, ~Peter Ferber

User generated image
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good information on all counts, thanks.  You've given me what I need to do further inquiry.