VBA環(huán)境下ACCESS、EXCEL、SQLSERVER互相訪問

1、實(shí)例7-1判斷SQLServer數(shù)據(jù)庫是否存在（ADO）弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_1()DimcnnAsADODB.ConnectionDimcnnStrAsStringDimmydataAsStringmydata=商品信息創(chuàng)建與SQLServer數(shù)據(jù)庫服務(wù)器連接的Connection對(duì)象Setcnn=NewADODB.Connection設(shè)置建立與SQLServer數(shù)據(jù)庫服務(wù)器中指定數(shù)據(jù)庫連接的字符串cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFC

2、OMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStr試圖打開指定的數(shù)據(jù)庫OnErrorResumeNextcnn.OpenOnErrorGoTo0判斷數(shù)據(jù)庫是否存在Ifcnn.State=adStateOpenThenMsgBox數(shù)據(jù)庫&mydata&存在cnn.CloseSetcnn=NothingElseMsgBox數(shù)據(jù)庫&mydata&不存在EndIfEndSub分析：判斷在SQLServer數(shù)據(jù)庫服務(wù)器上是否存在指定的某個(gè)數(shù)據(jù)庫，方法就是度圖去打開SQLServer數(shù)據(jù)庫服務(wù)器的某個(gè)數(shù)據(jù)庫，如能打開則表明該數(shù)據(jù)庫存在，否

3、則就不存在。利用ADODB.Connection對(duì)象的State屬性即可實(shí)現(xiàn)。State屬性用于檢查Connection對(duì)象是打開還是關(guān)閉，或處于連接狀態(tài)。如返回值為adstateopen,則表明已打開，如返回adstateclosed，則表明關(guān)閉，如返回adstateconnecting,表明是在連接。SQLOLEDB.1表示數(shù)據(jù)庫類型為SQLServer指定打開數(shù)據(jù)庫的密碼，可省略指定訪問為數(shù)據(jù)庫的用戶名_指定數(shù)據(jù)庫服務(wù)器名稱或IP地址指定要訪問的數(shù)據(jù)庫名例：cnnStr=Provider=SQLOLEDB.1;_&”Password=12345;”_&UserID=sa;_&DataSo

4、urce=THTFCOMP&InitialCatalog=&mydata-實(shí)例7-2檢查數(shù)據(jù)表是否存在（ADOX）弓丨用：microsoftadoext.2.xforddlandsecurityPublicSub7_2()DimmyCatAsADOX.CatalogDimmtbAsADOX.TableDimcnnStrAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名錄建立與SQLServer數(shù)據(jù)庫服務(wù)器指定數(shù)據(jù)庫的連接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSourc

5、e=THTFCOMPUTER;_&InitialCatalog=&mydataSetmyCat=NewADOX.CatalogmyCat.ActiveConnection=cnnStr判斷數(shù)據(jù)表是否存在ForEachmtbInmyCat.TablesIfLCase(mtb.Name)=LCase(myTable)ThenMsgBox數(shù)據(jù)表存在！GoTohhhEndIfNextMsgBox數(shù)據(jù)表&myTable&不存在！hhh:Setmtb=NothingSetmyCat=NothingEndSub實(shí)例7-3創(chuàng)建新的SQLServer數(shù)據(jù)庫和數(shù)據(jù)表（ADO）PublicSub7_3()Dimcn

6、nAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名錄創(chuàng)建與SQLServer數(shù)據(jù)庫服務(wù)器中指定數(shù)據(jù)庫連接的connection對(duì)象Setcnn=NewADODB.ConnectioncnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStrOnErrorResumeNe

