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

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(可选)：指定ODBC

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

24、e=基本信息建立与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.Connection

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

27、tcnn=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,SQLAsStringDimwbAsWorkbookDimwsAsWorks

29、heetSetwb=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).RowFo

30、ri=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,cnn,adO

31、penKeyset,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数据保存完毕！,vbInformation,

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

33、ver数据库服务器的连接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=inser

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

35、mcnnSQLAsNewADODB.ConnectionDimrsSQLAsNewADODB.RecordsetDimcnnDataAsNewADODB.ConnectionDimrsDataAsNewADODB.RecordsetDimmyCatAsNewADOX.CatalogDimmyTblAsNewADOX.TableDimmySQLAsStringDimmyDataAsStringDimmyTableAsStringDimmyNameAsString,myTypeAsInteger,mySizeAsIntegerDimiAsLong,jAsLong*查询获取SQLServer数据库服

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

37、ss数据库表*创建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=200ThenmyType=adVarWCharIfrsSQL.Fields(i).Type=135ThenmyType=adDatemySize=rsSQL.Fields(i).DefinedSize.Columns.AppendmyName,myType,mySizeNextiEndWithmyCat.Tables.AppendmyTbl向access数据表中添加数据