VBA环境下ACCESS、EXCEL、SQLSERVER互相访问

《VBA环境下ACCESS、EXCEL、SQLSERVER互相访问》由会员分享，可在线阅读，更多相关《VBA环境下ACCESS、EXCEL、SQLSERVER互相访问（23页珍藏版）》请在装配图网上搜索。

1、实例7-1判断SQLServer数据库是否存在（ADO）弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_1()DimcnnAsADODB.ConnectionDimcnnStrAsStringDimmydataAsStringmydata=商品信息创建与SQLServer数据库服务器连接的Connection对象Setcnn=NewADODB.Connection设置建立与SQLServer数据库服务器中指定数据库连接的字符串cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFC

2、OMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStr试图打开指定的数据库OnErrorResumeNextcnn.OpenOnErrorGoTo0判断数据库是否存在Ifcnn.State=adStateOpenThenMsgBox数据库&mydata&存在cnn.CloseSetcnn=NothingElseMsgBox数据库&mydata&不存在EndIfEndSub分析：判断在SQLServer数据库服务器上是否存在指定的某个数据库，方法就是度图去打开SQLServer数据库服务器的某个数据库，如能打开则表明该数据库存在，否

3、则就不存在。利用ADODB.Connection对象的State属性即可实现。State属性用于检查Connection对象是打开还是关闭，或处于连接状态。如返回值为adstateopen,则表明已打开，如返回adstateclosed，则表明关闭，如返回adstateconnecting,表明是在连接。SQLOLEDB.1表示数据库类型为SQLServer指定打开数据库的密码，可省略指定访问为数据库的用户名_指定数据库服务器名称或IP地址指定要访问的数据库名例：cnnStr=Provider=SQLOLEDB.1;_&”Password=12345;”_&UserID=sa;_&DataSo

4、urce=THTFCOMP&InitialCatalog=&mydata-实例7-2检查数据表是否存在（ADOX）弓丨用：microsoftadoext.2.xforddlandsecurityPublicSub7_2()DimmyCatAsADOX.CatalogDimmtbAsADOX.TableDimcnnStrAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名录建立与SQLServer数据库服务器指定数据库的连接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSourc

5、e=THTFCOMPUTER;_&InitialCatalog=&mydataSetmyCat=NewADOX.CatalogmyCat.ActiveConnection=cnnStr判断数据表是否存在ForEachmtbInmyCat.TablesIfLCase(mtb.Name)=LCase(myTable)ThenMsgBox数据表存在！GoTohhhEndIfNextMsgBox数据表&myTable&不存在！hhh:Setmtb=NothingSetmyCat=NothingEndSub实例7-3创建新的SQLServer数据库和数据表（ADO）PublicSub7_3()Dimcn

6、nAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名录创建与SQLServer数据库服务器中指定数据库连接的connection对象Setcnn=NewADODB.ConnectioncnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStrOnErrorResumeNe

7、xtcnn.OpenOnErrorGoTo0判断数据库是否存在Ifcnn.State=adStateOpenThenMsgBox数据库已经存在，请重新命名数据库名！,vbCriticalcnn.CloseSetcnn=NothingExitSubEndIf建立与SQLServer数据库服务器的连接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTERcnn.ConnectionString=cnnStrcnn.Open执行SQL语句创建数据库SQL=createdatabase&mydatacnn.ExecuteSQLM

8、sgBox数据库创建成功!,vbInformation,创建数据库关闭与SQLServer数据库服务器的连接cnn.Close重新建立与SQLServer数据库服务器的连接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydatacnn.ConnectionString=cnnStrcnn.Open执行SQL语句创建数据表SQL=createtable&myTable_&(商品编号varchar(10)notnullprimarykey,_&商品名称varchar(20)no

9、tnull)cnn.ExecuteSQLMsgBox数据表创建成功!,vbInformation,创建数据表cnn.CloseSetcnn=NothingEndSub分析：SQL=”createtable表名(字段字段类型(字段长度)是否允许空值是否为主键”创建数据表实例7-4在已有的SQLServre数据库中创建数据表(ADO)弓丨用：microsoftactivedataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurityPublicSub实例7_4()DimcnnAsADODB.ConnectionDimmyCatAsADOX.Ca

10、talogDimmtbAsADOX.TableDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品价格信息建立与SQLServer数据库服务器中指定数据库的连接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith判断在数据库中是否有同名的数据表Se

11、tmyCat=NewADOX.CatalogSetmyCat.ActiveConnection=cnnForEachmtbInmyCat.TablesIfLCase(mtb.Name)=LCase(myTable)ThenMsgBox数据表&myTable&已经存在！请重新命名数据表！,vbCriticalGoTohhhEndIfNext执行SQL语句创建数据表SQL=createtable&myTable_&(商品编号varchar(10)notnullprimarykey,_&商品单价decimalnotnull)cnn.ExecuteSQLMsgBox数据表创建成功!,vbInforma

12、tion,创建数据表hhh:cnn.CloseSetmyCat=NothingSetcnn=NothingEndSub实例7-5从SQLServer数据库服务器中删除数据库(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_5()OnErrorGoTohhhDimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsString指定要删除的数据库名称mydata=商品信息建立与SQLServer数据库服务器的连接Setcnn=NewA

13、DODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER.OpenEndWith执行SQL语句删除指定的数据库SQL=dropdatabase&mydatacnn.ExecuteSQLMsgBox数据库删除成功!,vbInformation,删除数据库GoToxxxhhh:MsgBoxErr.Description,vbCriticalxxx:cnn.CloseSetcnn=NothingEndSub分析：SQL=”dropdatabase数据库名1,数据库

14、名2,”删除指定的数据库实例7-6从SQLServer数据库中删除数据表(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_6()OnErrorGoTohhhDimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名录,商品价格信息建立与SQLServer数据库的连接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provi

15、der=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith执行SQL语句删除指定的数据表SQL=droptable&myTablecnn.ExecuteSQLMsgBox数据表删除成功!,vbInformation,删除数据表GoToxxxhhh:MsgBoxErr.Description,vbCriticalxxx:cnn.CloseSetcnn=NothingEndSub实例7-7将SQLServer数据库中的数据导入到Excel工作表(ADO)弓丨用：microsoft

16、activexdataobjects2.xlibraryPublicSub7_7()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsStringmydata=工资管理myTable=基本信息建立与SQLServer数据库的连接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&m

17、ydata.OpenEndWith打开查询数据集Setrs=NewADODB.Recordsetrs.OpenmyTable,cnn,adOpenKeyset,adLockOptimistic清除工作表数据Cells.Clear准备复制数据Withrs复制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext复制记录Range(A2).CopyFromRecordsetrsEndWith关闭数据集和数据库连接，并释放变量rs.Closecnn.CloseSetrs=NothingSetcnn=NothingEndSub实例7

18、-8将SQLServer数据库中的数据导入到Excel工作表(DAO)弓丨用：microsoftdao3.6objectlibraryPublicSub7_8()DimmyWspAsDAO.WorkspaceDimcnnAsDAO.ConnectionDimrsAsDAO.RecordsetDimcnnStrAsStringDimmydataAsString,myTableAsStringDimiAsLongmydata=工资管理myTable=基本信息设置连接字符串cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=THTFCOMPUTER;UID=sa;_&PW

19、D=;DATABASE=&mydata&;FILEDSN=C:建立与数据库的连接SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)Workspaces.AppendmyWspSetcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)建立查询数据集Setrs=cnn.OpenRecordset(myTable)清除工作表数据Cells.Clear准备复制数据Withrs复制字段名Fori=1To.Fields.CountCells

20、(1,i).Value=.Fields(i-1).NameNext复制记录Range(A2).CopyFromRecordsetrsEndWith关闭数据集和数据库连接，并释放变量rs.Closecnn.ClosemyWsp.CloseSetrs=NothingSetcnn=NothingSetmyWsp=NothingEndSub利用DAO连接SQLServer数据库的连接字符串。一般形式为：cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=SQLServer服务器名;UID=sa;_&PWD三DATABASE=数据库名;FILEDSN=C:建立与SQLServ

21、er数据库的连接，首先需要利用Createworkspace方法建立一个新的workspace对象：setworkspace对象变量=createworkspace(name,user,password,type)name指定该工作区workspace对象的名称，user设置该工作区的用户名，password是用户密码，type指定类型：有jet型(dbusejet)和odbc型(dbuseodbc)本例：SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)再使用workspace集合的append方法将这个workspace对象添加到workspa

22、ces集合中。workspaces.appendmywsp利用openconnection方法建立与SQLServer数据库的连接setconnection=workspace.openconnection(name,options,readonly,connect) connection:表示DAO.connection对象变量 workspace(可选)：扌旨定workspace对象 name:为字符串表达式，是创建的workspace对象名称 options(可选)：指定连接的名称Readonly(可选)：表示是否为只读打开数据库。默认为F。表示可读/写数据。 connect(可选)：指

23、定ODBC连接字符串。例：Setcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)实例7-9查询获取SQLServer数据库的数据（ADO）弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_9()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsString,SQLAsStringmydata=工资管理m

24、yTable=基本信息建立与SQLServer数据库的连接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith打开查询数据集SQL=select*from&myTable&where性别=男Setrs=NewADODB.Recordsetrs.OpenSQL,cnn,adOpenKeyset,adLockOptimistic清除工作表数据Cells.Clear

25、准备复制数据Withrs复制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext复制记录Range(A2).CopyFromRecordsetrsEndWith关闭数据集和数据库连接，并释放变量rs.Closecnn.CloseSetrs=NothingSetcnn=NothingEndSub实例7-10查询获取SQLServer数据库的数据（DAO）弓丨用：microsoftdao3.6objectlibraryPublicSub7_10()DimmyWspAsDAO.WorkspaceDimcnnAsDAO.Conne

26、ctionDimrsAsDAO.RecordsetDimcnnStrAsStringDimSQLAsStringDimmydataAsString,myTableAsStringDimiAsLongmydata=工资管理myTable=基本信息设置连接字符串cnnStr=ODBC;_&Driver=SQLServer;_&SERVER=THTFCOMPUTER;UID=sa;_&PWD=;DATABASE=&mydata&;FILEDSN=C:建立与数据库的连接SetmyWsp=CreateWorkspace(myWsp,myName,dbUseODBC)Workspaces.Appendmy

27、WspSetcnn=myWsp.OpenConnection(_Name:=cnnnection,_Options:=dbDriverNoPrompt,_Connect:=cnnStr)建立查询数据集SQL=select*from&myTable&where性别=男Setrs=cnn.OpenRecordset(SQL)清除工作表数据Cells.Clear准备复制数据Withrs复制字段名Fori=1To.Fields.CountCells(1,i).Value=.Fields(i-1).NameNext复制记录Range(A2).CopyFromRecordsetrsEndWith关闭数据集

28、和数据库连接，并释放变量rs.Closecnn.ClosemyWsp.CloseSetrs=NothingSetcnn=NothingSetmyWsp=NothingEndSub实例7-11将工作表数据导入到SQLServer数据库(ADO)弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_11()DimcnnAsADODB.ConnectionDimrsAsADODB.RecordsetDimiAsLongDimmydataAsString,myTableAsString,SQLAsStringDimwbAsWorkbookDimwsAs

29、WorksheetSetwb=ThisWorkbookSetws=wb.Sheets(sheet1)mydata=工资管理myTable=基本信息指定工作簿指定工作表名称指定数据库指定数据表建立与SQLServer数据库的连接Setcnn=NewADODB.ConnectionWithcnn.ConnectionString=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydata.OpenEndWith获取要保存的记录数(行数)n=ws.Range(A65536).End(xlUp).

30、RowFori=2Ton查询是否已经存在了某条记录SQL=select*from&myTable_&where职工编号=&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,cn

31、n,adOpenKeyset,adLockOptimisticIfrs.EOFAndrs.BOFThen如要数据表中没有工作表的某行数据，就添加到数据表rs.AddNewForj=1Tors.Fields.Countrs.Fields(j-1)=Trim(ws.Cells(i,j).Value)Nextjrs.UpdateElse如果数据表中有工作表的某行数据，就将数据进行更新Forj=1Tors.Fields.Countrs.Fields(j-1)=Trim(ws.Cells(i,j).Value)Nextjrs.UpdateEndIfNextiMsgBox数据保存完毕！,vbInforma

32、tion,保存数据关闭数据库及查询数据集，并释放变量rs.Closecnn.CloseSetwb=NothingSetws=NothingSetrs=NothingSetcnn=NothingEndSub实例7-12向SQLServer数据库中添加记录的一般方法弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_12()DimcnnAsADODB.ConnectionDimcnnStrAsString,SQLAsStringDimmydataAsString,myTableAsStringmydata=商品信息myTable=商品名录建立与S

33、QLServer数据库服务器的连接cnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&mydataSetcnn=NewADODB.Connectioncnn.ConnectionString=cnnStrcnn.Open为数据表添加新记录SQL=insertinto&myTable&values(1000000001,25彩电)cnn.ExecuteSQLSQL=insertinto&myTable&values(1000000002,29彩电)cnn.ExecuteSQLSQL=

