asked on

Establish connection to Oracle using {Microsoft ODBC for Oracle} driver

Hi,

I want to query Oracle from Excel VBA. I am not permitted to install the oracle driver. I do have the following Microsoft drivers - see below

I've gone through the following questions with no result:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_DB/Q_20706829.html
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_23717335.html

ADO

I keep running into this error when I connect using ADO

---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.
---------------------------
OK
---------------------------

Code below using the thin client - errors on 'con.Open...'

    Dim con As Object
    Dim rs As Object
    Dim query As String
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    strCon = "Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(jdbc:oracle:thin" & _
    "(ADDRESS=(PROTOCOL=TCP)" & _
    "(HOST=99.99.999.99)(PORT=1521))" & _
    "(CONNECT_DATA=(SID=TEST))); uid=apps; pwd=xxx;"
    
    con.Open strCon

Open in new window

DAO

This is the error I get with DAO - why does it look like an ado error to me(?).

Run-time error '429': ActiveX component can't create object

Here is one sulution I found ( link) - I actually have access to registry, but havn't tried it yet.

The 'Microsoft DAO 3.6 Object Library' reference is active. My code below errors at 'Set Session...'

Private Sub Form_Load()
Dim strSQL As String
Dim DatabaseName As String
DatabaseName = "TEST"

Set Database = Nothing
    Set Session = CreateObject("OracleInProcServer.XOraSession")
    Set Database = Session.OpenDatabase(DatabaseName, "apps/xxx", ORADB_NOWAIT)
    Set dbTable = Nothing
    strSQL = "SELECT * FROM FRYER_CLASSES"
    Set dbTable = Database.CreateDynaset(strSQL, ORADYN_DEFAULT)
End Sub

Open in new window

SQL Developer

This may be irrelevant, but I can get SQL Developer to connect fine:

What hope??

Thanks,
John

David VanZandt

Hi John, an excellently prepared question: kudos. In your con.Open you display placeholder values. You have, I trust, replaced them with the actual host, uid, and pwd in your actual code? The information in the ODBC should be tested by completing a SQL*Plus client connection with it.

CSI-Windows_com

Frequently this is a problem with bitness mis-match.

The application bitness must match the bitness of ODBCAD32.exe (or other utility) which must match the bitness of the Oracle client installed.

Unfortunately in ODBCAD32.EXE the "User DSN" tab shows user DSNs of BOTH bitnesses, but will only allow you to change ones that match the bitness of odbcad32.exe.

In most cases you are trying to successfully setup 32-bit application support.

Be sure to use c:\windows\syswow64\odbcad32.exe to configure 32-bit data sources.

In this case bitness of office must match the bitness of the utility which must match the bitness of the installed drivers.

If you have 64-bit office installed, that is generally a big mistake. The reason? The vast majority of Office plug-ins are only available as 32-bit plug-ins and you don't know what ones you might need to buy this coming year.

slightwv (䄆 Netminder)

>> I am not permitted to install the oracle driver.

Then you are SOL.

Pay attention to the following from the text you posted:
Oracle client and networking components were not found. These components are supplied by Oracle Corporation

The Microsoft drivers sit on top of the Oracle drivers. To connect to an Oracle database, you need the Oracle drivers. The only exception is a JDBC Thin connection.

>>This may be irrelevant, but I can get SQL Developer to connect fine:

It is. SQL Deverloper has the client built in.

_Benaiah

ASKER

Hi dvz,

Thanks and Yes, I replace HOST and Password with the correct values (these connect happily using SQL developer)

_Benaiah

ASKER

Hi CSI-Windows_com,

very interested to know more about bitness.

I think (correct me) that its not going to help without an Oracle Driver (screenshot from c:\windows\syswow64\odbcad32.exe configuration wizard)

I'm unaware what kind of server I'm connecting to - do you mean that my problem could be a 64bit server?

Thanks,

_Benaiah

ASKER

Hi slightwv,

I take it SOL is urban slang

Very informative info about MS driver

So, can I use the JDBC Thin connection in Excel? This didn't help:
http://stackoverflow.com/questions/13198438/oracle-data-access-with-vba

SQL Deverloper has the client built in

Or alternatively, can I piggyback on SQL Developer??

Thanks,

SOLUTION

CSI-Windows_com

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer