- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 255
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-11-25
              
|
由於工作上的需要, 深切體會ADO的方便. 其實與Database之間的資料存取、透過Excel再處理... 這些事, 尤其對於IT人員, 都有其他方法和工具可以達成, 但相對來說還是麻煩.
以下範例是根據我平時的環境 (DB, ODBC), 如果各位使用有何問題, 歡迎提出討論
'ADO - Query Oracle from Excel via ODBC driver
Sub ADO_example()
Dim con As Object, com As Object, rec As Object
Set con = CreateObject("ADODB.Connection")
Set com = CreateObject("ADODB.Command")
Set rec = CreateObject("ADODB.Recordset")
On Error GoTo ADO_Err
'星號部分依實際設定自行修改
con.Open "DRIVER={Microsoft ODBC for Oracle};UID=*** WD=***;SERVER=*****;"
With com
.ActiveConnection = con
.CommandType = 1 '1=adCmdText 4=adCmdStoredProc
.CommandText = "select emp_no,emp_name from employee where dept_no='001'"
End With
Set rec = com.Execute
'整個複製到工作表
Cells(1, 1).CopyFromRecordset rec
'讀取欄名方式一
For Each f In rec.Fields
Debug.Print f.Name
Next
'讀取欄名方式二 PS. rec(0)為 rec.Fields(0) 之簡化寫法
Debug.Print rec(0).Name & ", " & rec(1).Name
'直接讀取Recordset資料
While Not rec.EOF 'Loop: 適用多筆
Debug.Print rec(0) & ", " & rec(1)
rec.MoveNext
Wend
rec.Close: con.Close
1 Set con = Nothing: Set com = Nothing: Set rec = Nothing
Exit Sub
ADO_Err:
MsgBox Err & vbLf & Err.Description, 16
GoTo 1
End Sub
另以下是Connection和Recordset物件的一些屬性, 可用於測試, 也一併列出提供參考 --
'Test Oracle Connection info after Openning
'Debug.Print "con.CursorLocation =" & con.CursorLocation '2=adUseServer 3=adUseClient
'Debug.Print "con.DefaultDatabase =" & con.DefaultDatabase 'not supported by Oracle
'Debug.Print "con.IsolationLevel =" & con.IsolationLevel
'Debug.Print "con.Mode =" & con.Mode
'Debug.Print "con.Provider =" & con.Provider
'Debug.Print "con.State =" & con.State
'Debug.Print "con.Version =" & con.Version
'Test Recordset info after Executing of Command
'Debug.Print "rec.AbsolutePosition=" & rec.AbsolutePosition '-2=adPosBOF -3=adPosEOF -1=adPosUnknown
'Debug.Print "rec.ActiveCommand =" & rec.ActiveCommand.CommandText
'Debug.Print "rec.ActiveConnection=" & rec.ActiveConnection.ConnectionString
'Debug.Print "rec.CursorLocation =" & rec.CursorLocation '2=adUseServer 3=adUseClient
'Debug.Print "rec.CursorType =" & rec.CursorType '0=adOpenForwardOnly 1=adOpenKeyset 2=adOpenDynamic 3=adOpenStatic
'Debug.Print "rec.DataMember =" & rec.DataMember
'Debug.Print "rec.Fields.Count =" & rec.Fields.Count
'Debug.Print "rec.Index =" & rec.Index
'Debug.Print "rec.MaxRecords =" & rec.MaxRecords
'Debug.Print "rec.PageCount =" & rec.PageCount
'Debug.Print "rec.RecordCount =" & rec.RecordCount
'Debug.Print "rec.State =" & rec.State
'Debug.Print "rec.Status =" & rec.Status |
|
發表於 2010-4-30 23:02
| 