34、insertinto&myTable&values(2000000001,空调)cnn.ExecuteSQLMsgBox为数据表添加了三条记录!,vbInformation,添加记录cnn.CloseSetcnn=NothingEndSub分析：SQL=”insertinto数据表名values(字段1值，字段2值，)”向SQLServer数据库中添加记录实例7-13将SQLServer数据库转换为Access数据库弓丨用：microsoftactivexdataobjects2.xlibrary和microsoftadoext.2.xforddlandsecurityPublicSub7_1

35、3()DimcnnSQLAsNewADODB.ConnectionDimrsSQLAsNewADODB.RecordsetDimcnnDataAsNewADODB.ConnectionDimrsDataAsNewADODB.RecordsetDimmyCatAsNewADOX.CatalogDimmyTblAsNewADOX.TableDimmySQLAsStringDimmyDataAsStringDimmyTableAsStringDimmyNameAsString,myTypeAsInteger,mySizeAsIntegerDimiAsLong,jAsLong*查询获取SQLServe

36、r数据库服务器上指定数据库表表示全部数据*myData=商品信息myTable=商品名录建立与SQLServer数据库的连接CnnStr=Provider=SQLOLEDB.1;_&UserID=sa;_&DataSource=THTFCOMPUTER;_&InitialCatalog=&myDataWithcnnSQL.ConnectionString=CnnStr.OpenEndWith查询SQLServer数据库指定数据库表的全部记录rsSQL.OpenmyTable,cnnSQL,adOpenKeyset,adLockOptimistic*将查询到SQLServer数据库表全部数据保存

37、到Access数据库表*创建Access数据库和数据表myData=ThisWorkbook.Path&商品信息.mdbOnErrorResumeNextKillmyDataOnErrorGoTo0myCat.CreateProvider=Microsoft.Jet.OLEDB.4.0;DataSource=&myDataWithmyTbl.Name=myTable指定Access数据表名Fori=0TorsSQL.Fields.Count-1myName=rsSQL.Fields(i).NamemyType=rsSQL.Fields(i).TypeIfrsSQL.Fields(i).Type

38、=200ThenmyType=adVarWCharIfrsSQL.Fields(i).Type=135ThenmyType=adDatemySize=rsSQL.Fields(i).DefinedSize.Columns.AppendmyName,myType,mySizeNextiEndWithmyCat.Tables.AppendmyTbl向access数据表中添加数据SetcnnData=myCat.ActiveConnectionrsData.OpenmyTable,cnnData,adOpenKeyset,adLockOptimisticFori=1TorsSQL.RecordCou

39、ntWithrsData.AddNewForj=0TorsData.Fields.Count-1.Fields(j)=rsSQL.Fields(j).ValueNextj.UpdateEndWithrsSQL.MoveNextNextiMsgBox已经成功将SQLServer数据库数据保存为Access数据库！&vbCrLf_&数据库名为：_&vbCrLf&数据表名为：,vbInformation*关闭记录集与数据库的连接*rsSQL.ClosecnnSQL.ClosersData.ClosecnnData.Close释放变量SetrsSQL=NothingSetcnnSQL=NothingS

40、etrsData=NothingSetcnnData=NothingEndSub实例7-14将access数据库转换为SQLServer数据库弓丨用：microsoftactivexdataobjects2.xlibraryPublicSub7_14()DimcnnSQLAsNewADODB.ConnectionDimrsSQLAsNewADODB.RecordsetDimcnnDataAsNewADODB.ConnectionDimrsDataAsNewADODB.RecordsetDimmySQLAsStringDimmyDataAsStringDimmyTableAsStringDimm

41、yNameAsString,myTypeAsInteger,mySizeAsIntegerDimiAsLong,jAsLongDimCnnStrAsString,SQLAsStringmyData=职工管理myTable=职工基本信息*查询Access数据库WithcnnData.Provider=Microsoft.Jet.OLEDB.4.0.OpenThisWorkbook.Path&myData&.mdbEndWithrsData.OpenmyTable,cnnData,adOpenKeyset,adLockOptimistic*创建SQLServer数据库和数据表*CnnStr=Provider=SQLOLEDB.1;UserID=sa;DataSource=THTFCOMPUTERWithcnnSQL.ConnectionString=CnnStr.OpenEndWith删除同名数据库OnErrorResumeNextSQL=dropdatabase&myDatacnnSQL.ExecuteSQLOnErrorGoTo0创建新数据库SQL=createdatabase&myDatacnnSQL.ExecuteSQLcnnSQL.Close建立与数据库的连接