|
【例1】使用Connection對(duì)象,，從數(shù)據(jù)庫test.accdb的students表查詢所有數(shù)據(jù)并存放到Sheet1表中,。 Sub test() Dim cnn, rst Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") Dim conStr$, sqlStr$ conStr = "provider=Microsoft.ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb;" cnn.Open conStr sqlStr = "select * from students" Worksheets("Sheet1").[A2].CopyFromRecordset cnn.Execute(sqlStr) MsgBox "操作完成" cnn.Close End Sub 操作結(jié)果如下： 【例2】使用RecordSet對(duì)象，從數(shù)據(jù)庫test.accdb的students表查詢所有數(shù)據(jù)并存放到Sheet1表中,。
Sub test() Dim cnn, rst Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") Dim conStr$, sqlStr$
conStr = "provider=Microsoft.ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb;" cnn.Open conStr
sqlStr = "select * from students" rst.Open sqlStr, cnn
Worksheets("Sheet1").[A2].CopyFromRecordset rst
rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub 注：數(shù)據(jù)庫及表均跟例1相同,。 將讀取的數(shù)據(jù)存儲(chǔ)到數(shù)組數(shù)據(jù)庫記錄如下： 讀取結(jié)果如下：
Sub test() Dim cnn, rst Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") Dim conStr$, sqlStr$ Dim arr(), title()
conStr = "provider=Microsoft.ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb;" cnn.Open conStr
sqlStr = "select * from students" rst.Open sqlStr, cnn, adopenkeyset, adLockOptimistic title = Array("ID", "sName", "sSex", "sAddress") '數(shù)據(jù)庫中需要提取內(nèi)容的字段(部分或者全部) rst.Filter = "sAddress <>'武漢'" '過濾住址為武漢的記錄 Rem 第二個(gè)參數(shù)設(shè)置為adbookmarkfirst表示從第1行開始,返回?cái)?shù)組的第1個(gè)下標(biāo)標(biāo)識(shí)字段，第2個(gè)下標(biāo)表示記錄編號(hào) arr = rst.getrows(adgetrowsrest, adbookmarkfirst, title) Worksheets("Sheet1").[A1].Resize(UBound(arr, 2) + 1, UBound(arr, 1) + 1) = Application.WorksheetFunction.Transpose(arr)
rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub
注：getrows的第三個(gè)參數(shù)為需要讀取的字段,。第二個(gè)字段可以取值如下
|
|
|