7、xtcnn.OpenOnErrorGoTo0判斷數(shù)據(jù)庫是否存在Ifcnn.State=adStateOpenThenMsgBox數(shù)據(jù)庫已經(jīng)存在，請(qǐng)重新命名數(shù)據(jù)庫名！,vbCriticalcnn.CloseSetcnn=NothingExitSubEndIf建立與SQLServer數(shù)據(jù)庫服務(wù)器的連接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTERcnn.ConnectionString=cnnStrcnn.Open執(zhí)行SQL語句創(chuàng)建數(shù)據(jù)庫SQL=createdatabase&mydatacnn.ExecuteSQLM

8、sgBox數(shù)據(jù)庫創(chuàng)建成功!,vbInformation,創(chuàng)建數(shù)據(jù)庫關(guān)閉與SQLServer數(shù)據(jù)庫服務(wù)器的連接cnn.Close重新建立與SQLServer數(shù)據(jù)庫服務(wù)器的連接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStrcnn.Open執(zhí)行SQL語句創(chuàng)建數(shù)據(jù)表SQL=createtable&myTable_&(商品編號(hào)varchar(10)notnullprimarykey,_&商品名稱varchar(20)no

9、tnull)cnn.ExecuteSQLMsgBox數(shù)據(jù)表創(chuàng)建成功!,vbInformation,創(chuàng)建數(shù)據(jù)表cnn.CloseSetcnn=NothingEndSub分析：SQL=”createtable表名(字段字段類型(字段長度)是否允許空值是否為主鍵”創(chuàng)建數(shù)據(jù)表實(shí)例7-4在已有的SQLServre數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)表(ADO)弓丨用：microsoftactivedataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurityPublicSub實(shí)例7_4()DimcnnAsADODB.ConnectionDimmyCatAsADOX.Ca

10、talogDimmtbAsADOX.TableDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品價(jià)格信息建立與SQLServer數(shù)據(jù)庫服務(wù)器中指定數(shù)據(jù)庫的連接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith判斷在數(shù)據(jù)庫中是否有同名的數(shù)據(jù)表Se

11、tmyCat=NewADOX.CatalogSetmyCat.ActiveConnection=cnnForEachmtbInmyCat.TablesIfLCase(mtb.Name)=LCase(myTable)ThenMsgBox數(shù)據(jù)表&myTable&已經(jīng)存在！請(qǐng)重新命名數(shù)據(jù)表！,vbCriticalGoTohhhEndIfNext執(zhí)行SQL語句創(chuàng)建數(shù)據(jù)表SQL=createtable&myTable_&(商品編號(hào)varchar(10)notnullprimarykey,_&商品單價(jià)decimalnotnull)cnn.ExecuteSQLMsgBox數(shù)據(jù)表創(chuàng)建成功!,vbInforma

12、tion,創(chuàng)建數(shù)據(jù)表hhh:cnn.CloseSetmyCat=NothingSetcnn=NothingEndSub實(shí)例7-5從SQLServer數(shù)據(jù)庫服務(wù)器中刪除數(shù)據(jù)庫(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_5()OnErrorGoTohhhDimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsString指定要?jiǎng)h除的數(shù)據(jù)庫名稱mydata=商品信息建立與SQLServer數(shù)據(jù)庫服務(wù)器的連接Setcnn=NewA

13、DODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER.OpenEndWith執(zhí)行SQL語句刪除指定的數(shù)據(jù)庫SQL=dropdatabase&mydatacnn.ExecuteSQLMsgBox數(shù)據(jù)庫刪除成功!,vbInformation,刪除數(shù)據(jù)庫GoToxxxhhh:MsgBoxErr.Description,vbCriticalxxx:cnn.CloseSetcnn=NothingEndSub分析：SQL=”dropdatabase數(shù)據(jù)庫名1,數(shù)據(jù)庫

14、名2,”刪除指定的數(shù)據(jù)庫實(shí)例7-6從SQLServer數(shù)據(jù)庫中刪除數(shù)據(jù)表(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_6()OnErrorGoTohhhDimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名錄,商品價(jià)格信息建立與SQLServer數(shù)據(jù)庫的連接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provi

15、der=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith執(zhí)行SQL語句刪除指定的數(shù)據(jù)表SQL=droptable&myTablecnn.ExecuteSQLMsgBox數(shù)據(jù)表刪除成功!,vbInformation,刪除數(shù)據(jù)表GoToxxxhhh:MsgBoxErr.Description,vbCriticalxxx:cnn.CloseSetcnn=NothingEndSub實(shí)例7-7將SQLServer數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入到Excel工作表(ADO)弓丨用：microsoft

16、activexdataobjects2.xlibraryPublicSub7_7()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsStringmydata=工資管理myTable=基本信息建立與SQLServer數(shù)據(jù)庫的連接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&m

17、ydata.OpenEndWith打開查詢數(shù)據(jù)集Setrs=NewADODB.Recordsetrs.OpenmyTable,cnn,adOpenKeyset,adLockOptimistic清除工作表數(shù)據(jù)Cells.Clear準(zhǔn)備復(fù)制數(shù)據(jù)Withrs復(fù)制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext復(fù)制記錄Range(A2).CopyFromRecordsetrsEndWith關(guān)閉數(shù)據(jù)集和數(shù)據(jù)庫連接，并釋放變量rs.Closecnn.CloseSetrs=NothingSetcnn=NothingEndSub實(shí)例7

18、-8將SQLServer數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入到Excel工作表(DAO)弓丨用：microsoftdao3.6objectlibraryPublicSub7_8()DimmyWspAsDAO.WorkspaceDimcnnAsDAO.ConnectionDimrsAsDAO.RecordsetDimcnnStrAsStringDimmydataAsString,myTableAsStringDimiAsLongmydata=工資管理myTable=基本信息設(shè)置連接字符串cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=THTFCOMPUTER;UID=sa;_&PW

19、D=;DATABASE=&mydata&;FILEDSN=C:建立與數(shù)據(jù)庫的連接SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)Workspaces.AppendmyWspSetcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)建立查詢數(shù)據(jù)集Setrs=cnn.OpenRecordset(myTable)清除工作表數(shù)據(jù)Cells.Clear準(zhǔn)備復(fù)制數(shù)據(jù)Withrs復(fù)制字段名Fori=1To.Fields.CountCells

20、(1,i).Value=.Fields(i-1).NameNext復(fù)制記錄Range(A2).CopyFromRecordsetrsEndWith關(guān)閉數(shù)據(jù)集和數(shù)據(jù)庫連接，并釋放變量rs.Closecnn.ClosemyWsp.CloseSetrs=NothingSetcnn=NothingSetmyWsp=NothingEndSub利用DAO連接SQLServer數(shù)據(jù)庫的連接字符串。一般形式為：cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=SQLServer服務(wù)器名;UID=sa;_&PWD三DATABASE=數(shù)據(jù)庫名;FILEDSN=C:建立與SQLServ

21、er數(shù)據(jù)庫的連接，首先需要利用Createworkspace方法建立一個(gè)新的workspace對(duì)象：setworkspace對(duì)象變量=createworkspace(name,user,password,type)name指定該工作區(qū)workspace對(duì)象的名稱，user設(shè)置該工作區(qū)的用戶名，password是用戶密碼，type指定類型：有jet型(dbusejet)和odbc型(dbuseodbc)本例：SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)再使用workspace集合的append方法將這個(gè)workspace對(duì)象添加到workspa

22、ces集合中。workspaces.appendmywsp利用openconnection方法建立與SQLServer數(shù)據(jù)庫的連接setconnection=workspace.openconnection(name,options,readonly,connect)connection:表示DAO.connection對(duì)象變量workspace(可選)：扌旨定workspace對(duì)象name:為字符串表達(dá)式，是創(chuàng)建的workspace對(duì)象名稱options(可選)：指定連接的名稱Readonly(可選)：表示是否為只讀打開數(shù)據(jù)庫。默認(rèn)為F。表示可讀/寫數(shù)據(jù)。connect(可選)：指定ODBC

