Excel VBA connects to a remote Oracle database using InstantClient

Question

I am trying to use Excel (mainly 2003, for greater user compatibility) to connect to a remote Oracle database. I would like to run a .sql script and return the dataset to a worksheet.
I am on a Windows 7 64-bit machine. I do not know the specifications of the Oracle DB server.
I would like it to be as easy as possible (without additional file installations on client machines, use as much as possible shared network locations for the required files)

---

---

Still:

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

---

I tried several different string 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 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 more similar errors.

---

, instantclient, PATH. , .

:
TNS_ADMIN? ORACLE_HOME?

---

---

:

• Oracle VBA, instantclient, ( )?
  

  • ? ( EZConnect SQLPlus, ? , - , EZConnect ()?)
    

    My EZConnect Format: username/[e-mail]/mydb
    

  • "" "" ?
    

  • , ?

, , .

Answer 1

/ ( (?) /: InstantClient, ):

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

:

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

:
{Microsoft ODBC Oracle}

PATH, instantclient.
, . ORACLE_HOME, TNS_ADMIN ..