Avatar of PeterFrb
PeterFrbFlag for United States of America asked on

Creating a DSN-less connection to Oracle source in VBA

Hello:

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

Oracle DatabaseVisual Basic Classic

Avatar of undefined
Last Comment
PeterFrb

8/22/2022 - Mon
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.
ASKER
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:

1.

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

2.

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

3.

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.

4.

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

5.

(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

Toad Connection Properties
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
PeterFrb

Good information on all counts, thanks.  You've given me what I need to do further inquiry.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes