ExcelVBA与数据库Access.doc

《ExcelVBA与数据库Access.doc》由会员分享，可在线阅读，更多相关《ExcelVBA与数据库Access.doc（217页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流ExcelVBA与数据库Access.精品文档.目录1-1 利用DAO创建数据库和数据表71-2 利用ADOX创建数据库和数据表：81-3 利用SQL语句创建数据库和数据表91-4 在已有的数据库中创建数据表（DAO）101-5 在已有的数据库中创建数据表（ADOX）121-6 在已有的数据库中创建数据表（SQL，Command对象）131-7 在已有的数据库中创建数据表（SQL，Recordset对象）131-8 利用Access对象创建数据库和数据表141-8-1 不引用Access对象库而使用Access的有关对象、属性和方法161-9

2、 利用Access对象在已有的数据库中创建数据表171-10 利用工作表数据创建数据表（ADOX）181-11 利用工作表数据创建数据表（ADO+SQL）211-12 利用工作表数据创建数据表（DAO）231-13 利用已有的数据表创建新数据表（ADO）251-14 利用已有的数据表创建新数据表（DAO）271-15 利用已有的数据表创建新数据表（Access）(前绑定方法引用Access对象库)282-1 检查数据表是否存在（ADO）28实例2-2 检查数据表是否存在（ADOX）32实例2-3 检查数据表是否存在（DAO）33实例2-4 检查数据表是否存在（Access）33实例2-5 获取

3、数据库中所有表的名称和类型（ADO）34实例2-6 获取数据库中所有表的名称和类型（ADOX）35实例2-8 获取数据库中所有数据表名称（ADO）36实例2-9 获取数据库中所有数据表名称（ADOX）37实例2-10 获取数据库中所有数据表名称（DAO）37实例2-11 获取数据库中所有数据表名称（Access）38实例2-12 检查某字段是否存在（ADO）39实例2-13 检查某字段是否存在（ADOX）40实例2-14 检查某字段是否存在（DAO）41实例2-15 检查某字段是否存在（Access）41实例2-16 获取数据库中某数据表的所有字段信息（ADO）42实例2-17 获取数据库中某

4、数据表的所有字段信息（ADOX）44实例2-18 获取数据库中某数据表的所有字段信息（DAO）45实例2-19 获取数据库中某数据表的所有字段信息（Access）47实例2-20 获取数据库的所有查询信息（ADOX）48实例2-21 获取数据库的所有查询信息（DAO）49实例2-22 获取数据库的模式信息（openschema）50实例2-23 获取表的创建日期和最后更新日期（ADOX）51实例2-24 获取表的创建日期和最后更新日期（DAO）52实例3-1 将数据库记录数据全部导入到excel工作表（ADO，之一）53实例3-2 将数据库记录数据全部导入到excel工作表（ADO，之二）54

5、实例3-3 将数据库记录数据全部导入到Excel工作表（ADO,之三）55实例3-4 将数据库记录数据全部导入到Excel工作表（DAO,之一）56实例3-5 将数据库记录数据全部导入到Excel工作表（DAO，之二）57实例3-6 将数据库记录数据全部导入到Excel工作表（QueryTable集合）58实例3-7 将数据库的某些字段的记录数据导入到Excel工作表（ADO）59实例3-8 将数据库的某些字段记录数据导入到Excel工作表(DAO)60实例3-9 查询前面的若干条记录（全部字段）（TOP）61实例3-10 查询前面的若干条记录（部分字段）（TOP）62实例3-11 查询不重复

6、的字段记录（DISTINCT）63实例3-12 利用Like运算符进行模糊查询64实例3-13 查询某一区间内的记录（BETWEEN）65实例3-14 查询存在于某个集合里面的记录（IN）67实例3-15 将查询结果进行排序（ORDER BY）68实例3-16 进行复杂条件的查询（WHERE）69实例3-17 利用合计函数进行查询（查询最大值和最小值）70实例3-18 利用合计函数进行查询(查询合计值和平均值)71实例3-19 将一个查询结果作为查询条件进行查询72实例3-20 将查询结果进行分组（GROUP BY）73实例3-21 查询结果进行分组（HAVING）74实例3-22 通过计算列

7、进行查询76实例3-23 使用IS NULL运算符进行查询77实例3-24 使用COUNT函数进行查询78实例3-25 使用FIRST函数与LAST函数查询第一条记录和最后一条记录的字段78实例3-26 使用Parameters参数动态查询记录（DAO）指定单个参数79实例3-27 使用parameters参数动态查询记录（DAO）：指定多个参数80实例3-28 使用parameters参数动态查询记录（ADO）：指定单个参数81实例3-29 使用Parameters参数动态查询记录（ADO）：指定多个参数83实例3-30 使用别名查询数据库84实例3-31 将查询结果作为窗体控件的源数据85

8、实例3-32 通过窗体控件查询浏览数据库记录88实例3-33 多表查询（WHERE连接）98实例3-34 多表查询（内连接INNER JOINT）99实例3-35 多表查询（左外连接LEFT OUTER JOINT）101实例3-36 多表查询（右外连接 RIGHT OUTER JOINT）102实例3-37 多表查询（子查询WHERE，ANY，SOME）103实例3-38 多表查询（子查询EXISTS，NOT EXISTS）105实例3-39 从两个数据表中查询出都存在的记录106实例3-40 从两个数据表中查询出只存在于某个数据表的记录108实例3-41 将查询结果生成一个数据表108实例

9、3-42 将查询结果保存为一个XML文件109实例3-43 利用工作表实现记录的分页显示110实例3-44 利用窗体实现记录的分页显示113实例4-1 添加新记录（ADO+addnew）116实例4-2 添加新记录（ADO+SQL）117实例4-3 添加新记录（DAO+addnew）118实例4-4 添加新记录（DAO+SQL）118实例4-5 添加新记录（Access+SQL）119实例4-6 修改更新特定记录（ADO+SQL）120实例4-7 修改更新特定记录（DAO+SQL）120实例4-8 修改更新特定记录（Access+SQL）121实例4-9 修改更新全部记录（ADO+SQL）12

10、1实例4-10 修改更新全部记录（DAO+SQL）122实例4-11 修改更新全部记录（Access+SQL）122实例4-12 删除特定记录（ADO+SQL）123实例4-13 删除特定记录（DAO+SQL）124实例4-14 删除特定记录（Acess+SQL）124实例4-15 删除全部记录（ADO+SQL）124实例4-16 删除全部记录（DAO+SQL）125实例4-17 删除全部记录（Access+SQL）125实例4-18 通过窗体编辑记录126实例5-1 将整个工作表数据都保存为新的Access数据库（Access）128实例5-2 将工作表的某些区域数据保存为新Access数据

11、库（Access）129实例5-3 将工作簿的所有工作表数据分别保存为不同的数据表（Access）130实例5-4 将多个工作簿的某个工作表数据汇总为新Access数据库（Access）131实例5-5将多个工作簿的某个工作表数据保存为不同的数据表（Access）132实例5-6 将工作表数据保存到已有的Access数据库（循环方式）（ADO）134实例5-7 将工作表数据保存到已有的Access数据库（循环方式）（DAO）136实例5-8 将工作表数据保存到已有的Access数据库（数组方式）（ADO）138实例5-9 将工作表数据保存到已有的Access数据库（数组方式）（DAO）139实

12、例5-10 将工作簿的所有工作表数据分别保存为不同的数据表（ADO）141实例5-11 将工作簿的所有工作表数据分别保存为不同的数据表（DAO）142实例6-1 打开数据库和数据表（Getobject函数）144实例6-2 打开数据库和数据表（createobject函数）145实例6-3 删除数据表（ADO）145实例6-4 删除数据表（ADOX）146实例6-5 删除数据表（DAO+DELETE）147实例6-6 删除数据表（DAO+SQL）147实例6-7 删除数据表（Access）148实例6-8 为数据表增加字段（ADO）148实例6-9 为数据表增加字段（ADOX）149实例6-1

13、0 为数据表增加字段（DAO）150实例6-11 为数据表增加字段（Access）151实例6-12 删除字段（ADO）152实例6-13 删除字段（ADOX）153实例6-14 删除字段（DAO）154实例6-15 删除字段（Access）155实例6-16 改变字段的类型（ADO）157实例6-17 改变字段的类型（DAO）157实例6-18 改变字段的类型（Access）158实例6-19 改变字段的长度（ADO）158实例6-20 改变字段的长度（DAO）159实例6-21 改变字段的长度（Access）159实例6-22 重命名数据表（Access）159实例6-23 复制数据表（A

