Access VBA数据库编程(ADO) 详细.ppt

第7章VBA数据库编程 本章内容 记录集概述在Access中引用ADO对象引用记录字段浏览记录编辑数据用ADO技术实现复杂查询在VBA程序中使用SQL命令访问当前数据库以外的数据库综合实例 编制 研究生成绩管理与统计 程序 7 1记录集概述 7 1 1ADO的9个对象7 1 2了解记录集 7 1 1ADO的9个对象 ADO ActiveXDataObjectsAccess内嵌的VBA是用ADO技术开发数据库应用的主要工具ADO对象模型有9个对象 Connection Recordset Record Command Parameter Field Property Stream Error常用对象 Connection Command Recordset 7 1 1ADO的9个对象 Connection对象 ADO对象模型中最高级的对象 实现应用程序与数据源的连接 Command对象 主要作用是在VBA中通过SQL语句访问 查询数据库中的数据 Recordset对象 存储访问表和查询对象返回的记录 使用该对象 可以浏览记录 修改记录 添加新的记录或者删除特定的记录 Recordset对象的功能最常用 最重要 7 1 1ADO的9个对象 ADO的3个对象之间互有联系 Command对象和Recordset对象依赖于Connection对象的连接 Command对象结合SQL命令可以取代Recordset对象 但远没有Recordset对象灵活 实用 Recordset对象它只能实现数据表内记录集操作 无法完成表和数据库的数据定义操作 数据定义操作一般需通过Command对象用SQL命令完成 本章用DoCmd对象代替Command对象 7 1 2了解记录集 记录集 Recordset 对表执行查询操作时 返回的一组特定记录 用记录集可执行的操作 对表中的数据进行查询和统计 在表中添加 更新或删除记录 记录集是一个对象 它包括记录和字段 具有其特定的属性和方法 利用这些属性和方法就可以编程处理数据库中的记录 7 2在Access中引用ADO对象 应用程序中的ADO引用 声明Connection对象 创建Recordset对象 编程完成各种数据访问操作7 2 1声明Connection对象7 2 2声明与打开Recordset对象7 2 3关闭Recordset和Connection对象 7 2 1声明Connection对象 1 声明一个Connection对象 2 初始化Connection对象 决定Connection对象与哪个数据库相连接 例如 DimcnGraduateAsADODB ConnectionSetcnGraduate CurrentProject Connection 7 2 2声明与打开Recordset对象 1 声明Recordset对象 2 创建Recordset对象实例 3 打开Recordset对象例如 DimrsStudentsAsADODB RecordsetSetrsStudents NewADODB RecordsetrsStudents Open 研究生 cnGraduate adCmdTable 7 2 3关闭Recordset和Connection对象 方法 执行Recordset对象和Connection对象的Close方法 将对象设置为Nothing例如 rsStudents ClosecnGraduate CloseSetrsStudents NothingSetcnGraduate Nothing上述语句不是必须的 应用程序终止运行时 系统会自动关闭并清除这两个对象 7 3引用记录字段 任何对记录集的访问都是针对当前记录进行的 打开记录集时默认的当前记录为第1条记录 引用记录的字段 直接在记录集对象中引用字段名称 如Code rsStudents 学号若记录集字段名包含空格 或者字段名是一个保留字 则引用时必须将该字段名用方括号括起来 使用记录集对象的Fields n 属性 n是记录中字段从左到右的排列序号 第一个字段的序号为0 如Code rsStudents Fields 0 记录集对象与字段名间的连接符 7 3引用记录字段 例7 1 建立名为ADO的模块 编写如下过程 运行该过程后 输出对话框显示 导师 表中第一位教师的编号和姓名 7 3引用记录字段 记录集更多的应用是在窗体对象上 建立一个空白窗体 设计各个控件 编程引用记录集当前记录的相关字段或将字段的值通过控件显示注意 如果涉及数据访问的事件过程不止一个 可在代码窗口的通用段定义Connection对象和Recordset对象 然后在Form Load事件过程中完成数据库连接和数据表的打开 7 3引用记录字段 例7 2 运行 分别单击 导师编号 和 导师姓名 按钮 窗体设计视图 删除导航按钮 7 3引用记录字段 例7 2窗体程序代码 7 4浏览记录 Recordset记录集对象提供了4种记录指针的移动方法MoveFirst记录指针移到第一条记录MoveNext记录指针移到当前记录的下一条记录MovePrevious记录指针移到当前记录的上一条记录MoveLast记录指针移到最后一条记录Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置记录指针将指向最后一条记录之后 EOF属性为True记录指针将指向第一条记录之前 BOF属性为TrueBOF和EOF属性的值均为True 表示记录集为空 7 4浏览记录 例7 3 对例7 2进行修改 增加如下事件过程代码 PrivateSubCommand3 Click 单击 下一个记录 按钮rsTeacher MoveNextEndSub 7 4浏览记录 上面的过程隐含错误 移至最后一条记录后无法再下移指针 将出现运行错误信息框 两种纠正错误的方法 如果记录集的EOF属性为True 就回到第一条记录如果记录集的EOF属性为True 就回到最后一条记录 PrivateSubCommand3 Click 单击 下一个记录 按钮rsTeacher MoveNextEndSub 7 4浏览记录 7 4浏览记录 方法2的程序还将运行出错 因为Recordset对象有一个名为LockType的属性 默认值为adLockReadOnly 此时只能浏览记录数据 记录的移动方式也只能是MoveNext和MoveFirst 要实现记录指针的4种移动操作 完成记录的添加 删改或更新 必须在使用Open方法打开数据表之前 将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic 如rsTeacher LockType adLockPessimisticrsTeacher Open 导师 cnGraduate adCmdTable 7 4浏览记录 Recordset对象的LocyType属性决定数据的锁定方式 adLockReadOnly 数据处于只读状态 adLockPessimistic 保守式锁定 编辑数据时即锁定数据源记录 直到数据编辑完成才释放 adLockOptimistic 开放式锁定 编辑数据时不锁定 用Update方法提交数据时才锁定数据源记录 adLockBatchOptimistic 开放式更新 应用于批更新模式 7 4浏览记录 如果数据表中没有记录 即BOF和EOF同时为True 就不能引用表中的数据 将单击 导师姓名 按钮的事件过程改写如下PrivateSubCommand2 Click IfrsTeacher BOF TrueAndrsTeacher EOF TrueThenText1 Value ElseText1 Value rsTeacher 姓名EndIfEndSub同理 修改单击 导师编号 按钮的事件过程同理 修改 下一个记录 按钮的事件过程代码 空表情况下记录指针不移动 7 5编辑数据 7 5 1用ADO记录集的AddNew方法添加记录7 5 2用ADO记录集的Update方法修改记录7 5 3用ADO记录集的Delete方法删除记录 7 5 1用ADO记录集的AddNew方法添加记录 添加新记录的步骤 1 用记录集的AddNew方法产生一个空记录 2 为空记录的各个字段赋值 3 用记录集的Update方法保存新记录 例7 4 在例7 3的基础上进行修改 7 5 1用ADO记录集的AddNew方法添加记录 添加的程序代码 PrivateSubCommand4 Click DimAgeAsBytersTeacher MoveFirstAge rsTeacher 年龄 读取第一条记录的年龄字段值rsTeacher AddNew 添加一条新记录rsTeacher 导师编号 107 rsTeacher 姓名 高原 rsTeacher 年龄 AgersTeacher UpdateEndSub 思考 什么情况下单击 新记录 按钮 系统会提示出错 7 5 2用ADO记录集的Update方法修改记录 修改记录的步骤 1 将记录指针移动到需要修改的记录上 2 对记录中的相关字段的值进行修改 3 用Update方法保存更改修改后的结果不得违反数据完整性约束 例7 5 在模块ADO中添加过程Update Age 将 导师 表中第5条记录的年龄字段值修改为60岁 注意设置记录集的LockType类型 7 5 2用ADO记录集的Update方法修改记录 SubUpdate Age DimIAsByteDimcnGraduateAsADODB ConnectionSetcnGraduate CurrentProject ConnectionDimrsTeacherAsADODB RecordsetSetrsTeacher NewADODB RecordsetrsTeacher LockType adLockPessimisticrsTeacher Open 导师 cnGraduate adCmdTable 向下跳过4条记录 将记录指针指向第5条记录ForI 1To4rsTeacher MoveNextNextIrsTeacher 年龄 60 rsTeacher UpdateEndSub 7 5 2用ADO记录集的Update方法修改记录 例7 6 在例7 4的基础上进行修改添加的程序代码 PrivateSubCommand5 Click rsTeacher MoveFirstDoWhileNotrsTeacher EOFIfrsTeacher 性别 男 ThenrsTeacher 年龄 rsTeacher 年龄 1rsTeacher UpdateEndIfrsTeacher MoveNextLoopEndSub 7 5 3用ADO记录集的Delete方法删除记录 删除记录要慎重 因为被删记录无法恢复删除记录的步骤 1 移动到需要删除的记录上 2 用记录集对象的Delete方法删除当前记录 3 将某条记录指定为当前记录删除记录后 Access不能自动使下一条记录成为当前记录 根据不同情况 可用MoveNext或MoveLast方法定位记录指针 7 5 3用ADO记录集的Delete方法删除记录 例7 7 对例7 6添加如下的单击窗体事件过程 7 5 3用ADO记录集的Delete方法删除记录 IfrsTeacher 姓名 高原 ThenFlag MsgBox 是否要删除高原 vbYesNo 删除确认 IfFlag vbYesThen 确定删除rsTeacher DeleteMsgBox 记录删除完毕 rsTeacher MoveNext 删除记录后设定新的当前记录IfrsTeacher EOFThenrsTeacher MoveLastExitSubElseIfFlag vbNoThen 不删除记录MsgBox 放弃删除操作 删除确认 ExitSubEndIfEndIf 如果找到 高原 进行相关操作 然后退出过程 7 6用ADO技术实现复杂查询 例7 8 在ADO模块中建立Sex过程 统计并在输出对话框中显示男 女研究生人数比 要求以人数多的一方为1 放在右侧 保留小数两位 程序分析 先遍历全部记录 分别统计出男 女研究生人数 如果男生人数多于女生 则男生人数为1 女生人数改为女生人数除以男生人数 反之亦然 7 6用ADO技术实现复杂查询 SubSex 定义对象 完成与数据库的连接 打开研究生表 定义变量 Student MoveFirstDoWhileNotStudent EOF 统计男 女研究生人数IfStudent 性别 男 ThenBoy Boy 1IfStudent 性别 女 ThenGirl Girl 1Student MoveNextLoopIfGirl BoyThen 以男生人数为1MsgBox 女 男 Format Girl Boy 0 00 1 Else 以女生人数为1MsgBox 男 女 Format Boy Girl 0 00 1 EndIfEndSub 7 6用ADO技术实现复杂查询 例7 9 略 例7 10 7 6用ADO技术实现复杂查询 PrivateSubForm Load MSFlexGrid1 Rows 1MSFlexGrid1 Cols 8 以下为MsFlexGrid添加列标题MSFlexGrid1 Row 0 MSFlexGrid1 Col 1 选定0行1列MSFlexGrid1 Value 学号 MSFlexGrid1 Col 2 MSFlexGrid1 Value 姓名 MSFlexGrid1 Col 3 MSFlexGrid1 Value 性别 MSFlexGrid1 Col 4 MSFlexGrid1 Value 入学日期 MSFlexGrid1 Col 5 MSFlexGrid1 Value 入学分数 MSFlexGrid1 Col 6 MSFlexGrid1 Value 研究方向 MSFlexGrid1 Col 7 MSFlexGrid1 Value 导师编号 EndSub 7 6用ADO技术实现复杂查询 PrivateSubCommand1 Click 单击 生成 按钮 定义变量 定义对象 完成与数据库的连接 打开研究生表 Student MoveFirstDoWhileNotStudent EOFForI 2ToStudent 入学分数 1IfStudent 入学分数ModI 0ThenExitFor 不是质数NextIIfI Student 入学分数Then 如果入学分数为质数S ForI 0To6 将当前记录各个字段连成一行S S Student Fields I Chr 9 NextIMSFlexGrid1 AddItem vbTab S 跳过每行左侧的标题栏EndIfStudent MoveNextLoopEndSub 7 7在VBA程序中使用SQL命令 Access提供了DoCmd对象 该对象的RunSQL方法可以在VBA程序中用SQL命令直接对数据源进行操作 RunSQL方法的格式为 DoCmd RunSQL也可以DimAsString DoCmd RunSQL是一对由双引号括起来的SQL命令 7 7在VBA程序中使用SQL命令 7 7 1定义数据7 7 2编辑数据7 7 3实现数据完整性约束7 7 4执行查询操作 7 7 1定义数据 1 创建数据表CREATETABLE 字段名数据类型 NULL NOTNULL 字段名AS计算表达式 n 例7 11 在SQL模块中建立如下过程 7 7 1定义数据 2 在表中增加字段 例7 12 为Student表增加一个货币型的字段 学费 3 改变字段的类型 例7 13 修改Student表中 年龄 字段的类型 SubAdd Field DoCmd RunSQL ALTERTABLEStudentADD学费CURRENCY EndSub 7 7 1定义数据 4 改变字段的宽度 例7 14 更改Student表中 姓名 字段的宽度5 删除一个字段 例7 15 删除Student表中的 年龄 字段 SubDelete Field DoCmd RunSQL ALTERTABLEStudentDROP年龄 EndSub 7 7 1定义数据 6 删除一个表 例7 16 删除Student表7 修改数据表名字 例7 17 将Student表的名字更改成 学生 SubRename Table DoCmd Rename 学生 acTable Student EndSub SubDelete Table DoCmd RunSQL DROPTABLEStudent EndSub 7 7 2编辑数据 1 向表中追加记录 例7 18 在Student表中添加一条记录 SubInsert Table DoCmd RunSQL INSERTINTOStudentVALUES 李大明 35 2003 1 15 EndSub VALUES后的数据与表中字段的顺序要一一对应 说明 1 日期型常量可以放在一对单引号或一对 中 2 如果数据是通过输入对话框等途径获得 需在SQL命令中使用变量 然后用 运算符将变量连接到SQL命令中 对于字符串变量或日期型变量 要在这些变量的两侧加上一对单引号 7 7 2编辑数据 例7 19 通过变量为Student表添加一条记录 SubInsert Table VBA DimS nameAsStringDimAgeAsByte S dateAsDateS name InputBox 输入学生姓名 S date InputBox 入学日期 Age 21DoCmd RunSQL INSERTINTOStudentVALUES S name Age S date EndSub 注意单引号和双引号出现的位置 7 7 2编辑数据 2 修改表中记录 例7 20 将 导师 表中 李向明 的年龄改成40 例7 21 将 导师 表中所有男导师的年龄增加1岁 SubUpdate Table 1 DoCmd RunSQL UPDATE导师SET年龄 40WHERE姓名 李向明 EndSub 如果本例用ADO技术编程 代码会怎样 SubUpdate Table 2 DoCmd RunSQL UPDATE导师SET年龄 年龄 1WHERE性别 男 EndSub 7 7 2编辑数据 3 删除特定记录 例7 22 将 导师 表中年龄在50岁以下的记录全部删除 SubDelete Record DoCmd RunSQL DELETEFROM导师WHERE年龄 50 EndSub 思考 1 如果要求将 导师 表中低于年龄平均值的导师记录删除 例7 22应怎样修改 2 如果要删除小于X岁的所有记录 X的值通过键盘在程序运行时输入 程序又该怎样修改 7 7 3实现数据完整性约束 1 设置主键 例7 23 为 导师 表和 研究生 表设置主键 如果这两个表已有主键和外键 请先予以删除 例7 24 在创建新表Teacher的同时设定code为主键 SubCreate Primary DoCmd RunSQL AlterTable导师AddPrimaryKey 导师编号 DoCmd RunSQL AlterTable研究生AddPrimaryKey 学号 EndSub SubCreate Table Primary DoCmd RunSQL CREATETABLETeacher codetext 3 PRIMARYKEY nametext 6 birthdaydate salarycurrency EndSub 7 7 3实现数据完整性约束 2 设置外键 例7 25 将研究生表中的 导师编号 设为外键 对应的参照表是 导师 表 建立 导师 表和 研究生 表间的关系 例7 26 在创建新表Student1的同时指定其外键和参照表 SubCreate Foreign DoCmd RunSQL AlterTable研究生AddForeignKey 导师编号 References导师 EndSub 7 7 4执行查询操作 VBA程序中用SQL命令完成的数据查询操作 无法直接将查询结果所返回的记录集按数据表形式显示 解决方法 方法1 将查询形成的记录集生成一个新表保存到数据库中 然后用ADO记录集对象打开这个表进行各种操作 完成后删除这个表 方法2 将返回的记录集看成是保存在内存中的一个临时表 用ADO记录集对象直接打开该临时表进行各种操作 7 7 4执行查询操作 例7 27 7 7 4执行查询操作 例7 28 SubQuery2 DimSQLAsStringSQL SELECTt 姓名as导师姓名 s 姓名as学生姓名FROM导师t 研究生sWHEREt 导师编号 s 导师编号andt 性别 男 定义对象 连接数据库 设置记录集LockType属性 打开temp表 rsTeacher OpenSQL cnGraduate 打开查询记录集DoWhileNotrsTeacher EOFDebug PrintrsTeacher 导师姓名 rsTeacher 学生姓名rsTeacher MoveNextLoopEndSub t s分别作为 导师 表和 研究生 表的别名 例7 29 不使用SQL命令实现上题的功能 略 7 8访问当前数据库以外的数据库 连接另一个数据库 使用Connection对象的Open方法 语法格式为 Open Provider Da