23、連接字符串。例：Setcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)實(shí)例7-9查詢獲取SQLServer數(shù)據(jù)庫的數(shù)據(jù)（ADO）弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_9()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsString,SQLAsStringmydata=工資管理myTabl

24、e=基本信息建立與SQLServer數(shù)據(jù)庫的連接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith打開查詢數(shù)據(jù)集SQL=select*from&myTable&where性別=男Setrs=NewADODB.Recordsetrs.OpenSQL,cnn,adOpenKeyset,adLockOptimistic清除工作表數(shù)據(jù)Cells.Clear準(zhǔn)備復(fù)制數(shù)

25、據(jù)Withrs復(fù)制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext復(fù)制記錄Range(A2).CopyFromRecordsetrsEndWith關(guān)閉數(shù)據(jù)集和數(shù)據(jù)庫連接，并釋放變量rs.Closecnn.CloseSetrs=NothingSetcnn=NothingEndSub實(shí)例7-10查詢獲取SQLServer數(shù)據(jù)庫的數(shù)據(jù)（DAO）弓丨用：microsoftdao3.6objectlibraryPublicSub7_10()DimmyWspAsDAO.WorkspaceDimcnnAsDAO.Connection

26、DimrsAsDAO.RecordsetDimcnnStrAsStringDimSQLAsStringDimmydataAsString,myTableAsStringDimiAsLongmydata=工資管理myTable=基本信息設(shè)置連接字符串cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=THTFCOMPUTER;UID=sa;_&PWD=;DATABASE=&mydata&;FILEDSN=C:建立與數(shù)據(jù)庫的連接SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)Workspaces.AppendmyWspSe

27、tcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)建立查詢數(shù)據(jù)集SQL=select*from&myTable&where性別=男Setrs=cnn.OpenRecordset(SQL)清除工作表數(shù)據(jù)Cells.Clear準(zhǔn)備復(fù)制數(shù)據(jù)Withrs復(fù)制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext復(fù)制記錄Range(A2).CopyFromRecordsetrsEndWith關(guān)閉數(shù)據(jù)集和數(shù)據(jù)庫連

