If rs.RecordCount > 0 Then For j = 0 To rs.Fields.Count - 1 Cells(1, j + 2) = rs.Fields(j).Name Next j
Range("B2").CopyFromRecordset rs End If
rs.Close Set rs = Nothing con.Close Set con = Nothing
End Sub
'连接Oracle Public objOraSession Public objOraDBPublic strDbConn As String Public strDbUser As String Public strDbPwd As String'connectDB Public Function connectDB() As Boolean On Error Resume Next
Set objOraSession = CreateObject("oracleinprocserver.xorasession") Set objOraDB = objOraSession.OpenDatabase(strDbConn, strDbUser & "/" & strDbPwd, 0) If Err.Number > 0 Then MsgBox Err.Description Set objOraDB = Nothing Set objOraSession = Nothing
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strQ As String
Dim j As Integer
con.CursorLocation = adUseClient
con.Open "PROVIDER=MSDASQL;Driver={SQL Server};Server=xxx.xxx.xxx.xxx;uid=sa;pwd=my_password;database=my_dbname;"
strQ = strQ & " SELECT * from my_table where my_id = '" & Cells(1, 1) & "' " & vbNewLine
rs.Open strQ, con, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then
For j = 0 To rs.Fields.Count - 1
Cells(1, j + 2) = rs.Fields(j).Name
Next j
Range("B2").CopyFromRecordset rs
End If
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub
Public objOraSession
Public objOraDBPublic strDbConn As String
Public strDbUser As String
Public strDbPwd As String'connectDB
Public Function connectDB() As Boolean
On Error Resume Next
Set objOraSession = CreateObject("oracleinprocserver.xorasession")
Set objOraDB = objOraSession.OpenDatabase(strDbConn, strDbUser & "/" & strDbPwd, 0) If Err.Number > 0 Then
MsgBox Err.Description
Set objOraDB = Nothing
Set objOraSession = Nothing
connectDB = False
Exit Function
End If
connectDB = True
End Function