14、ccess）160实例6-24 复制数据表（ADO）161实例6-25 复制数据表（DAO）162实例6-26 通过窗体维护数据库162实例7-1 判断SQL Server数据库是否存在（ADO）172实例7-2 检查数据表是否存在（ADOX）173实例7-3 创建新的SQL Server数据库和数据表（ADO）174实例7-4 在已有的SQL Servre数据库中创建数据表（ADO）175实例7-5 从SQL Server数据库服务器中删除数据库（ADO）176实例7-6 从SQL Server数据库中删除数据表（ADO）177实例7-7 将SQL Server数据库中的数据导入到Excel

15、工作表（ADO）177实例7-8 将SQL Server数据库中的数据导入到Excel工作表（DAO）178实例7-9 查询获取SQL Server数据库的数据（ADO）180实例7-10 查询获取SQL Server数据库的数据（DAO）181实例7-11 将工作表数据导入到SQL Server数据库（ADO）182实例7-12 向SQL Server数据库中添加记录的一般方法183实例7-13 将SQL Server数据库转换为Access数据库184实例7-14 将access数据库转换为SQL Server数据库185实例8-1 将FoxPro数据库全部数据导入到Excel工作表187

16、实例8-2 查询获取FoxPro数据库数据188实例8-3 将excel工作表数据保存到FoxPro数据库189实例8-4 判断FoxPro数据库的字段是否存在190实例8-5 获取FoxPro数据库的字段信息190实例9-1 从工作簿的某个工作表中查询获取数据（ADO）192实例9-2 从工作簿的全部工作表中查询获取数据（ADO）193实例9-3 利用DAO从工作表中查询数据194实例9-4 查询其他工作簿的数据（ADO）195实例9-6 利用ADO对工作表数据进行多重排序196实例9-7 利用ADO按照字符的长度对数据进行排序197实例9-8 比较两张表，将两个表中相同的行数据抓取出来19

17、8实例9-9 比较两张表，将只存在于某个表中的行数据抓取出来199实例9-10 删除工作表数据区域内的所有空行200实例10-2 利用DAO导入文本文件的全部内容202实例10-3 利用ADO导入文本文件的部分内容203实例10-4 利用DAO导入文本文件的部分内容204实例10-5 利用ADO获取文本文件的行数和列数205实例10-6 利用ADO将超过65536行的文本文件数据导入到Excel工作表206实例10-7 将工作表全部数据保存为文本文件（SaveAs）208实例10-8 将工作表全部数据保存为文本文件（循环）208实例10-9 将数据库数据导出为文本文件209实例10-10 将文

18、本文件保存为Access数据库（Access）210实例10-11 将文本文件保存为Access数据库（ADO+ADOX）2111-1 利用DAO创建数据库和数据表1. 首先建立对DAO对象库Microsoft DAO3.6 Object Library的引用.在VBA界面下:工具-引用,选中”Microsoft DAO3.6 Object Library”2. 代码:Public Sub1_1()Dim myDb As DAO.Database 定义DAO的Database(数据库)对象变量Dim myTbl As DAO.TableDef 定义DAO的TableDef(数据表)对象变量Di

19、m myData As String 定义数据库名称变量Dim myTable As String 定义数据表名称变量设置要创建的数据库名称（包括完整路径）myData=ThisWorkbook.Path & “学生成绩管理.mdb”设置要创建的数据表名称myTable=”期末成绩”删除已经存在的数据库文件on error resume nextkill myDataon error goto 0创建数据库Set myDb=CreateDatabase(myData,dbLangChineseSimplified)创建数据表Set myTbl=myDb.CreateTableDef(myTab

20、le)为创建的数据表添加各个字段With myTbl .Fields.Append .CreateField(“学号”,dbText,10) .Fields.Append .CreateField(“姓名”,dbText,6).Fields.Append .CreateField(“性别”,dbText,1).Fields.Append .CreateField(“班级”,dbText,10).Fields.Append .CreateField(“数学”,dbSingle).Fields.Append .CreateField(“语文”,dbSingle).Fields.Append .Cr

21、eateField(“物理”,dbSingle).Fields.Append .CreateField(“化学”,dbSingle).Fields.Append .CreateField(“英语”,dbSingle).Fields.Append .CreateField(“总分”,dbSingle)End With将创建的数据表添加到数据库的TableDefs集合中myDb.TableDefs.Append myTbl关闭数据库，并释放变量myDb.CloseSet myDb=NothingSet myTbl=Nothing弹出信息MsgBox”创建数据库成功！” & vbCrLf & “数据