28、接，并釋放變量rs.Closecnn.ClosemyWsp.CloseSetrs=NothingSetcnn=NothingSetmyWsp=NothingEndSub實(shí)例7-11將工作表數(shù)據(jù)導(dǎo)入到SQLServer數(shù)據(jù)庫(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_11()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsString,SQLAsStringDimwbAsWorkbookDimwsAsWorks

29、heetSetwb=ThisWorkbookSetws=wb.Sheets(sheet1)mydata=工資管理myTable=基本信息指定工作簿指定工作表名稱指定數(shù)據(jù)庫指定數(shù)據(jù)表建立與SQLServer數(shù)據(jù)庫的連接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith獲取要保存的記錄數(shù)(行數(shù))n=ws.Range(A65536).End(xlUp).RowFo

30、ri=2Ton查詢是否已經(jīng)存在了某條記錄SQL=select*from&myTable_&where職工編號(hào)=&Trim(ws.Cells(i,1).Value)&and姓名=&Trim(ws.Cells(i,2).Value)&_&and性別=&Trim(ws.Cells(i,3).Value)&_&and所屬部門=&Trim(ws.Cells(i,4).Value)&_&and工資總額=&Trim(ws.Cells(i,5).Value)_&and備注=&Trim(ws.Cells(i,6).Value)&Setrs=NewADODB.Recordsetrs.OpenSQL,cnn,adO

31、penKeyset,adLockOptimisticIfrs.EOFAndrs.BOFThen如要數(shù)據(jù)表中沒有工作表的某行數(shù)據(jù)，就添加到數(shù)據(jù)表rs.AddNewForj=1Tors.Fields.Countrs.Fields(j-1)=Trim(ws.Cells(i,j).Value)Nextjrs.UpdateElse如果數(shù)據(jù)表中有工作表的某行數(shù)據(jù)，就將數(shù)據(jù)進(jìn)行更新Forj=1Tors.Fields.Countrs.Fields(j-1)=Trim(ws.Cells(i,j).Value)Nextjrs.UpdateEndIfNextiMsgBox數(shù)據(jù)保存完畢！,vbInformation,

32、保存數(shù)據(jù)關(guān)閉數(shù)據(jù)庫及查詢數(shù)據(jù)集，并釋放變量rs.Closecnn.CloseSetwb=NothingSetws=NothingSetrs=NothingSetcnn=NothingEndSub實(shí)例7-12向SQLServer數(shù)據(jù)庫中添加記錄的一般方法弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_12()DimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名錄建立與SQLSer

33、ver數(shù)據(jù)庫服務(wù)器的連接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydataSetcnn=NewADODB.Connectioncnn.ConnectionString=cnnStrcnn.Open為數(shù)據(jù)表添加新記錄SQL=insertinto&myTable&values(1000000001,25彩電)cnn.ExecuteSQLSQL=insertinto&myTable&values(1000000002,29彩電)cnn.ExecuteSQLSQL=inser

34、tinto&myTable&values(2000000001,空調(diào))cnn.ExecuteSQLMsgBox為數(shù)據(jù)表添加了三條記錄!,vbInformation,添加記錄cnn.CloseSetcnn=NothingEndSub分析：SQL=”insertinto數(shù)據(jù)表名values(字段1值，字段2值，)”向SQLServer數(shù)據(jù)庫中添加記錄實(shí)例7-13將SQLServer數(shù)據(jù)庫轉(zhuǎn)換為Access數(shù)據(jù)庫弓丨用：microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurityPublicSub7_13()Di

35、mcnnSQLAsNewADODB.ConnectionDimrsSQLAsNewADODB.RecordsetDimcnnDataAsNewADODB.ConnectionDimrsDataAsNewADODB.RecordsetDimmyCatAsNewADOX.CatalogDimmyTblAsNewADOX.TableDimmySQLAsStringDimmyDataAsStringDimmyTableAsStringDimmyNameAsString,myTypeAsInteger,mySizeAsIntegerDimiAsLong,jAsLong*查詢獲取SQLServer數(shù)據(jù)庫服

36、務(wù)器上指定數(shù)據(jù)庫表表示全部數(shù)據(jù)*myData=商品信息myTable=商品名錄建立與SQLServer數(shù)據(jù)庫的連接CnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&myDataWithcnnSQL.ConnectionString=CnnStr.OpenEndWith查詢SQLServer數(shù)據(jù)庫指定數(shù)據(jù)庫表的全部記錄rsSQL.OpenmyTable,cnnSQL,adOpenKeyset,adLockOptimistic*將查詢到SQLServer數(shù)據(jù)庫表全部數(shù)據(jù)保存到Acce

37、ss數(shù)據(jù)庫表*創(chuàng)建Access數(shù)據(jù)庫和數(shù)據(jù)表myData=ThisWorkbook.Path&商品信息.mdbOnErrorResumeNextKillmyDataOnErrorGoTo0myCat.CreateProvider=Microsoft.Jet.OLEDB.4.0;DataSource=&myDataWithmyTbl.Name=myTable指定Access數(shù)據(jù)表名Fori=0TorsSQL.Fields.Count-1myName=rsSQL.Fields(i).NamemyType=rsSQL.Fields(i).TypeIfrsSQL.Fields(i).Type=200ThenmyType=adVarWCharIfrsSQL.Fields(i).Type=135ThenmyType=adDatemySize=rsSQL.Fields(i).DefinedSize.Columns.AppendmyName,myType,mySizeNextiEndWithmyCat.Tables.AppendmyTbl向access數(shù)據(jù)表中添加數(shù)據(jù)