Excel VBA connect to remote Oracle DB with InstantClient

Question

I am trying to use Excel (mainly 2003, for more user compatibility) to connect to a remote Oracle DB. I would like to run a .sql script and return the dataset to a worksheet.
I am on a Windows 7 64bit machine. I do not know the specs of the Oracle DB server.
I would like to keep this as lightweight as possible (no extra file installations on client machines, use shared network locations for required files as much as possible)

---

---

So Far:

I downloaded and "installed" the InstantClient from Oracle (versions 12.1 and 11.2 for both 32bit and 64bit) onto a remote network location.
I tried connecting to the Oracle DB using SQL Plus and it worked fine (I tried several of the installed InstantClient versions to see if there would be any compatibility issues).
As a test: using SQL Plus and the Shell function in VBA, I was able to successfully spool the data into a separate excel file.

---

I tried several different connection string formats using various drivers/providers:

• Driver={Oracle in instantclient_11_2}
• Driver={Microsoft ODBC for Oracle}
• Provider=MSDAORA
• Provider=MSDAORA.1
• Provider=OraOLEDB.Oracle
  

---

Errors I Received:

"Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation..."

"Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

"Run-time error '3706':
Provider cannot be found. It may not be properly installed"

And a few other similar errors.

---

I have added the network location containing the instantclient files to my PATH environmental variable. Not sure what other environmental variables I require or even if my current one is correct.

Do I need:
TNS_ADMIN? ORACLE_HOME?

---

---

Question:

• How do I connect to the remote Oracle DB with VBA, using the instantclient files that are located in a network (share) location?
  

  • What is the correct full connection string? (I used the EZConnect format with SQLPlus; are the actual connection details the same? and for clarification, could someone post an example of how the EZConnect format converts to the other format(s)?)
    

    My EZConnect Format: username/[email protected]/mydb
    

  • What "provider" or "driver" should I use for this purpose and are there any significant differences?
    

  • What environmental variables do I require to make this work?

I've found a lot of questions that are similar or related, but none that directly answered my question or helped me enough to completely solve it.

Answer 1

Ended up editing/using this function (which does not(?) use driver/provider: InstantClient but still uses the files):

Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)
  Dim strConOracle, oConOracle, oRsOracle
  Dim StrResult As String
  StrResult = ""
  strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=" & strHost & ")(PORT=1521))" & _
         "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
  Set oConOracle = CreateObject("ADODB.Connection")
  Set oRsOracle = CreateObject("ADODB.Recordset")
  oConOracle.Open strConOracle
  Set oRsOracle = oConOracle.Execute(strSQL)
  MsgBox (oRsOracle.Fields(0).Value)
  varResult = oRsOracle.GetRows
  Do While Not oRsOracle.EOF
      If StrResult <> "" Then
        StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value
      Else
        StrResult = oRsOracle.Fields(0).Value
      End If
    oRsOracle.MoveNext
  Loop
  oConOracle.Close
  Set oRsOracle = Nothing
  Set oConOracle = Nothing
  ORAQUERY = StrResult
End Function

Correct full Connection String:

Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=strHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=strDatabase))); uid=strUser; pwd=strPassword;

Provider or Driver:
{Microsoft ODBC for Oracle}

Needed to set PATH environmental variable to point to instantclient.
Didn't use any of the other environmental variables e.g. ORACLE_HOME, TNS_ADMIN, etc.