22、库文件名为：” & myData & vbCrLf & “数据表名称为：” & myTable & vbCrLf & “保存位置：” & ThisWorkbook.Path,vbInformation,”创建数据库”End sub注：CreateDatabase方法创建数据库set mydb=createdatabase(mydata,dblangchinesesimplified)mydb:数据库类型变量dblangchinesesimplified：表达字符串比较规则，这里为简体中文CreateTableDef方法创建数据表Set myTbl=mydb.Createtabledef(myt

23、able)mytbl:表类型变量mydb:数据库名mytable:表名补充：创建带密码的Access数据库Set mydb=createdatabase(mydata, dblangchinesesimplified & “;pwd=12345”)1-2 利用ADOX创建数据库和数据表：引用：microsoft ADO Ext.2.X for DDL and Security代码：public sub1_2()dim mycat as new adox.catalog 定义ADOX的Catalog对象变量dim mytbl as new table 定义table对象变量dim mydata

24、as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件on error resume nextkill mydataon error goto 0创建新的数据库mycat.create”provider=microsoft.jet.oledb.4.0;data source=” & mydata创建数据表，并添加字段with mytbl .name=mytabl

25、e .columns.append “学号”,advarwchar,10 .columns.append “姓名”,advarwchar,6.columns.append “性别”,advarwchar,1.columns.append “班级”,advarwchar,10.columns.append “数学”,adSingle.columns.append “语文”,adSingle.columns.append “物理”,adSingle.columns.append “化学”,adSingle.columns.append “英语”,adSingle.columns.append “总

26、分”,adSingleEnd with将创建的数据表添加到ADOX的Tables集合中mycat.tables.append mytbl释放变量set mycat=nothingset mytbl=nothing弹出信息msgbox “创建数据库成功!” & vbcrlf & “数据库文件名为:” & mydata & vbcrlf & “数据表名称为:” & mytable & vbcrlf & “保存位置:” & thisworkbook.path,vbinformation,”创建数据库”end sub注：在VB中，常用的数据访问接口有下列三种：数据库访问对象（DAO，DataAcces

27、s object）、远程数据库对象（RDO,Remote Data Object）和ActiveX数据对象（ADO,ActiveX Data Object）ADOX的常用方法：Append方法：可以创建columns,groups,indexes,keys,procedures,tables,users,views等 为数据表添加字段： mytbl.columns.append 字段名，数据类型，字段长度 将创建的数据表添加到ADOX的Tables集合中的语句是： Mycat.tables.append mytblCreate方法：创建一个新的数据库的语句： Mycat.create “pro

28、vider=Microsoft.jet.oledb.4.0;data source=” & mydataDelete方法：删除数据表： Mycat.tables.delete 数据表名Refresh方法：用于更新集合中的对象1-3 利用SQL语句创建数据库和数据表首先引用：microsoft activeX data objects 2.X library 和 microsoft ado ext.2.x for ddl and security”代码：public sub1_3()dim mycat as new adox.catalog 定义ADOX的Catalog对象变量Dim mycmd

29、 as new mand 定义Command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim SQL as string设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件on error resume nextkill mydataon error goto 0创建数据库文件mycat.create “provider=microsoft.jet.oledb.4.0;D

30、ata source=” & mydata设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句 SQL = CREATE TABLE & myTable _ & (学号 text(10),姓名 text(6),性别 text(1),班级 text(10), _ & 数学 Single,语文 Single,物理 Single,化学 Single, _ & 英语 Single,总分 Single)利用execute方法创建数据表with mycmd .commandtext=sql .execute, , adc

31、mdtextend with释放变量set mycat=nothingset mycmd=nothing弹出信息 msgbox “创建数据库成功！” & vbcrlf & “数据库文件名为：” & mydata & vbcrlf & “数据表名称为：” & mytable & vbcrlf & “保存位置：” & thisworkbook.path,vbinformation,”创建数据库”end sub注：有两种方法来创建数据表： 利用ADODB.Command对象的commandtext属性和execute方法：dim mycmd as new mandset mycmd.activeco

32、nnection=mycat.activeconnectionwith mycmd .commandtext=SQL .execute, , adcmdtextend with 利用ADODB.Connection对象的execute方法来生成几个记录集Dim cnn as new adodb.connectiondim rs as new adodb.recordsetset cnn=mycat.activeconnectionset rs=cnn.execute(sql)1-4 在已有的数据库中创建数据表（DAO）引用DAO对象库：microsoft DAO 3.6 object libr

