Excel VBA使用InstantClient连接到远程Oracle DB

Excel VBA使用InstantClient连接到远程Oracle DB

最终编辑/使用了此功能（不使用驱动程序/提供程序：

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 = StrResultEnd 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:

需要设置PATH环境变量以指向InstantClient。
没有使用任何其他环境变量，例如ORACLE_HOME，
TNS_ADMIN等。