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

PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
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

Toad Connection Properties
slightwv (䄆 Netminder) Commented:
1:  There is really no client driver per say.

The Oracle Client is just a general term for several pieces of software that will allow you to connect to an Oracle database in several ways.

The Microsoft drivers that connect to Oracle, actually access Oracle DLLs to connect.

That is whay I say: With the exception of the JDBC Thin connection, to connect to an Oracle database, you Need Oracle software.

2:  To download Oracle Software, you just need to register with them.

For example, if you wanted to download the Instant Client with sqlplus (by the way, nothing to install so you can access an Oracle database with it, just not ODBC).

Go to:

Pick the right version for you.  You will be asked to create an account then you can download the files.

3:  Toad likely uses the JDBC connection.  It sort of has to if you don't have any Oracle software installed.

4: I don't think VBA can use the JDBC connection but I'm not a VBA Expert.  You might be able to Google around and find a way to use VBA without an Oracle client or ask a question here in the VBA Zone.

5: Without an Oracle driver, or JDBC, you cannot connect to an Oracle database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Good information on all counts, thanks.  You've given me what I need to do further inquiry.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.