33、ary代码：public sub1_4()dim mydb as dao.database 定义DAO的database(数据库)对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”打开数据库set mydb=opendatabase(mydata)删除数据库中已经存在的数据表mydb.tabledefs.delete mytable创建新的数据表set myt

34、bl=mydb.createtabledef(mytable)为创建的数据表添加各个字段With mytbl .fields.append .createfield(学号, dbtext, 10) .fields.append .createfield(姓名, dbtext, 6) .fields.append .createfield(性别, dbtext, 1) .fields.append .createfield(班级, dbtext, 10) .fields.append .createfield(数学, dbsingle) .fields.append .createfield(语

35、文, dbsingle) .fields.append .createfield(物理, dbsingle) .fields.append .createfield(化学, dbsingle) .fields.append .createfield(英语, dbsingle) .fields.append .createfield(总分, dbsingle)end with将创建的数据表添加到数据库的TableDefs集合中mydb.tabledefs.append mytbl关闭数据库mydb.close释放变量set mydb=nothingset mytbl=nothing弹出信息msg

36、box”数据表创建成功！”,vbinformation,”创建数据表”end sub补充：opendatabase方法用来打开一个已有的数据库，返回一个数据库对象，并自动将该数据库对象加入到数据库对象集中。set database=workspace.opendatabase(databasename,options,read-only,connect)workspace:定义的Workspace类型变量，它表示所使用的工作环境，将包含新的数据库对象databasename:一个有效的Jet数据库文件或ODBC数据源options:T/F,T表示以独占方式打开数据库，而F表示以共享方式打开数据

37、库read-only:是否以只读方式打开数据库，为T/Fconnect:说明不同连接方式以及密码扩展：利用DAO打开有密码的Access数据库set mydb=opendatabase(mydata,true,false,”;pwd=12345”)1-5 在已有的数据库中创建数据表（ADOX） 引用：microsoft ADO Ext.2.x for ddl and security 代码：public sub1_5()Dim mycat as new adox.catalog 定义ADOX的catalog对象变量dim mytbl as new table 定义table对象变量dim my

38、data as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_ & “data source=” & mydata删除数据库中已经存在的数据表mycat.table.delete mytable创建数据表，并添加字段with mytbl .name=myta

39、ble .columns.append “学号”, advarwchar,10 .columns.append “姓名”, advarwchar,6 .columns.append “性别”, advarwchar,1 .columns.append “班级”, advarwchar,10 .columns.append “数学”, adsingle .columns.append “语文”, adsingle .columns.append “物理”, adsingle .columns.append “化学”, adsingle .columns.append “英语”, adsingle

40、 .columns.append “总分”, adsingleend with将创建的数据表添加到ADOX的tables集合中mycat.tables.append mytbl释放变量set mycat=nothingset mytbl=nothing弹出信息msgbox “数据表创建成功！”,vbinformation,”创建数据表”end sub注：Activeconnection属性用来指示catalog所属的ADO Connection对象，表示到数据源的打开的连接。1-6 在已有的数据库中创建数据表（SQL，Command对象） 引用:microsoft activex data o

41、bjects2.x library和microsoft ado ext.2.x for ddl and security 代码：public sub1_6()dim mycat as new adox.catalog 定义adox的catalog对象变量dim mycmd as new mand 定义command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string 设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建

42、的数据表名称mytable=”期末成绩”建立与数据库的连接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_ & “data source=” & mydata删除数据库中已经存在的数据表mycat.tables.delete mytable设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句SQL=”CREATE TABLE” & mytable_ & “(学号 text(10),姓名 text(6),性别 text(1),班级 text(

43、10),” _ & “数学 single,语文 single,物理 single,化学 single, “_ & “英语 single,总分 single)”利用Execute方法创建数据表With mycmd .commandtext=sql .execute, , adcmdtextEnd with释放变量Set mycat=nothingSet mycmd=nothing弹出信息Msgbox “数据表创建成功！”,vbinformation,”创建数据表”End sub 1-7 在已有的数据库中创建数据表（SQL，Recordset对象） 引用：Microsoft ActiveX Data Objects 2.X library 代码：public sub1_7()dim cnn as new adodb.connection 定义connection对象变量dim rs as new adodb.recordset 定义Recordset对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string设置数据库名称(包括完整路径)mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数