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

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.

PeterFrb

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

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

Start Free Trial

PeterFrb

ASKER

Good information on all counts, thanks. You've given me what I need to do further inquiry.