Excel VBA+ADO+SQL入門教程022:Execute方法
1.
諸君好,我們今天聊Connection對象的Execute方法;該方法可以向資料庫提交查詢,比如SQL語言,是我們系列教程中經常使用到的——其語法如下:
Connection.Execute CommandText,RecordsAffected, Options
第1個參數CommandText為字元串類型,是必須的,用來指定提交的查詢,比如SQL語句。
第2個參數RecordsAffected是可選的輸出參數,用來指定查詢影響的行數。
第3個參數Options也是可選參數,用於指定命令類型和可能的CommandTypeEnum值的詳細信息。
第2~3參數,作為新手我們基本用不到,所以就當沒看到。
2.
Execute方法有兩種使用形式。一種是Cnn.Execute SQL;另一種是Cnn.Execute(SQL)。
沒錯。兩者看似一樣,但以魯迅他老人家兩棵棗樹般寂寞的情懷發誓,其實並不一樣後者比前者多了一對括弧……
當Execute執行的SQL語句是不需要返回記錄集時,例如對資料庫數據的刪除、新增、更新等,Execute方法的參數,既可以加括弧,也可以不加括弧,比如:
Cnn.Execute "delete from 成績表 where 姓名="馬可波羅""
也可以寫成:
Cnn.Execute ("delete from 成績表 where 姓名="馬可波羅"")
而當Execute指定的SQL語句是需要返回記錄集,也就是SELECT查詢語句時,由於VB語法規定帶返回值的調用其參數必須加括弧,因此就需要對SQL語句加上一對括弧了。
……
舉個例子:
Sub DoExecute2()
Dim cnn As Object, rst As Object
Dim i As Long, Sql As String
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
"創建到代碼所在工作簿的連接,Excel版本非03版
Sql = "select * from [成績表$]""Sql語句
Set rst = cnn.Execute(Sql)"Execute執行Sql語句
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
"遍歷獲取記錄集中的標題
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("a2").CopyFromRecordset rst
"獲取記錄集中的記錄
cnn.Close"關閉連接
Set cnn = Nothing"釋放內存
End Sub
上面的代碼Set rst = cnn.Execute(Sql),得到一個新的、只讀屬性的Recordset記錄集,該記錄集由標題和記錄行兩部分構成;我們通過遍歷循環的方式,將該記錄集的標題名(Fields.Name)依次放置到表格的第1行;並使用單元格的CopyFromRecordset方法,將查詢記錄放置到右上角為A2單元格的區域內。
當然,如果我們並不需要獲取標題之類的信息,只是需要獲取查詢記錄,也可以不使用rst對象,代碼會相當簡潔。
Sub DoExecute3()
Dim cnn As Object
Dim i As Long, Sql As String
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
"創建到代碼所在工作簿的連接,Excel版本非03版
Sql = "select * from [成績表$]""Sql語句
Cells.ClearContents
Range("a2").CopyFromRecordset cnn.Execute(Sql)
"Execute執行Sql語句,CopyFromRecodset獲取記錄集中的記錄
cnn.Close
Set cnn = Nothing
End Sub
點擊【閱讀原文】可以下載示例文件,比較兩段代碼運行結果的不同。
小貼士:
使用Connection的Execute的方法執行SQL語句,不管是否使用到該方法返回的Recordset對象,均不需要使用代碼關閉相關Recordset對象(rst.Close),例如上述示例。
3.
儘管Execute方法可以向資料庫提交SQL語句,且語句十分簡單,但它也有很多的局限性……周杰倫:聽媽媽的話,媽媽說便宜沒好貨……
當Execute執行的是SQL的改、增、刪語句時,返回的Recordset對象是關閉的;而當執行SQL的SELECT查詢語句時,總是返回一個新的Recordset對象,且該對象屬性只讀、游標僅向前……我們後面會講到如何在Recordset記錄集內,操縱數據的增加、刪除等,如果Recordset屬性只讀,則此類許多操作也就無意義了。
另外,Execute方法返回的Recordset對象的方法和屬性也有很多的局限性;有些屬性方法用不了,還有些屬性方法用了會出錯。比如,我們無法使用rst.RecordCount來判斷查詢的結果集是否為空、條目多少,它的rst.RecordCount結果始終返回-1;倘若需要判斷查詢結果是否含有記錄,通常使用以下語句:
IfNot (rst.BOF And rst.EOF)Then
MsgBox "查有結果"
Else
MsgBox "查無結果"
End If
……
因此,Execute方法最適合執行操作查詢,也就是增、改、刪的SQL語句,這些語句我們通常並不需要獲取及操作Recordset對象;而如果我們是執行查詢語句,並需要對Recordset記錄集靈活而豐富的使用,通常建議使用Recordset的Open方法……我們應該會在第25章講到該方法。
……
沒了
致安
…………Hi,love you forever and ever…………