VBA連接 SQL SERVER 數(shù)據(jù)庫 實(shí)例.doc

《VBA連接 SQL SERVER 數(shù)據(jù)庫 實(shí)例.doc》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫。

1、VBA中用ADO訪問SQLSERVER數(shù)據(jù)庫：數(shù)據(jù)查詢?yōu)榱烁玫慕忉?，特提供兩個(gè)實(shí)例：使用數(shù)據(jù)對象ADO訪問SQL數(shù)據(jù)庫，從而進(jìn)行數(shù)據(jù)查詢，并將查詢的數(shù)據(jù)返回到EXCEL中，由于此程序需用到SQL數(shù)據(jù)庫，所以只供大家參考，不便運(yùn)行，但大家可以看出其精華！====================================================實(shí)例1：====================================================???PrivateSubComm

2、andButton1_Click()??DimcnAsNewADODB.ConnectionDimrstAsNewADODB.RecordsetDimR,C,F,IAsIntegerDimSql_text,day1,linenumber,boxAsStringConstcnnstr="Provider=SQLOLEDB;"&_????????????????????????????"DataSource=apsgszml04;"&_????????????????????????????"Initi

3、alCatalog=bhl2ken;UserID=sa;Password=;"'連接數(shù)據(jù)庫'默認(rèn)SQLServer服務(wù)器：LOCAL'用戶：sa'密碼：空'數(shù)據(jù)庫：Northwind'表源：Customersday1=UserForm1.TextBox1.Textlinenumber=UserForm1.ComboBox1.Textbox=UserForm1.ComboBox2.Textcn.OpencnnstrSql_text=Sql_text&"SELECTCONVERT(Char,dbo.TR

4、Y123.[day],101)asdate1,"???Sql_text=Sql_text&"dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnumber"???Sql_text=Sql_text&"FROMdbo.TRY123"???Sql_text=Sql_text&"WHERE(CONVERT(Char,dbo.TRY123.[day],101)='"&day1&"'anddbo.TRY1

5、23.linenumber='"&linenumber&"'anddbo.TRY123.box_no='"&box&"')"???Sql_text=Sql_text&"ORDERBYdbo.TRY123.serialnumber"‘使用SQL數(shù)據(jù)庫查詢語言查詢????????????????????????????'SELECT????CONVERT(Char,dbo.TRY123.[day],101)asdate1,dbo.TRY123.linenumber,dbo.TRY123.box_no,d

6、bo.TRY123.serialnumber,dbo.TRY123.lotnumber''FROM??????dbo.TRY123'WHERE??????(CONVERT(Char,dbo.TRY123.[day],101)="&day1&"anddbo.TRY123.linenumber="&linenumber&"anddbo.TRY123.box_no='"&box&"''ORDERBY??dbo.TRY123.serialnumberrst.OpenSql_text,cn,adOpenSta

7、tic,adLockBatchOptimistic'用adOpenStatic+adLockBatchOptimistic打開較快，且占用資源小R=5??'Excel表的行序號C=3??'Excel表的列序號I=0??'SQL表的字段序號F=rst.Fields.Count-1Worksheets("sheet1").UnprotectWorksheets("sheet1").Cells.ClearContentsWhileNotrst.EOF???ForI=0ToF???????Sheet1.Ce

8、lls(R,I+3).Rows.Value=rst.Fields(I).Value???NextI???R=R+1???rst.MoveNext???????’將數(shù)據(jù)庫的數(shù)據(jù)返回到EXCEL表中WendWorksheets("sheet1").ProtectUserForm1.Hide'MsgBox("讀取完畢")rst.Close??'完成后要關(guān)閉cn.Close???'完成后要關(guān)閉'注意：以上為直接連接SQLServer的方法，不用建ODBC數(shù)據(jù)源Workshee