Oracle Client and networking components were not found - Excel VBA

Question

I'm trying to connect to an Oracle DB through Excel. I have installed the Instant Client from Oracle on my machine, but am getting this message when I run this code:

Sub testing()


    Dim myConn As Connection: Set myConn = New Connection
    Dim mySet As Recordset: Set mySet = New Recordset
    Dim CONNSTRING As String
    CONNSTRING = "Driver={Microsoft ODBC for Oracle}; " & _
                "CONNECTSTRING=(DESCRIPTION=" & _
                "(ADDRESS=(PROTOCOL=TCP)" & _
                "(HOST=xxxxxx.xxx.xxxxxxxxxxx.com)(PORT=1524))" & _
                "(CONNECT_DATA=(SERVICE_NAME=dev))); uid=xxxxxxx; pwd=xxxxxxxxxxx;"

    myConn.Open CONNSTRING
    mySet.Open "SELECT * FROM apps.ap_invoice_lines_interface", myConn, adOpenStatic, adLockBatchOptimistic, adCmdTable

    Sheet1.Range("A1").CopyFromRecordset mySet

    mySet.Close
    myConn.Close

End Sub

The message I am getting is

The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation. You will be unable to use this driver until these components have been installed

Stupid question, but did you reboot your machine after installing the required Oracle client? Asking because I had this issue and that's how I solved it. After you solve the issue though, you will probably have an issue with the `conn` after the `SELECT`. It should be `myConn` in there — Victor Moraes, Sep 29 '16 at 20:12
I did not reboot yet, and thanks much for that catch! I'll change it to myConn — jDave1984, Sep 29 '16 at 20:14
In that case, it was a valid question, hehe. Let us know the outcome — Victor Moraes, Sep 29 '16 at 20:16
Is your excel 32-bit or 64-bit? Is your installed Oracle client 32-bit or 64-bit? They have to match, and I've seen this very error for this very reason. — EdStevens, Sep 29 '16 at 20:29
I have 32 Bit Excel, but I admittedly did install the 64 bit because I have a 64 bit OS. I'll get the 32 bit as well and let you know — jDave1984, Sep 29 '16 at 20:34
@EdStevens -- I really encourage you to post this as a formal answer. I think this is a very common issue as most Office instances are 32-bit. We have the same issue frequently with ODBC setup. This could potentially help a lot of future lurkers if it were more obvious that this is the solution. — Hambone, Sep 30 '16 at 03:53

score 5 · Accepted Answer · answered Sep 30 '16 at 12:26

5

I see this error frequently when our desktop support team installs the wrong Oracle client. If your application (Excel, in this case) is 32-bit, then you also need to be using 32-bit ODBC and 32-bit Oracle client. It doesn't matter that your OS or hardware is 64-bit. What matters is the application. And it seems that most MS Office installations are 32-bit, even when installed on 64-bit Windows.

answered Sep 30 '16 at 12:26

EdStevens

3,708
2
10
18

1

Thanks again for your help! – jDave1984 Sep 30 '16 at 14:58
Great answer. I have both 32 bit and 64 bit Oracle Clients installed but I still get this error. Should get the 64-bit version uninstalled? – Satej Koli Jan 25 '20 at 05:39

score 0 · Answer 2 · answered Sep 05 '18 at 01:23

0

In my case, the Console project in Visual Studio had Prefer 32 bit check-marked in its Properties.

Press Alt + Enter and open the Build section.

I unchecked it and then the app worked perfectly.

By the way: I had installed Oracle client 64 bit.

answered Sep 05 '18 at 01:23

Leniel Maccaferri

100,159
46
371
480

Oracle Client and networking components were not found - Excel VBA

2 Answers2

Linked