1、vba連接中連接sql,access等數據的方法 , vba連接中連接sql，access等數據的方法 dim cnn as new adodb.connection 定義一個新的ado對象連接 dim rst as new adodb.recordset 定義一個ado對象數據集 dim stpath, strsql as string定義路徑、查詢變量 stpath = thisworkbook.path application.pathseparator 同學檔案.mdb定義路徑及文件名 cnn.open provider=microsoft.jet.oledb.4.0;data sou

2、rce= stpath ' ;jet oledb:database password= 123打開鏈接： provider=microsoft.jet.oledb.4.0是軟件供應者為microsoft.jet.oledb.4.0 data source= stpath 是鏈接數據源為stpath jet oledb:database password= 123是假如access數據庫設置有愛護密碼，此句必不行少 if combobox3.value = then假如性別的框中為空則 strsql = select * from 檔案 where 籍貫 like ' combo

3、box2.value '從檔案表中查找籍貫為combobox2的記錄 select*是查找全部符合條件的字段,假如想查找符合條件并顯示出詳細字段,可以用select 字段名1,字段名2.from 檔案是從檔案表中查找符合 籍貫 like ' combobox2.value ' 的記錄 where后為查找的條件 elseif combobox2.value = then strsql = select * from 檔案 where 性別 like ' combobox3.value ' else strsql = select * from 檔案 whe

4、re 性別 like ' combobox3.value ' and 籍貫 like ' combobox2.value ' end if , 以上幾句為當選取項目不同時設置不同的查找語句 rst.open strsql, cnn打開記錄集 recordset.open source(來記錄來源), activeconnection(打開的鏈接) sheet1.range(a2:g100).clearcontents sheet1.cells(2, 1).copyfromrecordset rst copyfromrecordset 方法 將一個 ado 或 d

5、ao recordset 對象的內容復制到工作表中，復制的起始位置在指定區域的左上角,sheet1.cells(2, 1).copyfromrecordset rst為把查找到的記錄得制到以sheet1.cells(2, 1)為頂點的單元格區域中 rst.close關閉記錄集 set rst = nothing釋放對象變量 set cnn = nothing 2：實現查詢功能（ado+sql） on error goto 100 if textbox1.text = then msgbox 請輸入姓名, 1 + 16, 系統提示 textbox1.setfocus else dim cnn a

6、s new adodb.connection dim rst as new adodb.recordset dim stpath, strsql as string stpath = thisworkbook.path application.pathseparator 同學檔案.mdb cnn.open provider=microsoft.jet.oledb.4.0;data source= stpath ' ;jet oledb:database password= 123 strsql = select * from 檔案 where 姓名 like ' textbox

7、1.value ' rst.open strsql, cnn , textbox2.value = rst.fields(年齡).value textbox4.value = rst.fields(性別).value textbox5.value = rst.fields(籍貫).value rst.close set rst = nothing set cnn = nothing end if exit sub 100: msgbox 找不到符合條件的記錄, 1 + 16, 系統提示 實現查詢功能（dao） on error goto 100 if textbox1.text = t

8、hen msgbox 請輸入姓名, 1 + 16, 系統提示 textbox1.setfocus else dim rs1 as recordset dim db1 as database set db1 = opendatabase(thisworkbook.path 同學檔案.mdb) set rs1 = db1.openrecordset(name:=檔案, type:=dbopendynaset) rs1.findfirst 姓名=' textbox1.value ' if rs1.nomatch = true then msgbox 對不起，沒有該記錄 rs1.clo

9、se exit sub , else textbox2.value = rs1.fields(年齡).value textbox4.value = rs1.fields(性別).value textbox5.value = rs1.fields(籍貫).value textbox6.value = rs1.fields(聯系電話).value end if rs1.close set rs1 = nothing set db1 = nothing end if exit sub 100: msgbox 找不到符合條件的記錄, 1 + 16, 系統提示 3：實現數據輸入功能（利用dao） 代碼：

10、 dim rs1 as recordset dim db1 as database on error goto 1000 set db1 = opendatabase(thisworkbook.path 同學檔案.mdb) rs1 = db1.openrecordset(name:=檔案, type:=dbopendynaset) with rs1 .addnew .fields(姓名).value = me.textbox1.value .fields(年齡).value = me.textbox2.value .fields(性別).value = me.textbox4.value ,

11、.fields(籍貫).value = me.textbox5.value .fields(聯系電話).value = me.textbox6.value .update msgbox 檔案表中增加了一條記錄! end with db1.close exit sub 4：實現修改指定記錄功能 on error goto 100 if textbox1.text = then msgbox 請輸入姓名, 1 + 16, 系統提示 textbox1.setfocus else dim rs1 as recordset dim db1 as database set db1 = opendataba

12、se(thisworkbook.path 同學檔案.mdb) set rs1 = db1.openrecordset(name:=檔案, type:=dbopendynaset) rs1.findfirst 姓名=' textbox1.value ' rs1.edit rs1.fields(年齡).value = textbox2.value rs1.fields(性別).value = textbox4.value rs1.fields(籍貫).value = textbox5.value rs1.fields(聯系電話).value = textbox6.value rs1

13、.update , rs1.close set rs1 = nothing set db1 = nothing end if exit sub 100: msgbox 找不到符合條件的記錄, 1 + 64, 系統提示 5：實現刪除指定記錄功能 on error goto 100 if textbox1.text = then msgbox 請輸入姓名, 1 + 16, 系統提示 textbox1.setfocus else dim rs1 as recordset dim db1 as database set db1 = opendatabase(thisworkbook.path 同學檔案

14、.mdb) set rs1 = db1.openrecordset(name:=檔案, type:=dbopendynaset) rs1.findfirst 姓名=' textbox1.value ' rs1.delete rs1.update rs1.close set rs1 = nothing set db1 = nothing end if exit sub , 100: msgbox 找不到符合條件的記錄, 1 + 64, 系統提示 6：實現排序功能 例：查詢結果按年齡字段排序 strsql = select * from 檔案 where 性別 like '

15、 combobox3.value ' 改為 升序排列（默認） strsql = select * from 檔案 where 籍貫 like ' combobox3.value 'order by 年齡 降序排列 strsql = select * from 檔案 where 籍貫 like ' combobox2.value 'order by 年齡 desc 7：實現分類匯總功能 dim cnn as new adodb.connection dim rst as new adodb.recordset dim stpath, strsql as string stpath = thisworkbook.path application.pathseparator 同學檔案.mdb cnn.open provider=microsoft.jet.oledb.4.0;data source= stpath '