VB：VB(Visual Basic)程序设计教案
MySQL：MySQL数据库(安装配置 语句语法使用 项目中操作MySQL)
Access数据库需要会员才可以用？: 提取码: amjm
参考视频：Access2016数据库零基础计算机二级（17小时） 、Excel+VBA+Access数据库编程（30小时）
Access数据库应用、进销存系统项目
需要安装备用软件：点击我获取 提取码: 6zm3
一：Access基础知识
1.前言
1.1 基本流程
表 第一步：需求分析确定功能（实现这些表包括哪些实体及属性 他们的关系如何） 第二步：建excel表（录入数据） 第三步：把数据表导入到access数据库里面（外部数据——>Excel——>浏览） 第四步：设计视图（数据类型 字段大小 数据库工具_关系） 查询表 第一种：创建——>查询——>查询设计——>选择需要的添加表——>双击选择到下面字段栏去——>保持—>给查询表取名 第二种：创建——>查询——>查询向导——>简单查询向导——>选择表——>字段——>给查询表取名 窗体：最好设置好关系 样式：弹出方式、 记录选择器、导航按钮、控制框 命令按钮向导：记录导航、记录操作、窗体操作、报表操作、应用程序、杂项 报表：展示数据和打印 第一种：查询表——>报表 第二种：报表向导——>选择查询——>选定字段 宏：通常利用宏作为主窗口 第一种：事件——>单击三个点点——>选择生成器——>宏生成器 第二种：创建——>宏(命名为autoexec) openform打开窗体、closewindow关闭窗体、openquery查询、messagebox提示、openreport打开报表、quitaccess退出数据库...
1.2 基本概念
数据处理的进化史 人工纸质：效率低下、管理麻烦 文件记录：无法处理大量数据存在大量冗余信息 数据库系统：可以存储大量数据、方便关联数据处理 适用版本：2010、2013、2016 什么是Access数据库？ Access是微软发布的图形用户界面形式的关系数据库管理系统 数据库：就是存储数据的仓库 Access意思：访问，存取 用途？ Access的用途体现在两个方面：数据分析、开发软件 关系数据库：彼此之间相互关联，可以解决数据的冗余问题 数据库相关概念 数据(Data) :数字、文字、图像、音频、视频等 数据库 (DataBase) :按特定结构存储数据的文件 数据库管理系统(DBMS) : 对数据进行统一管理，方便数据共享，处理，维护 数据库应用系统 (DataBase Application System，DAS)：基于数据库可以做不同的应用系统/软件 数据库管理员(DataBase Administrator， DBA) Access六种对象 ---------------------------------------------------- 表：保存数据 查询：从表中提取数据，查询结果可作为窗体和报表的数据源 窗体：数据库与用户之间的界面，用于数据输入和显示 报表：自设计格式，展示数据和打印 宏：非编程方式实现任务自动化 VBA模块：编程方式实现更复杂的自动化功能 ---------------------------------------------------- Access工作界面 标题栏：快速访问工具栏（鼠标右键可以自定义指定添加删除） 功能区：里面有很多选项卡，选项卡下面有很多命令(命令组) 选项卡（隐藏选项/卡上下文选项卡）——>命令组——>命令 数据库三范式：设计数据库的基本概念；建立冗余较小结果合理的数据库；看需求和性能(需求>性能>表结构) 第一范式（1NF）：列不可再分 两列的属性相近或一样，尽量合并属性一样的列 第二范式（2NF）：属性完全依赖于主键 每个实例或行必须可以被唯一区分标识 第三范式（3NF）：属性不依赖于其它非主键属性 应该拆开成两张表
2.使用步骤方法
2.1 表【设计】
2.1.1 表的理论基础
表中概念：字段(纵向的)、记录(横向的) 表（关系）：用于存储信息，表现某一类事物 表结构（关系模型）：由小标题字段组成 行：记录/元组/具体的某个事物 列标题：字段/事物属性 值：交叉的小格子/每一个方格数据 域：值的限定范围 主键（主关键字）：可以定位到某一条具体的数据、非空、不能重复 方便和其他的表进行关联：因为不会重复、可以定位到一条具体的数据 外键（外部关键字）：在另外一张表中与”主键“相互产生关联 数据表视图：创建好之后展示效果 设计视图：设计的内容 数据库设计 1.需求分析：比如“咨询信息管理系统” 2.表设计：有几张表 3.字段设计 设置关键字字段 每个字段都有存在意义 有意义的拆分字段 多表不要存在相同字段 4.表间关系与设计 一对一（合并表）：员工与身份证信息 一对多：部门与员工 多对多（中间表）：课程与学生
2.1.2 Access建库建表
创建Access数据库：（后缀名为accdb） 开始——>空白数据库——>修改存放位置 命名——>确定——>创建 它内含一些现成的模板 创建表结构 创建表——>字段名称 数据类型 字段属性——>建立表间关系 详细操作 a 创建表 第一种方式（直接创建表）：打开access数据库——>创建——>表 第二种方式（从excel导入）：打开access数据库——>外部数据——>Excel——>浏览——>选择打开 b 保存：给这个表取名 c 字段名称：自定义（Tab/方向键切换） 长度：小于64个字符 组成：包含汉族、字母、数字、空格 注意：不要以空格开头、不要包含！ # %等特殊字符 d 数据类型：视图/选中表右键——>设计视图——>鼠标右键保存 短文本：字母数字字符 0-255个文字 长文本：字母数字字符 1G数据 备注：字母数字字符 0-65536个字符 数字：整型、长整型、单精度型、双精度型 视具体类型来看 日期/时间：斜杠分割 8字节 货币：钱 8字节 自动编号：自动设置编号 4字节 是/否：勾选选择状态 1位（0/-1） OLE对象：图像照片、图形、声音、视频 最高为1GB （鼠标右键——>插入对象——>由文件创建——>浏览） 超链接：邮箱、网址 0-64000个字符 附件：文档（鼠标右键/双击——>管理附件——>添加） 可依次添加多个 取决于附件 计算：选表——>选字段——>数学表达式——>确定 查阅向导：提供可选值，或引用另一个表的数据 通常为4字节 自行键入所需的值（下拉选择）：设置行数列数——>限于列表（男/女） 使用查阅字段获取其他表或查询中的值（可以关联表）：选表——>选字段——>下一步——>排序对象选择——>下一步——>完成 > 一个一个添加到右边 >> 全部添加到 右边 < << e 说明（可选）：解释说明 f 设置主键：选中字段——>点击功能区主键（会显示出一个钥匙） 选择需要设置的最前面——>鼠标右键——>点击主键 g 字段属性：不同数据类型字段属性不一样 大小：数字（255）、类型（字节 整型 长整型 单精度型 双精度型）... 格式：货币、常规数字、长中短日期、>自动设置大写、<自动设置小写、百分比... 标题：字段名称改变（ID） 默认值：可以给字段设置默认值（=Date()函数） 验证规则：>=3000、<=Date() 验证文本：错误的提示内容（会弹出窗口进行提示） 必须：有些字段必须要有值 索引：有大量数据要进行查询搜索，从而提高效率（上面有索引按钮） 输入掩码：展示相应的格式引导我们录入数据、限定我们输入的内容（避免录入错误数据） ———————————————————————————————————————————————————— 占位符 设置显示的符号 0 数字（必须），不允许+和- 9 数字或空格（可选），不允许+和- # 数字或空格（可选），允许+和- L 字母（必须） ？ 字母或空格（可选） A 字母或数字（必须） a 字母、数字或空格（可选） & 任意字符或空格（必须） C 任意字符或空格（可选） . , : ; / 小数点、千分位、日期时间分隔符 <,> 小写，大写 \ 原样显示 手动输入：0000\-0000\-0000 ———————————————————————————————————————————————————— h 录入数据 i 建立表间关系：数据类型必须一致才可以 第一步：数据库工具——>关系 第二步：鼠标左键可拖动选择——>添加——>关闭 第三步：鼠标单击选中字段——>拖动到另外字段相同的关系表中——>单击创建（会有线条关联、表前面的“+”加号可以查看信息关系） 实施参照完整性：明确主键和外键之间的关系 级联更新相关字段：修改一个信息，相关的数据也会跟着改变 级联删除相关记录：删除一个信息，相关的数据也会跟着被删除 关系报告：鼠标右键保存之后在左侧会出现一个报表 编辑关系：修改关系 清除布局：清除掉关系数据 隐藏表、直接关系、所有关系：会依次显示执行
2.1.3 表的基本操作
1.修改表结构：视图——>设计视图 2.录入表数据 Tab键（从左往右） Shift建（从右往左） 空格键（选择） Ctrl+'（引用上方数据） Ctrl+Shift+;（当前时间） Ctrl+;/（当前日期） 3.数据导入导出 外部数据选项卡 ——>导出（类型）——>导出到哪里 导出数据时包含格式和布局 完成导出操作后打开目标文件 仅导出所选记录 ——>导入并链接（类型）——>浏览文件——>选择存储方式和位置 ——>将源数据带入当前数据库的新表中：没表就创建表；有表就提取数据覆盖数据 ——>向表中追加一份记录的副本（建议使用）：没表就创建表；有表就提取数据追加数据 ——>通过创建链接表来链接到数据源：Excell数据变化，Access里面的数据也会跟着变化；反之不行 4.编辑表数据：选择、复制、粘贴、新建、查找、替换... 查找通配符 —————————————————————————————————————————————————————————————————————————————— * 任意多个字符 （王* 姓王、*王* 包含王） ？ 任意单个字符 （王？ 姓王，名字两个字） [] 括号内任意单个字符 （[王李]？ 姓王或李，名字两个字） ！ 不在括号内的字符 （![王李]？ 不姓王或李，名字两个字） - 范围内的任意一个字符 （[a-j]bd abd,cbd,jbd） # 单个数字 （5#1 501,502,503） —————————————————————————————————————————————————————————————————————————————— 5.调整表格式：字体、大小、背景色、颜色、位置、网格线、移动、宽高、显示隐藏、子数据表... 6.记录排序：升序、降序 7.筛选记录：筛选器（返回就点击“切换筛选”）、选择 最下方可以点击“已/未筛选” 筛选器——>单击想要赛选的方格——>选择（等于 不等于 包含 不包含 小于等于 大于等于 介于...） 属性栏右侧器——>点击小三角器——>赛选器（更加精密的设置筛选） 筛选器器——>高级器——>按窗体筛选器——>应用筛选排序 ——>高级筛选排序——>设置——>应用筛选排序 8.汇总数据功能 记录——>合计（最后一行有“汇总”可以进行操作） 9.查询表 9.1 创建——>查询——>查询设计——>选择需要的添加表——>双击选择到下面字段栏去——>保持—>给查询表取名 创建——>查询——>查询向导——>简单查询向导——>选择表——>字段——>给查询表取名 选择查询：设计——>运行（排序 显示 条件） 用户手动输入查询 [这里面写提示信息] 保存（不要直接不错不然会覆盖之前的）：单击文件——>另存为——>对象另存为——>点击另存为——>取名——>确定 避免笛卡儿积现象：加入关联表 设置查询条件（可以和通配符搭配使用） ———————————————————————————————————————————————————————————————— 算术运算符：+、-、*、/、\(整除)、^(求幂)、Mod(取模) 比较运算符：=(等于)、<、>、<>(不等于)、<=、>= 逻辑运算符：And(与)、Or(或)、Not(非)、Between...And、In(多项) 字符串/连接运算符：& 模糊查询：Like "王*" 空值判断：Is Null、Is Not Null 函数应用：Year([入职日期])=2024 ———————————————————————————————————————————————————————————————— 生成表查询：能够把查询结果的数据放到一个新的表中（自命名） 追加查询：允许多次设置查询条件，把结果保存到某一个表中 更新查询：更新数据 交叉表查询：比如“行”“列”筛选得到想要的“值” 删除查询：设置删除的条件 聚合/汇总查询：总计里面设计（选择where可以设置条件） 可能涉及条件嵌套：[引用查询名称]![查询的字段] 参数查询：[]对话框 [请输入员工姓名] >=[请输入最低工资] and <=[请输入最高工资] 带计算的查询：加减乘除 操作查询 更新查询：更新数据源表中某些数据 更新——>更新到 删除查询：删除数据表中某些数据 删除——>条件 生成表查询：利用数据源表里的数据生成一个新的数据表 生成表——>表名称 追加查询：将数据源表中的数据追加到另一个表中 追加——>表名称 SQL查询：看2.2 SQL视图 9.2 查询向导——>查询——>查询向导 简单查询向导：选择表——>字段——>给查询取名 交叉表查询向导：count first last max min 查询重复项查询向导：重复的值 查询不匹配项查询向导：A表在B表中是否有 9.3 左右连接 左连接：包含左边的全部，另外的必须相互关联才显示 右连接：包含右边的全部，另外的必须相互关联才显示 10.数据库的加密方法 方法一：创建密码窗体_窗体属性设置 窗体——>设计视图——>属性表——>快捷菜单——>否（密码页右键单击不会出现任何东西） ——>弹出方式——>是 ——>模式——>是 ——>导航按钮——>否 ——>记录选择器——>否 ——>控制框——>否 ——>添加文本框——>属性表——>输入掩码——>密码 ——>按钮——>下一步——>文本——>事件——>最右边三个点——>添加If——>绑定文本框——>[text1]="123456" ——>Messagebox提示信息 ——>Openform打开窗体主页 ——>CloseWindow关闭当前密码窗体 ——>鼠标右键单击导航窗口——>隐藏 ——>鼠标右键——>导航选项——>取消勾选“显示隐藏对象”——>确定 ——>宏——>命名为autoexec——>messagebox提醒——>openform打开密码页 方法二：数据库加密 文件——>点击打开——>这台电脑——>找到你的access数据库文件——>以独占方式打开 ——>点击信息——>用密码进行加密——>设置数据库密码——>确定
2.2 SQL语句代码【设计】
SQL数据库教案
开始——>视图——>SQL视图
创建——>查询设计——>关闭显示表——>点击最左侧的“SQL视图”——>书写SQL语句——>单击红色感叹号“运行”
Access、MySQL、SQLServer、Oracle、SqlLite都是关系型数据库 SQL结构化查询语言:Structured Query Language 结构化查询语言，关系数据库常用语言 例如:SELECT*FROM 员工: 功能:数据定义、数据查询、数据操作、数据控制
查询类型：设计——>联合 传递 数据定义
UNION联合查询：合并两个表中的数据，生成新表 SELECT 姓名,性别,手机号 FROM 员工 UNION SELECT 姓名,性别,手机号 FROM 客户; 传递查询：传递SOL语句到远程数据库服务器执行 数据定义：启动查询窗口，让我们能够输入一个创建表的sql语句
2.3 窗体【交互】
2.3.1 多方式创建窗体
1.创建——>窗体——>窗体
2.创建——>窗体——>其他窗体——>数据表窗体
设计——>视图——>窗体视图
窗体:用户与数据库之间的交互界面，方便管理数据库 作用:方便用户查看、输入、更新、删除数据 四种窗体视图 窗体视图:查看窗体效果，可处理数据 布局视图:可查看数据，可更改窗体设计 设计视图:无法查看数据，可更改窗体设计，设计功能更强大 数据表视图:表格形式显示数据 窗体操作技巧 前进：tab 向下方向键 后退：shift + tab 向上方向键 选中：f2 记录切换：导航条 开始：新建、保存、删除、筛选、查找、排序3.创建——>窗体——>窗体向导
窗体操作技巧 选择多个：鼠标左键 + ctrl键4.创建——>窗体——>其他窗体——>多个项目
5.创建——>窗体——>其他窗体——>分割窗体
6.创建——>窗体——>其他窗体——>模式对话框
7.创建——>窗体——>空白窗体
8.创建——>窗体——>窗体设计
基础操作 属性表打开：左上角交汇处小方块双击、设计——>属性表 A4纸张：长29.7 x 宽21 连续选择：shift键 + 鼠标左键 多个选择：ctrl键 + 鼠标左键 剪切：ctrl + x 复制：ctrl + v 控件操作 选择：单个（鼠标单击）、多个相邻（鼠标拖动）、多个不相邻（Ctrl+鼠标左键）、所有（ctrl+a） 移动：鼠标单击左上角小黑块 然后拖动、可以配合键盘的方向键移动 宽高 删除：选中 按键盘上的del 对齐：功能栏——>对齐（对齐、大小空格） 格式：功能栏——>格式（字体、数字、背景、控件格式） 设计：文本框、标签、图片（附件的方式可以显示）、主题、颜色、字体... 添加计算公式字段：设计——>属性表——>控件来源——>表达式生成器 =IIf(Year([入职日期])<2019,[工资]*1.5.[工资]*1.2) 设置tab顺序：tab键移动 设计——>Tab键次序——>鼠标左键拖动顺序 不许进行编辑：设计——>属性表——>其他——>制表位（否） 窗体页眉与页脚：设计视图——>设计——>鼠标右键（窗体页眉与页脚 页面页面与页脚） 窗体页眉：窗体标题；日期时间 页面页眉：按钮；只是打印的时候才会显示出来（每一页都显示） 主体：显示数据 页面页脚：按钮；只是打印的时候才会显示出来（每一页都显示） 窗体页脚：注脚信息9.创建导航窗体——>窗体——>导航（水平、垂左、垂右、水平2、水平垂直左、水平垂直右）
2.3.2 窗体常用的控件
设计——>控件（先打开使用控件向导）
组合框：把信息通过下拉列表一 一显示出来 使用组合框获取其他表或查询中的值 自行键入所需的值：自己写入信息选择 在基于组合框中选定的值而创建的窗体上查询记录：基于当前表 列表框：把所有的信息一次性展示出来 选项组 图像 按钮 选项卡：分空间存储 插入分页符 直线/矩形/超链接 子窗口/子报表
2.3.3 设置启动窗体
单击文件——>选项——>当前数据库——>标题取名 设置显示窗体——>确定
如何隐藏左侧的导航窗格？ 单击文件——>选项——>当前数据库——>取消勾选“显示导航窗格”——>确定 如何显示左侧的导航窗格？ 按键盘的f11
2.3.4 参数查询窗体
第一种：可以不停输入查询
第一步【主窗体】：创建空白窗体——>添加文本框（记住文本框名字） 第二步【查询】：创建条件查询（以刚才文本框名称为条件） 选择表为数据源——>条件——>鼠标右键单击——>生成器——>找到刚刚的窗体——>找到文本双击双击 第三步【绑定】：将主窗体记录源设置为刚才创建的查询 窗体——>设计视图——>属性表——>数据——>数据源——>选择刚刚的查询数据 第四步【查询记录 子窗体】：以创建好的查询为记录源创建一个窗体——>作为子窗体 点击主窗体——>点击“子窗体/子报表”——>使用现有的窗体——>无（若有绑定关联）——>下一步——>完成 第五步【合并窗体】：在查询窗体中插入子窗体——>添加一个“刷新窗体数据”的按钮 按钮——>窗体操作——>刷新窗体数据——>文本 弹出方式——>是 记录选择器、导航按钮、控制框——>否第二种：只能查询一次
第一步：先创建一个参数查询——>查询设计——>添加字段——>设置条件 第二步：以刚刚创建的查询为数据源创建一个窗体——>点击它——>创建——>窗体
2.4 报表【汇报】
2.4.1 创建报表方式
创建报表 第一种：查询表——>报表 第二种：报表向导——>选择查询——>选定字段 报表（展示数据）：将表或者是查询中的内容，按照特定的格式组织起来，然后用于显示 报表视图：默认的 打印预览：预览效果 布局视图：进行调整 设计视图：详细设计 报表设计：设计视图 报表页眉（只显示一次）：标题；图片；日期时间 页面页眉：放置报表字段的名称属性 主体：相关数据 页眉页脚：放置针对每一页的数据；每一页都显示的内容(页码) 报表页脚（只显示一次）：汇总的数据 ="总共"&Count(*) & "条数据" 空报表 报表向导
2.4.2 报表分组与统计
设计——>分组和排序（添加组 添加排序）
函数：Count技术、Max最大、Min最小、Sum求和、Avg平均值
2.5 宏【绑定调用函数】
宏：非编程方式实现操作自动化（双击/选择调用现成函数方法）;通常利用宏作为主窗口 第一种：事件——>单击三个点点——>选择生成器——>宏生成器 第二种：创建——>宏(命名为autoexec) openform打开窗体、closewindow关闭窗体、openquery查询、messagebox提示、openreport打开报表、quitaccess退出数据库... 创建——>宏与代码——>单击“宏” ——>显示隐藏——>操作目录——>程序流程(Comment、Group、 If、Submacro) ——>操作(窗口管理、宏命令、筛选查询搜索、导入导出、数据库对象、数据输入、系统命令、用户界面命令) ——>在此数据库中(目前数据库中有那些对象包含了宏相关的操作) ——>显示所有操作（标记可能存在安全性问题的一些操作） ——>折叠展开 宏安全性设置：文件——>选项——>信任中心——>信任中心设置——>宏设置——>“禁用所有宏并发出通知” 流程控制 Comment：注释/解释说明 Group：分组 把窗口信息操作进行分组展示 If：分支的流程 创建——>窗体设计——>按钮——>类别（杂项）——>运行宏——>下一步——>选择比如（IF程序流程）——>文本——>下一步——>取名字给这个按钮 Submacro：子宏 绑定了子宏 = 触动了SelectFrame操作 宏分类 独立宏（Stand-Alone Macros）：没有和任何的窗口、表、报表产生任何关联 嵌入宏（Embeded Macros）：由access的对象或者是控件它的事件触发执行的宏 属性表——>事件——>每栏右边的三个点——>宏生成器——>确定——>设置操作 嵌入宏特点：在导航窗口中是不可见的 数据宏（Data Macros）：表——>前期事件 后期事件 针对表中数据，如果做了一些操作，就会触发宏执行(数据宏/也是嵌入宏的一种) 宏的临时变量 属性表——>事件——>每栏右边的三个点——>宏生成器——>宏命令（SetTempVar）保存到变量中 记录源——> =[TempVars]![部门] 解决占用内存问题——>宏命令（RemoveTempVar）清除变量释放内存 ——>筛选查询搜索——>Requery(重新进行查询) 绑定对应的报表的名称来进行实时显示 autoexec宏 创建——>宏——>保存宏（取名autoexec）注意名字不能取错了——>其他操作（比如打开某个窗口）
二：Access高阶知识-VBA
VB：VB(Visual Basic)程序设计教案
Excel前端 + Access后端 + VBA作为Excel的内置编程语言
1.VBA连接Access数据库
通过VBA方式：能够让Access自动化的帮助我们去实现一些工作任务；有利于扩展office应用程序的功能
方法一：Access宏连接
单击创建——>宏与代码——>Visual Basic（快捷键Alt + F11）——>书写代码
方法二：Access事件代码生成器连接
设计——>事件（比如按钮）——>属性表——>事件——>单击（右边小三点）——>代码生成器——>确定——>书写代码
方法三：VBA代码连接
基本原理：Excel数据库xlsm（SQL命令）——>ADO工具（连接数据库connection 获取数据recordset 命令command）——>Access数据库（后缀accdb） 版本问题：2010以下是8.0版本、2010以上是12.0版本
step1：通过什么方式打开VBA
a.如果是excel去打开VBA
页面展示“开发工具”选项卡：打开excel——>点击选项——>自定义功能区——>常用命令——>勾选上开发工具——>确定 进入excel主页面——>开发工具——>Visual Basic——>插入——>模块——>编辑代码** b.如果是wps去打开VBA **
安装wpsvba宏文件 找到需要打开的excel表格——>（如有提示就 启动宏）——>工具——>开发工具——>VB编辑器——>插入——>模块——>编辑代码**c.如果是access去打开VBA **
打开Access软件——>新建“空白数据库”——>命名 设置保存路径——>点击创建——>点击数据库工具——>Visual Basic——>Microsoft Visual Basic for Applications——>插入——>模块——>编辑代码
step2：连接何种类型文件
a 数据库access文件
通过excel——>打开vba——>代码连接access文件
逐步
Sub 测试数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.声明对象变量：给连接对象取名字 Dim con As ADODB.Connection '3.创建对象变量：创建对象变量并赋值 Set con = New ADODB.Connection '4.连接access数据库 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" '5.测试是否连接成功（如果打开成功对应路径下会出现后缀为"laccdb"的文件） MsgBox "数据库链接成功" End Sub合并
'Option Explicit Option Compare Database Sub 连接数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.创建连接对象 Dim con As New ADODB.Connection '3.建立数据库的连接 With con .Provider = "microsoft.ace.oledb.12.0" .ConnectionString = ThisWorkbook.Path & "\学生管理.accdb" .Open End With '4.测试是否连接成功（如果打开成功对应路径下会出现后缀为"laccdb"的文件） MsgBox "连接成功" End Sub
b excel表格xlsx文件
通过excel——>打开vba——>代码连接xlsx
逐步
Sub 链接EXCEL数据() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.声明对象变量：给连接对象取名字 Dim con As ADODB.Connection '3.创建对象变量：创建对象变量并赋值 Set con = New ADODB.Connection '4.建立数据库的连接 con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0; data source=" & _ ThisWorkbook.Path & "\测试.xlsx" '5.测试是否连接成功 MsgBox "数据库链接成功" End Sub合并
Option Explicit 'Option Compare Database Sub 连接数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.创建连接对象 Dim con As New ADODB.Connection '3.建立数据库的连接 With con .Provider = "microsoft.ace.oledb.12.0;extended properties=excel 12.0" .ConnectionString = ThisWorkbook.Path & "\测试.xlsx" .Open End With '4.测试是否连接成功 MsgBox "连接成功" End Sub
c 基本推论步骤如下
书写代码：打开VBA——>插入/工程——>添加模块 Option Explicit 'Option Compare Database Sub 连接数据库() '第一步：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '第二步：创建连接对象 'Dim con As New ADODB.Connection ’声明对象变量：给连接对象取名字 Dim con As ADODB.Connection '创建对象变量：创建对象变量并赋值 Set con = New ADODB.Connection '第三步：建立数据库的连接 '连接access数据库 '第一种写法 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" '第二种写法 With con .Provider="microsoft.ace.oledb.12.0" .ConnectionString=ThisWorkbook.Path & "\学生管理.accdb" .Open end With '连接excel数据库 con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & _ thisworkbook.Path & "\数据.xlsx" '第四步：测试是否连接成功（如果打开成功对应路径下会出现后缀为"laccdb"的文件） msgbox "连接成功" End Sub
2.基于Access通过VBA利用SQL语句进行数据操作
SQL和VBA都不区分大小写
2.1 插入/增加数据 insert
Sub 测试数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.定义、创建连接变量 Dim con As New ADODB.Connection '3.建立连接access数据库 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" '4.插入记录：insert into 表名(列1,列2,...) Values(值1,值2,...) '4.1:书写SQL命令 Dim sql As String sql="insert into 院系(系号,系名,电话) Values('A07','人文学院','999')" 'Dim sql As String 'sql="insert into 院系 Values('A07','人文学院','999')" '4.2:执行SQL命令 con.Execute(sql) '5:释放变量空间 '5.1:关闭连接 con.Close '5.2:释放变量 Set con=Nothing End Sub
2.2 删除数据 delete
Sub 测试数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.定义、创建连接变量 Dim con As New ADODB.Connection '3.建立连接access数据库 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" '4.删除记录：delete from 表名 [where 条件] '4.1:书写SQL命令 Dim sql As String sql="delete from 院系 where 系号='A11'" 'sql="delete from 院系 where 系名='明文学院'" 'sql="delete from 院系 '4.2:执行SQL命令 con.Execute(sql) '5:释放变量空间 '5.1:关闭连接 con.Close '5.2:释放变量 Set con=Nothing End Sub
2.3 修改/更新数据 update
Sub 测试数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.定义、创建连接变量 Dim con As New ADODB.Connection '3.建立连接access数据库 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" '4.更新记录：update 表名 set 字段=值 [where 条件] '4.1:书写SQL命令 Dim sql As String sql="update 院系 set 电话='9999' where 系名='明文学院'" 'sql="update 学生 set 班级='1班',研究方向='会计' where 性别='男'" '4.2:执行SQL命令 con.Execute(sql) '------------------------------------------------------------------------------- '在SQL语句中使用变量 Dim str As String '用于保存用户输入的条件 str=inputbox("请输入性别：") sql="update 学生 set 班级='2班' where 性别='" & str & "'" '------------------------------------------------------------------------------- '5:释放变量空间 '5.1:关闭连接 con.Close '5.2:释放变量 Set con=Nothing End Sub
2.4 查询数据 select
**Select语句的执行顺序：from-->where-->group-->having-->select **
2.4.1 简单查询
Sub 测试数据库() '1：告诉电脑我们要用ADO（引用ADO工具） '工程——>引用——>Microsoft ActiveX Data Objects 6.1 Library——>确定 '2.定义、创建连接变量 Dim con As New ADODB.Connection '3.建立连接access数据库 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 Dim sql As String sql="select * from 院系" '4.2:创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset '4.3:执行SQL命令：提取到的数据会被加载到内存中 Set rs = con.Execute(sql) '4.4:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next '4.5:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5:释放对象变量空间 '5.1:关闭连接释放rs变量 rs.Close:Set rs = Nothing '5.2:关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.2 其他子句
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 sql="select * from 院系" '查询全部字段 ’sql="select 姓名,性别,职称 from 导师" '指定部分字段 ’sql="select distinct 研究方向 from 学生" '提取不重复字段 ’sql="select 课程代码,成绩 from 成绩 order by 成绩 asc/不写" ’排序-降序：desc ’sql="select *,year(入学日期) from 学生" '生成新的字段 ’sql="select *,2024 from 学生" ’sql="select *,year(入学日期) as 年份 from 学生" '取别名 ’sql="select 姓名,性别 from 学生 union select 姓名,性别 from 导师" 'union all不会去重、不排序 ’sql="select 姓名,性别,'学生' as 身份 from 学生 union select 姓名,性别,'老师' as 身份 from 导师" '4.2:执行SQL命令：提取到的数据会被加载到内存中 Set rs = con.Execute(sql) '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next '4.4:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放rs变量 rs.Close:Set rs = Nothing '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.3 条件查询
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 '= <> > < 'sql="select * from 学生 where 性别='男'" 'sql="select * from 学生 where 性别<>'男'" '列表查询in not in 'sql="select * from 学生 where 研究方向 in('风险投资','项目投资')" '介于查询between 'sql="select * from 成绩 where 成绩 between 80 and 90" '空值查询Null Not Null 'sql="select * from 成绩 where is null" '字符连接& 'sql="select 学号&姓名 as 学号姓名,性别,班级 from 学生" 'sql="select * from 学生 where 性别='女' and 班级='1班'" 'sql="select * from 学生 where 性别&班级='女1班'" '4.2:执行SQL命令：提取到的数据会被加载到内存中 Set rs = con.Execute(sql) '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next '4.4:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放rs变量 rs.Close:Set rs = Nothing '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.4 模糊查询
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 ' % 相当于* ，任意多个字符 (非)始于、(非)止于、(不)包含 not ! ' _ 相当于？，任意单个字符 限定文本个数 ' [] 字符组 [A-Z]、[9-9]、[1-龢]、[!...] 'sql="select * from 员工 where 姓名 like '李%'" 'sql="select * from 员工 where 姓名 like '%丽'" 'sql="select * from 员工 where 简历 like '%组织能力强%'" 'sql="select * from 员工 where 姓名 like '张_'" 'sql="select * from 员工 where 电子邮件 like '[!h-m]%'" 'sql="select * from 员工 where 户籍&工作地 like '%北京%'" 'sql="select * from 员工 where 姓名 like '[张王李刘]%'" '4.2:执行SQL命令：提取到的数据会被加载到内存中 Set rs = con.Execute(sql) '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next '4.4:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放rs变量 rs.Close:Set rs = Nothing '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.5 分组计算查询
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 '普通字段不能与聚合函数同时放在一起，因为记录条数不对应 '普通字段如果与聚合函数同时出现在select后面，那么普通字段要么聚合、要么分组 ’聚合函数（sum、avg、max、min、count） 'sql="slect avg(年龄) as 平均年龄,max(年龄) as 最大年龄 from 员工” '分组统计（group by） 'sql="slect 部门,avg(年龄) as 平均年龄from 员工 group by 部门” '小组筛选（having）分组之后进行筛选 'sql="slect 部门,avg(年龄) as 平均年龄from 员工 group by 部门 having avg(年龄)>35” '4.2:执行SQL命令：提取到的数据会被加载到内存中 Set rs = con.Execute(sql) '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next '4.4:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放rs变量 rs.Close:Set rs = Nothing '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.6 生成表查询
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 '将查询结果生产一个新表 'sql="select * into 优秀 from 成绩 where 成绩>=90" '将查询结果追加到已有的表 'sql="insert into 优秀 select * from 成绩 where 成绩 between 85 and 89" '4.2:执行SQL命令：提取到的数据会被加载到内存中 con.Execute(sql) '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
产生记录集的两种方式比较（execute方法-open方法）
提取到的数据会被加载到内存中
Option Explicit '定义、创建连接变量 Dim con As New ADODB.Connection '创建记录集对象（第一行/字段名称、其他行/记录） Dim rs As New ADODB.Recordset Sub 测试数据库() '1.调用自定义过程连接数据库 Call 连接数据库 '2.定义命令字符串变量 Dim sql As String '3.调用清除工作表数据 Call 清除工作表数据() '4.查询记录：select 字段列表 from 表名 [where 条件] '4.1:书写SQL命令 sql="select * from 学生 where 班级='3班'" '4.2:执行SQL命令：提取到的数据会被加载到内存中——记录集 '第一种：使用connection对象的execute方法产生记录集 '获得的记录集是只读的，不能修改记录，通常用于做一些没有返回的操作 '不能获取记录的条数 BOF表示记录的开头、EOF表示记录的结尾 Set rs = con.Execute(sql) '---------------------------------------------------------- if rs.EOF and rs.BOF then '指针即既指向开头，又指向结尾，说明没有记录 msgbox "没有满足条件的记录" else '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next end if '---------------------------------------------------------- '第二种：使用recordset对象的open方法产生记录集 '获得的记录集是可读可写，可以修改记录 '可以获取记录的条数 rs.Open sql,con,adOpenKeyset,adLockOptimistic MsgBox rs.RecordCount '表示记录条数，即行数 '---------------------------------------------------------- if rs.RecordCount<=0 then msgbox "没有满足条件的记录" else '4.3:获取字段名：使用循环（字段编号从0开始）【第一行标题A1】 Dim i As Integer For i=0 to rs.Fields.Count-1 'rs.Fields(i).Name 'cells用于访问excel表格对应行列的单元格 Cells(1,i+1)=rs.Fields(i).Name Next end if '---------------------------------------------------------- '4.4:将记录集rs中的数据返回到工作表(表名 开始的位置)【第二行开始】 sheels("演示").range("A2").CopyFromRecordset rs '5.调用释放对象变量空间 call 释放变量空间 End Sub '-------------------------------------------------------------------- Sub 连接数据库() con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb" End Sub Sub 清除工作表数据() Cells.Clear End Sub Sub 释放变量空间() '关闭连接释放rs变量 rs.Close:Set rs = Nothing '关闭连接释放con变量 con.Close:Set con = Nothing End Sub
2.4.7 多表查询
多表查询（Where连接）
Option Explicit '等值连接：将多表中“连接字段”相等共有的所有记录作为查询的来源 Sub 查询() Dim con As New ADODB.Connection '声明并创建连接对象 Dim rs As New ADODB.Recordset '声明并创建记录集对象 '建立数据库连接 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" Dim sql As String '定义命令字符串变量 '----------------------------------------------------------------本讲知识点：多表查询（Where连接） 'sql = "select * from 学生,课程,成绩 " _ & "where 学生.学号=成绩.学号 " 'sql = "select * from 学生,课程,成绩 " _ & "where 课程.课程代码=成绩.课程代码" 'sql = "select * from 学生,课程,成绩 " _ & "where 学生.学号=成绩.学号 and 课程.课程代码=成绩.课程代码" 'sql = "select 学生.学号,姓名,性别,课程.课程代码,课程名称,成绩" _ & " from 学生,课程,成绩" _ & " where 学生.学号=成绩.学号 and 课程.课程代码=成绩.课程代码" '例1：查询所有学生的姓名、性别、选修的课程名称及成绩 'sql = "select 姓名,性别,课程名称,成绩" _ & " from 学生,课程,成绩" _ & " where 学生.学号=成绩.学号 and 课程.课程代码=成绩.课程代码" '例2：查询“王维欣”同学的性别、选修的课程名称及考试成绩 'sql = "select 姓名,性别,课程名称,成绩" _ & " from 学生,课程,成绩" _ & " where 学生.学号=成绩.学号 and 课程.课程代码=成绩.课程代码" _ & " and 姓名='王维欣'" '例3：查询所有课程的平均成绩，结果包含课程名称、平均成绩2个字段【给工作表取名字，前面可以加上as】 sql = "select 课程名称,avg(成绩) as 平均成绩 " _ & "from 课程 t1,成绩 t2 where t1.课程代码=t2.课程代码 group by 课程名称" '----------------------------------------------------------------本讲知识点：多表查询（Where连接） Set rs = con.Execute(sql) '执行SQL命令，产生记录集 Cells.Clear '清空工作表原有数据 '循环输出记录集字段名称 Dim i As Integer For i = 0 To rs.Fields.Count - 1 Cells(1, i + 1) = rs.Fields(i).Name Next Range("A2").CopyFromRecordset rs '将记录集中的记录返回到工作表 Columns.AutoFit '调整单元格列宽为“最适合列宽” '释放变量空间 rs.Close: Set rs = Nothing con.Close: Set con = Nothing End Sub
多表查询（内连接）
Option Explicit Sub 查询() Dim con As New ADODB.Connection '声明并创建连接对象 Dim rs As New ADODB.Recordset '声明并创建记录集对象 '建立数据库连接 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" Dim sql As String '定义命令字符串变量 '----------------------------------------------------------------本讲知识点：多表查询（内连接） '例：查询所有课程的平均成绩，结果包含课程名称、平均成绩2个字段 'sql = "select 课程名称 as 课程,avg(成绩) as 平均成绩 " _ & "from 课程 inner join 成绩 on 课程.课程代码=成绩.课程代码 " _ '"from 课程,成绩 where 课程.课程代码=成绩.课程代码 " _ & "group by 课程名称 having avg(成绩)>=85" 'Select语句的执行顺序：from-->where-->group-->having-->select sql = "select 课程名称 as 课程,avg(成绩) as 平均成绩 " _ & "from 课程 inner join 成绩 on 课程.课程代码=成绩.课程代码 " _ & "where 成绩>80 group by 课程名称 " 'sql = "select 课程名称 as 课程,avg(成绩) as 平均成绩 " _ & "from 课程 inner join 成绩 on 课程.课程代码=成绩.课程代码 " _ & "where 课程<>'投资学' group by 课程名称 " '----------------------------------------------------------------本讲知识点：多表查询（内连接） Set rs = con.Execute(sql) '执行SQL命令，产生记录集 Cells.Clear '清空工作表原有数据 '循环输出记录集字段名称 Dim i As Integer For i = 0 To rs.Fields.Count - 1 Cells(1, i + 1) = rs.Fields(i).Name Next Range("A2").CopyFromRecordset rs '将记录集中的记录返回到工作表 Columns.AutoFit '调整单元格列宽为“最适合列宽” '释放变量空间 rs.Close: Set rs = Nothing con.Close: Set con = Nothing End Sub
多表查询（外连接）
Option Explicit Sub 查询() Dim con As New ADODB.Connection '声明并创建连接对象 Dim rs As New ADODB.Recordset '声明并创建记录集对象 '建立数据库连接 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" Dim sql As String '定义命令字符串变量 '----------------------------------------------------------------本讲知识点：多表查询（外连接） '基本格式:from 左表 连接类型 右表 on 连接条件 '左连接：左表连接字段有的，而右表没有的，左表全部显示，右表留空 '例1：查询所有导师的院系信息，包含姓名、性别、职称、系号、系名 'sql = "select 姓名,性别,职称,系号,系名 " _ & "from 导师 left join 院系 on 导师.院系编号=院系.系号" '右连接：右表连接字段有的，而左表没有的，右表全部显示，左表留空 '例2：查询所有院系的导师信息，包含系号、系名、姓名、职称 'sql = "select 系号,系名,院系编号,姓名,职称 " _ & "from 导师 right join 院系 on 导师.院系编号=院系.系号" '全连接：Excel不支持全连接，其他标准数据库支持全连接 等效与先把2个表左连接，整体再跟第3张表右连接。 '例3：查询所有导师、所有院系的信息，包含姓名、性别、职称、系号、系名 'sql = "select 姓名,性别,职称,院系编号,系号,系名 " _ & "from 导师 full join 院系 on 导师.院系编号=院系.系号" '----------------------------------------------------------------本讲知识点：多表查询（外连接） Set rs = con.Execute(sql) '执行SQL命令，产生记录集 Cells.Clear '清空工作表原有数据 '循环输出记录集字段名称 Dim i As Integer For i = 0 To rs.Fields.Count - 1 Cells(1, i + 1) = rs.Fields(i).Name Next Range("A2").CopyFromRecordset rs '将记录集中的记录返回到工作表 Columns.AutoFit '调整单元格列宽为“最适合列宽” '释放变量空间 rs.Close: Set rs = Nothing con.Close: Set con = Nothing End Sub
多表查询（自连接）
Option Explicit Sub 查询() Dim con As New ADODB.Connection '声明并创建连接对象 Dim rs As New ADODB.Recordset '声明并创建记录集对象 '建立数据库连接 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" Dim sql As String '定义命令字符串变量 '----------------------------------------------------------------本讲知识点：多表查询（自连接） '含义：自己连接自己 '例：查询员工表中姓名有重复的员工记录 '刷出全部记录看看 'sql = "select * from 员工 t1 inner join 员工 t2 on t1.姓名=t2.姓名" sql = "select distinct t1.编号,t1.姓名,t1.身份证号,t1.部门 " _ & "from 员工 t1 inner join 员工 t2 " _ & "on t1.姓名=t2.姓名 where t1.编号<>t2.编号 order by t1.姓名" '----------------------------------------------------------------本讲知识点：多表查询（自连接） Set rs = con.Execute(sql) '执行SQL命令，产生记录集 Cells.Clear '清空工作表原有数据 '循环输出记录集字段名称 Dim i As Integer For i = 0 To rs.Fields.Count - 1 Cells(1, i + 1) = rs.Fields(i).Name Next Range("A2").CopyFromRecordset rs '将记录集中的记录返回到工作表 Columns.AutoFit '调整单元格列宽为“最适合列宽” '释放变量空间 rs.Close: Set rs = Nothing con.Close: Set con = Nothing End Sub
多表查询（子查询）
Option Explicit Sub 查询() Dim con As New ADODB.Connection '声明并创建连接对象 Dim rs As New ADODB.Recordset '声明并创建记录集对象 '建立数据库连接 con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _ ThisWorkbook.Path & "\学生管理.accdb" Dim sql As String '定义命令字符串变量 '----------------------------------------------------------------本讲知识点：子查询（也叫嵌套查询） '注意： '1、子查询必须用小括号括起来 '2、在一个select语句中，子查询必定会首先执行 '用法1：将子查询做-数据源 '例1：查询每个部门年龄在30以上的员工的人数 'sql = "select 部门,count(*) as 人数 from 员工 where 年龄>=30 group by 部门" 'sql = "select 部门,count(*) as 人数 from (select * from 员工 where 年龄>=30) group by 部门" 'sql = "select * from (select 部门,count(*) as 人数 from 员工 group by 部门) order by 人数" '用法2：将子查询做-条件 '注意： '1、当子查询的结果只有1个值的时候，where条件后面可以使用= < > <> '2、当子查询的结果有多个值的时候，where条件必须用in not in '3、子查询只能有1个字段 '例2：查询年龄高于平均年龄的员工信息，包含姓名、身份证号、部门、年龄、职务 'sql = "select 姓名,性别,部门,年龄,职务 from 员工 where 年龄>(select avg(年龄) from 员工)" 'sql = "select 部门,avg(年龄) from 员工 group by 部门" '例3：查询年龄排在第5-10名的员工信息，包含姓名、身份证号、部门、年龄、职务 sql = "select top 6 姓名,性别,部门,年龄,职务 from 员工 where 年龄 not in(select top 4 年龄 from 员工 order by 年龄 desc) order by 年龄 desc" '----------------------------------------------------------------本讲知识点：子查询（也叫嵌套查询） Set rs = con.Execute(sql) '执行SQL命令，产生记录集 Cells.Clear '清空工作表原有数据 '循环输出记录集字段名称 Dim i As Integer For i = 0 To rs.Fields.Count - 1 Cells(1, i + 1) = rs.Fields(i).Name Next Range("A2").CopyFromRecordset rs '将记录集中的记录返回到工作表 Columns.AutoFit '调整单元格列宽为“最适合列宽” '释放变量空间 rs.Close: Set rs = Nothing con.Close: Set con = Nothing End Sub
3.数据库操作
3.1 判断数据库是否存在&新建数据库&连接数据库
Option Explicit '工具——>引用——>Microsoft ActiveX Data Objects 6.1 Library '工具——>引用——>Microsoft ADO Ext 6.0 For DDL and Security Sub 判断并创建数据库和表() Dim cnn As New ADODB.Connection '连接操作数据库 Dim myCat As New ADOX.Catalog '对数据库的创建删除 Dim mydata As String '记录路径 Dim sql As String 'sql命令字符串 mydata = ThisWorkbook.Path & "\成绩管理.accdb" '指定数据库名称 '数据库是否存在-利用Dir函数可以判断某个文件是否存在：dir(文件完整路径) '如果文件存在，那么会返回该文件的文件名 '如果文件不存在，则返回空值 'MsgBox Dir(mydata) If Len(Dir(mydata)) > 0 Then MsgBox "数据库已经存在。" Kill mydata '删除文件 End If '创建数据库 myCat.Create "provider=microsoft.ace.oledb.12.0;data source=" & mydata '连接数据库 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open mydata End With '创建数据表的SQL命令 'create table 表名(字段1 类型(宽度) 约束条件,字段2 类型(宽度) 约束条件,....) sql = "create table 期中成绩(学号 text(10) not null," _ & "姓名 text(8) not null,性别 text(1) not null," _ & "班级 text(10) not null,语文 single not null," _ & "数学 single not null,英语 single not null," _ & "物理 single not null,化学 single not null," _ & "生物 single not null,总分 single not null)" cnn.Execute sql MsgBox "数据库创建成功!", vbInformation, "创建数据库" cnn.Close Set cnn = Nothing Set myCat = Nothing End Sub
3.2 在已有的数据库中创建表
两种方式 ：利用Command对象创建表、利用SQL语句创建数据表
Option Explicit Sub 利用Command对象创建表() On Error Resume Next Dim myCmd As New ADODB.Command Dim myCat As New ADOX.Catalog Dim mydata As String Dim myTable As String Dim sql As String mydata = ThisWorkbook.Path & "\成绩管理.accdb" '指定数据库名称 myTable = "期末成绩" '建立数据库连接 myCat.ActiveConnection = "provider=microsoft.ace.oledb.12.0;" & "data source=" & mydata '删除已经存在的同名数据表 myCat.Tables.Delete myTable '设置数据库连接：方便接下来创建数据表 Set myCmd.ActiveConnection = myCat.ActiveConnection '设置创建数据表的SQL语句 sql = "create table 期末成绩(学号 text(10) not null," _ & "姓名 text(8) not null,性别 text(1) not null," _ & "班级 text(10) not null,语文 single not null," _ & "数学 single not null,英语 single not null," _ & "物理 single not null,化学 single not null," _ & "生物 single not null,总分 single not null)" '利用Command对象的Execute方法执行命令 With myCmd .CommandText = sql '连接命令字符串 .Execute , , adCmdText '表示执行一个文本字符串命令 End With MsgBox "数据表创建成功!", vbInformation, "创建数据表" Set myCmd = Nothing Set myCat = Nothing End Sub Sub 利用SQL语句创建数据表() On Error Resume Next Dim cnn As New ADODB.Connection Dim mydata As String Dim myTable As String Dim sql As String mydata = ThisWorkbook.Path & "\成绩管理.accdb" '指定数据库名称 myTable = "期末成绩" '建立数据库连接 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open mydata End With '删除已有的同名数据表：drop table 表名 sql = "drop table " & myTable cnn.Execute sql '到目前，我们学过的SQL命令 '数据定义功能： ' 创建表：create table ... ' 删除表：drop table 表名 '数据操纵功能： ' 删除记录（delete）、更新记录（update）、插入记录（insert） ' 查询记录（select） '当前学过的可以执行SQL命令的对象： 'connection.execute、recordset.open、command '设置创建数据表的SQL语句 sql = "create table 期末成绩(学号 text(10) not null," _ & "姓名 text(8) not null,性别 text(1) not null," _ & "班级 text(10) not null,语文 single not null," _ & "数学 single not null,英语 single not null," _ & "物理 single not null,化学 single not null," _ & "生物 single not null,总分 single not null)" '利用connection对象的Execute方法执行命令 cnn.Execute sql cnn.Close Set cnn = Nothing MsgBox "数据表创建成功!", vbInformation, "创建数据表" End Sub
3.3 检查数据表是否存在
Option Explicit Sub 检查数据表是否存在() '关于Connection对象的OpenSchema方法： '格式：set recordset=connection.OpenSchema(查询类型) '查询类型： 'adSchemaTables---数据表 'adSchemaColumns--字段 'adSchemaIndexes--索引 'adSchemaPrimaryKeys---主键 Dim myData As String Dim myTable As String Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset myData = ThisWorkbook.Path & "\成绩管理.accdb" myTable = "期末成绩" '建立数据库连接 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With '利用Connection对象的OpenSchema方法产生数据表记录集 Set rs = cnn.OpenSchema(adSchemaTables) '利用循环查询是否存在该数据表 ' Do While Not rs.EOF ' 'rs!table_name也可以写成rs("table_name") ' If LCase(rs!table_name) = LCase(myTable) Then 'Lcase是将字符转换为小写 ' MsgBox "数据表<" & myTable & ">存在。" ' GoTo hhh ' End If ' rs.MoveNext ' Loop ' MsgBox "数据表<" & myTable & ">不存在。" '利用Recordset对象的Find方法查找数据表并判断是否存在 'find方法会直接将光标定位到找到的记录，如果没找到，就定位到EOF末尾 rs.Find "table_name='" & myTable & "'" If rs.EOF Then MsgBox "数据表<" & myTable & ">不存在。" Else MsgBox "数据表<" & myTable & ">存在。" End If hhh: rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub Sub 错误捕捉() Dim myData As String Dim myTable As String Dim cnn As New ADODB.Connection myData = ThisWorkbook.Path & "\成绩管理.accdb" myTable = "期中成绩" '建立数据库连接 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With On Error Resume Next '遇到错误，继续往下执行 '删除数据表 cnn.Execute "drop table " & myTable ' MsgBox Err.Number If Err.Number <> 0 Then '出错了，说明表不存在 MsgBox Err.Description '显示错误描述 Else MsgBox "该表存在。" End If Stop cnn.Close Set cnn = Nothing End Sub
3.4 获取数据库中所有表的名称和类型
Option Explicit Sub 获取数据库中所有表的名称和类型() Dim i As Integer Dim myData As String Dim cnn As New ADODB.Connection Dim rs As New ADODB.Recordset myData = ThisWorkbook.Path & "\学生管理.accdb" With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With Cells.Clear '通过常量数组Array产生表头 Range("A1:B1") = Array("表名称", "表类型") i = 2 '开始获取表名称和表类型 Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If rs!table_type = "TABLE" Then '"TABLE"只能大写 Cells(i, 1) = rs("table_name") '也可以写成rs!table_name Cells(i, 2) = rs("table_type") i = i + 1 End If rs.MoveNext Loop Columns.AutoFit rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub
3.5 表中字段的判断及信息获取
Option Explicit Sub 检查字段是否存在() Dim myData As String Dim myTable As String Dim myColumn As String Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset myData = ThisWorkbook.Path & "\学生管理.accdb" myTable = "学生" myColumn = "姓名1" '建立数据库连接 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With '利用Connection对象的OpenSchema方法产生字段记录集 Set rs = cnn.OpenSchema(adSchemaColumns) '利用循环查询是否存在该数据表 ' Do While Not rs.EOF ' 'rs!table_name也可以写成rs("table_name") ' If LCase(rs!table_name) = LCase(myTable) Then 'Lcase是将字符转换为小写 ' MsgBox "数据表<" & myTable & ">存在。" ' GoTo hhh ' End If ' rs.MoveNext ' Loop ' MsgBox "数据表<" & myTable & ">不存在。" '利用Recordset对象的Find方法查找字段并判断是否存在 'find方法会直接将光标定位到找到的记录，如果没找到，就定位到EOF末尾 rs.Find "column_name='" & myColumn & "'" If rs.EOF Then MsgBox "数据表<" & myTable & ">中不存在字段<" & myColumn & ">" Else MsgBox "数据表<" & myTable & ">中存在字段<" & myColumn & ">" End If hhh: rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub Sub 获取数据库中所有表的名称和类型() Dim i As Integer Dim myData As String Dim cnn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim myTable As String myData = ThisWorkbook.Path & "\学生管理.accdb" myTable = "学生" With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With Cells.Clear '通过常量数组Array产生表头 Range("A1:C1") = Array("字段名", "字段类型", "字段大小") i = 2 '开始获取表名称和表类型 Dim myField As ADODB.Field rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic For Each myField In rs.Fields Range("A" & i) = myField.Name '字段名称 'field.type用于获取字段的类型，但是不会直接返回类型的字符串 '而是返回表示该类型的一个integer数字。 Range("B" & i) = IntToString(myField.Type) '字段类型 Range("C" & i) = myField.DefinedSize '字段大小 i = i + 1 Next Columns.AutoFit rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub '自定义函数，用于将数据类型整数值转换为类型字符串 Function IntToString(myInt As Integer) As String Dim myStr As String '定义类型字符串变量，用于存储转换后的类型字符串 Select Case myInt Case 20: myStr = "adBigInt" Case 128: myStr = "adBinary" Case 11: myStr = "adBoolean" Case 8: myStr = "adBSTR" Case 136: myStr = "adChapter" Case 129: myStr = "adChar" Case 6: myStr = "adCurrency" Case 7: myStr = "adDate" Case 133: myStr = "adDBDate" Case 134: myStr = "adDBTime" Case 135: myStr = "adDBTimeStamp" Case 14: myStr = "adDecimal" Case 5: myStr = "adDouble" Case 0: myStr = "adEmpty" Case 10: myStr = "adError" Case 64: myStr = "adFileTime" Case 72: myStr = "adGUID" Case 9: myStr = "adIDispatch" Case 3: myStr = "adInteger" Case 13: myStr = "adIUnknown" Case 205: myStr = "adLongVarBinary" Case 201: myStr = "adLongVarChar" Case 203: myStr = "adLongVarWchar" Case 131: myStr = "adNumeric" Case 138: myStr = "adPropVariant" Case 4: myStr = "adSingle" Case 2: myStr = "adSmallInt" Case 16: myStr = "adTinyInt" Case 21: myStr = "adUnsignedBigInt" Case 19: myStr = "adUnsignedInt" Case 18: myStr = "adUnsignedSmallInt" Case 17: myStr = "adUnsignedtinyInt" Case 132: myStr = "adUserDefined" Case 204: myStr = "adVarBinary" Case 200: myStr = "adVarChar" Case 12: myStr = "adVariant" Case 139: myStr = "adVarNumeric" Case 202: myStr = "adVarWChar" Case 130: myStr = "adWChar" Case Else: myStr = "Error" End Select IntToString = myStr End Function
3.6 对字段的增删改
Option Explicit Sub 对字段的增删改() Dim myData As String, myTable As String Dim cnn As New ADODB.Connection, sql As String myData = ThisWorkbook.Path & "\学生管理.accdb" myTable = "成绩" '建立数据库的连接 With cnn .Provider = "microsoft.ace.oledb.12.0" .Open myData End With '增加字段：alter table 表名 add 字段名 类型(大小) ' sql = "alter table " & myTable & " add 备注 text(50)" '删除字段：alter table 表名 drop 字段名 ' sql = "alter table 成绩 drop 备注" '修改字段类型和大小：alter table 表名 alter 字段名 类型(大小) sql = "alter table 成绩 alter 课程代码 text(20)" On Error GoTo hhh '遇到错误继续执行On Error Resume Next cnn.Execute sql '关闭连接，释放变量 cnn.Close Set cnn = Nothing Exit Sub '没有出错就退出 hhh: MsgBox Err.Description End Sub
3.7 将工作表数据导入数据库
循环
Option Explicit Dim cnn As ADODB.Connection '连接对象变量 Dim myCmd As ADODB.Command '命令对象变量 Dim rs As ADODB.Recordset '记录集对象变量 Sub 循环方式() '建立数据库连接 Set cnn = New ADODB.Connection With cnn .Provider = "microsoft.ace.oledb.12.0" .Open ThisWorkbook.Path & "\成绩管理.accdb" End With '查询数据表是否已经存在 Dim myTable As String myTable = "课程" '指定数据表名 Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If LCase(rs!table_name) = LCase(myTable) Then GoTo hhh '如果存在，则直接添加记录 End If rs.MoveNext Loop '如果表不存在，就创建数据表 Set myCmd = New ADODB.Command Set myCmd.ActiveConnection = cnn myCmd.CommandText = "create table " & myTable _ & "(课程代码 text(20),课程名称 text(20),课程类别 text(8)," _ & "学时 integer,学分 integer,授课教师 text(10))" '利用Command对象的Execute方法执行命令 myCmd.Execute , , adCmdText hhh: Dim n As Integer, i As Integer, j As Integer, sql As String n = Range("A1").End(xlDown).Row '记录当前工作表有效数据行数 For i = 2 To n '检查是否已经存在某条记录 sql = "select * from " & myTable _ & " where 课程代码='" & Cells(i, 1).Value & "'" _ & " and 课程名称='" & Cells(i, 2).Value & "'" _ & " and 课程类别='" & Cells(i, 3).Value & "'" _ & " and 学时=" & Cells(i, 4).Value _ & " and 学分=" & Cells(i, 5).Value _ & " and 授课教师='" & Cells(i, 6).Value & "'" Set rs = New ADODB.Recordset rs.Open sql, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount = 0 Then '如果数据表中没有工作表中某行数据，则添加数据 rs.AddNew For j = 1 To rs.Fields.Count rs.Fields(j - 1) = Cells(i, j).Value Next j rs.Update Else '如果数据表中有工作表中某行数据，就将数据进行更新 For j = 1 To rs.Fields.Count rs.Fields(j - 1) = Cells(i, j).Value Next j rs.Update End If Next i MsgBox "数据保存完毕。", vbInformation, "提示" rs.Close cnn.Close Set rs = Nothing Set myCmd = Nothing Set cnn = Nothing End Sub
数组
Option Explicit Dim cnn As ADODB.Connection '连接对象变量 Dim rs As ADODB.Recordset '记录集对象变量 Dim myCmd As ADODB.Command '命令对象变量 Sub 数组方式() '将工作表数据存入数组 Dim arr arr = Range("A1").CurrentRegion '存数据 '建立数据库连接 Set cnn = New ADODB.Connection With cnn .Provider = "microsoft.ace.oledb.12.0" .Open ThisWorkbook.Path & "\成绩管理.accdb" End With '查询数据表是否已经存在 Dim myTable As String myTable = "课程" '指定数据表名 Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If LCase(rs!table_name) = LCase(myTable) Then GoTo hhh '如果存在，则直接添加记录 End If rs.MoveNext Loop '如果表不存在，就创建数据表 Set myCmd = New ADODB.Command Set myCmd.ActiveConnection = cnn myCmd.CommandText = "create table " & myTable _ & "(课程代码 text(20),课程名称 text(20),课程类别 text(8)," _ & "学时 integer,学分 integer,授课教师 text(10))" '利用Command对象的Execute方法执行命令 myCmd.Execute , , adCmdText hhh: Dim i As Integer, j As Integer, sql As String For i = 2 To UBound(arr) sql = "select * from " & myTable _ & " where 课程代码='" & arr(i, 1) & "'" _ & " and 课程名称='" & arr(i, 2) & "'" _ & " and 课程类别='" & arr(i, 3) & "'" _ & " and 学时=" & arr(i, 4) _ & " and 学分=" & arr(i, 5) _ & " and 授课教师='" & arr(i, 6) & "'" Set rs = New ADODB.Recordset rs.Open sql, cnn, adOpenKeyset, adLockOptimistic If rs.RecordCount = 0 Then '如果数据表中没有工作表中某行数据，则添加数据 rs.AddNew For j = 1 To rs.Fields.Count rs.Fields(j - 1) = arr(i, j) Next j rs.Update Else '如果数据表中有工作表中某行数据，就将数据进行更新 For j = 1 To rs.Fields.Count rs.Fields(j - 1) = arr(i, j) Next j rs.Update End If Next i MsgBox "数据保存完毕。", vbInformation, "提示" rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub
4.综合项目
员工信息浏览系统
Option Explicit Sub 打开窗口() frmEmpInfo3.Show End Sub'员工信息浏览系统 Option Explicit Dim con As ADODB.Connection '声明连接对象变量 Dim rs As ADODB.Recordset '声明记录集对象变量 '释放变量空间、关闭数据库连接、关闭窗体 Private Sub cmdClose_Click() con.Close Set rs = Nothing Set con = Nothing Unload Me '关闭窗体 End Sub '-----------------------------------------------------------------------------------------------'单击部门-选择员工 '鼠标选择某个部门，相当于单击列表框，单击列表框，查询所选部门的员工 '提取员工的编号和姓名，避免姓名重复的问题 Private Sub lstBM_Click() Dim sql As String, i As Integer sql = "select 编号,姓名 from 员工 where 部门='" & lstBM.Value & "' order by 编号" rs.Open sql, con, adOpenKeyset, adLockOptimistic With lstEmp .Clear For i = 1 To rs.RecordCount .AddItem rs("编号") & Space(2) & rs("姓名") 'space(n)，用于产生n个空格 rs.MoveNext Next i End With rs.Close End Sub '-----------------------------------------------------------------------------------------------'单击部门-选择员工 '-----------------------------------------------------------------------------------------------'单击选择员工-员工信息 Private Sub lstEmp_Click() Dim arr, i As Integer Dim sql As String sql = "select * from 员工 where 编号='" & Left(lstEmp.Value, 6) & "'" '获取选择员工里面左边6位的编号 rs.Open sql, con, adOpenKeyset, adLockOptimistic '数组将每个字段的值存到相应的控件里 arr = Array("txtID", "txtName", "txtNumber", "txtBM", "txtAge", _ "txtZW", "txtDate", "txtAddress", "txtMail", "txtInfo") For i = 0 To UBound(arr) '获取最大角标 Me.Controls(arr(i)).Value = rs.Fields(i) Next i rs.Close End Sub '-----------------------------------------------------------------------------------------------'单击选择员工-员工信息 '当窗体加载时，填写lstBM这个列表框的内容 Private Sub UserForm_Initialize() '建立数据库的连接 Set con = New ADODB.Connection With con .Provider = "microsoft.ace.oledb.12.0" .ConnectionString = "data source=" & ThisWorkbook.Path & "\学生管理.accdb" .Open End With '提取不重复的部门名称 Dim sql As String '定义命令字符串变量 sql = "select distinct 部门 from 员工" Set rs = New ADODB.Recordset '创建记录集对象 rs.Open sql, con, adOpenKeyset, adLockOptimistic '-----------------------------------------------------------------------------------------------'选择部门 '将记录集中的部门名称显示到lstBM部门列表框中 Dim i As Integer With lstBM .Clear For i = 1 To rs.RecordCount .AddItem rs("部门") rs.MoveNext '将记录集中的指针指向下一条记录 Next i End With rs.Close '-----------------------------------------------------------------------------------------------'选择部门 End Sub
ListView控件-分页显示
ListView控件使用
Option Explicit '引用ListView控件 '鼠标右键——>添加附件——>勾选Microsoft ListView Control Version 6.0 Private Sub 添加表头_Click() With ListView1 '--------------------------------------------------------------------------------- '方法1：挨个添加，比较适合列数已知且不太多的情况 ' .ColumnHeaders.Add 1, "xh", "学号", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 2, "xm", "姓名", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 3, "bj", "班级", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 4, "yw", "语文", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 5, "sx", "数学", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 6, "yy", "英语", .Width / 7, lvwColumnLeft ' .ColumnHeaders.Add 7, "zf", "总分", .Width / 7, lvwColumnLeft '方法2：利用循环动态添加 .ColumnHeaders.Clear '清除数据 Dim i As Integer '循环变量 Dim col As Integer '用于记录列数 col = Range("A1").End(xlToRight).Column '从a1开始向右获取最后一列列号 For i = 1 To col .ColumnHeaders.Add i, , Cells(1, i), .Width / col, lvwColumnLeft Next i '--------------------------------------------------------------------------------- '格式处理 .Gridlines = True '显示表格线 .FullRowSelect = True '支持整行选择 .View = lvwReport '设置数据以报表形式显示 '循环添加记录：循环添加列 .ListItems.Clear '清除数据 Dim ITM As ListItem Dim j As Integer For i = 2 To Range("A1").End(xlDown).Row 'ListItem行记录 Set ITM = .ListItems.Add() ITM.Text = Cells(i, 1) For j = 1 To Range("A1").End(xlToRight).Column - 1 '第一列（text）和其它列（SubItems）n ITM.SubItems(j) = Cells(i, j + 1) Next j Next i End With End Sub Private Sub 添加记录_Click() Dim ITM As ListItem Dim i As Integer '--------------------------------------------------------------------------------- ' '手动添加列 ' For i = 2 To Range("A1").End(xlDown).Row ' Set ITM = ListView1.ListItems.Add() '空行 ' ITM.Text = Cells(i, 1) '第一列 ' ITM.SubItems(1) = Cells(i, 2) '第二列 ' ITM.SubItems(2) = Cells(i, 3) ' ITM.SubItems(3) = Cells(i, 4) ' ITM.SubItems(4) = Cells(i, 5) ' ITM.SubItems(5) = Cells(i, 6) ' ITM.SubItems(6) = Cells(i, 7) ' ITM.SubItems(7) = Cells(i, 8) ' Next i '循环添加列 Dim j As Integer With ListView1 .ListItems.Clear For i = 2 To Range("A1").End(xlDown).Row 'ListItem行记录 Set ITM = .ListItems.Add() ITM.Text = Cells(i, 1) '第一行第一列 For j = 1 To Range("A1").End(xlToRight).Column - 1 '第一列（text）和其它列（SubItems）n ITM.SubItems(j) = Cells(i, j + 1) Next j Next i End With '--------------------------------------------------------------------------------- End Sub
利用ListView控件实现分页显示【需要多研究几遍】
Option Explicit Dim con As ADODB.Connection '声明数据库连接对象变量 Dim rs As ADODB.Recordset '声明记录集对象变量 Dim rsDS As ADODB.Recordset '声明记录集对象变量 Dim rsPage As Integer '用于记录当前处于第几页 '组合框Change事件，当改变组合框的值，重新刷新窗体上的记录显示 Private Sub cmbRecNum_Change() rsPage = 1 '显示第1页记录 Call AddRows(rsPage) End Sub '单击“上一页”按钮，切换到上一页 Private Sub cmdBefore_Click() If rsPage <> 1 Then rsPage = rsPage - 1 Call AddRows(rsPage) End If End Sub '单击“关闭”按钮，释放变量空间，关闭窗体 Private Sub cmdClose_Click() rs.Clone con.Close Set rs = Nothing Set rsDS = Nothing Set con = Nothing End End Sub '单击“第一页”按钮，切换到第一页 Private Sub cmdFirst_Click() rsPage = 1 Call AddRows(rsPage) End Sub '单击“最末页”按钮，切换到最末页 Private Sub cmdLast_Click() rsPage = rs.PageCount Call AddRows(rsPage) End Sub '单击“下一页”按钮，切换到下一页 Private Sub cmdNext_Click() If rsPage <> rs.PageCount Then rsPage = rsPage + 1 Call AddRows(rsPage) End If End Sub '窗体加载时，完成数据库的连接，设置显示页和每页记录数等 Private Sub UserForm_Initialize() '循环方式为组合框添加项目，提供显示条数的选择 Dim i As Integer '循环变量 For i = 1 To 20 cmbRecNum.AddItem i Next '设置一些默认值，方便窗体的初始化 cmbRecNum.Value = 5 '默认每页显示5条记录 rsPage = 1 '默认显示第1页记录 '建立数据库的连接 Set con = New ADODB.Connection '创建连接对象 With con .Provider = "microsoft.ace.oledb.12.0" .ConnectionString = "data source=" & ThisWorkbook.Path & "\学生管理.accdb" .Open End With '查询表中数据，生成记录集 Dim SQL As String '定义命令字符串变量 SQL = "select * from 员工1 order by 编号 asc" Set rs = New ADODB.Recordset '创建记录集对象 rs.Open SQL, con, adOpenKeyset, adLockOptimistic '生成ListView（名称为lstShow）控件的基本框架结构 With lstShow .ColumnHeaders.Clear '清除表头 .ListItems.Clear '清除记录 .View = lvwReport '设置显示方式为“报表形式” .FullRowSelect = True '允许选中整行 .Gridlines = True '显示网格线 '使用循环方式为lstShow控件设置标题 For i = 0 To rs.Fields.Count - 1 .ColumnHeaders.Add , , rs.Fields(i).Name Next i End With Call AddRows(rsPage) '调用子过程显示第一页记录 End Sub '自定义子过程，用于随时在lstShow控件上显示当前页的数据 Public Sub AddRows(myPage As Integer) 'myPage就表示第几页 On Error Resume Next Dim i As Integer, j As Integer '创建局部RecordSet对象rsDS，保存rs记录集中当前页的记录数据 Set rsDS = New ADODB.Recordset For i = 0 To rs.Fields.Count - 1 'append,追加的意思 rsDS.Fields.Append rs.Fields(i).Name, rs.Fields(i).Type, rs.Fields(i).DefinedSize Next i rsDS.Open ' 打开局部RecordSet对象rsDS '关键:PageSize，表示记录集的每页的记录条数 rs.PageSize = Val(cmbRecNum.Value) '重置rs每页显示的记录条数 rs.AbsolutePage = myPage '重置rs的当前记录页,根据赋值的值显示第几页的第一条记录值 '将rs当前记录页的记录保存到rsDS中 For i = 1 To rs.PageSize rsDS.AddNew '添加一行记录 For j = 0 To rs.Fields.Count - 1 rsDS.Fields(j).Value = rs.Fields(j).Value Next j If rs.EOF Then Exit For rs.MoveNext Next i '在lstShow控件中显示当前页的记录数据 rsDS.MoveFirst '定位rsDS中的第一条记录 With lstShow .ListItems.Clear For i = 1 To rsDS.RecordCount .ListItems.Add , , rsDS.Fields(0).Value For j = 1 To rsDS.Fields.Count - 1 .ListItems(i).SubItems(j) = rsDS.Fields(j).Value Next j rsDS.MoveNext Next i End With '在文本框中刷新当前记录页信息 txtPage.Value = myPage & "/" & rs.PageCount End Sub
数据库维护系统
【需要多研究几遍】
Option Explicit '加载窗体时，建立数据库连接，并刷新“数据表”列表框的信息 Private Sub UserForm_Initialize() '建立数据库的连接 Call 数据库连接 '调用自定义过程，为“数据表清单”列表框刷新数据 Call 获取数据表清单 End Sub '自定义过程“获取数据表清单”用于为列表框刷新数据 Public Sub 获取数据表清单() Set rs = cnn.OpenSchema(adSchemaTables) With 数据表清单 .Clear Do Until rs.EOF If rs!table_type = "TABLE" Then .AddItem rs!table_name End If rs.MoveNext Loop .ListStyle = fmListStyleOption '让每个选项有按钮 End With rs.Close Set rs = Nothing End Sub Private Sub 备份数据表_Click() Dim sql As String, myNewName As String '判断是否选择了要备份的数据表 If 数据表清单.ListIndex = -1 Then MsgBox "没有选择要复制的数据表。", vbCritical, "警告" Exit Sub End If '确认是否备份选择的数据表 If MsgBox("是否要备份数据表<" & 数据表清单.Text & ">？", _ vbQuestion + vbYesNo) = vbNo Then Exit Sub reStart: '指定数据表的新名称 myNewName = InputBox("请输入数据表新名称：", "输入数据表名称") If Len(Trim(myNewName)) = 0 Then MsgBox "没有输入有效的数据表名。", vbCritical, "警告" Exit Sub End If '检查是否存在同名数据表 Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If LCase(rs!table_name) = LCase(myNewName) Then MsgBox "数据表<" & myNewName & ">已经存在，请重新输入。", _ vbCritical, "警告" GoTo reStart End If rs.MoveNext Loop '利用生成表查询达到备份效果 sql = "select * into " & myNewName & " from " & 数据表清单.Text cnn.Execute sql MsgBox "成功将数据表<" & 数据表清单.Text & ">备份了一份。名称为<" _ & myNewName & ">", vbInformation + vbOKOnly, "备份数据表" '刷新“数据表清单”列表框 Call 获取数据表清单 '删除“字段清单”列表框中的项目 字段清单.Clear Set rs = Nothing End Sub Private Sub 创建数据表_Click() 创建数据表窗体.Show Call 获取数据表清单 End Sub Private Sub 改变字段类型_Click() Dim sql As String, myFieldType As String '判断是否选择了要修改类型的字段 If 字段清单.ListIndex = -1 Then MsgBox "没有选择要改变数据类型的字段。", vbCritical, "警告" Exit Sub End If '确认是否要改变选择字段的数据类型 If MsgBox("是否要改变字段<" & 字段清单.Text & ">的数据类型？", _ vbQuestion + vbYesNo) = vbNo Then Exit Sub reStart: '指定字段的新类型 myFieldType = InputBox("请输入字段新类型：", "输入字段类型") If Len(Trim(myFieldType)) = 0 Then MsgBox "没有输入有效的字段类型。", vbCritical, "警告" Exit Sub End If '改变选择字段的数据类型 sql = "alter table " & 数据表清单.Text & " alter " _ & 字段清单.Text & Space(1) & myFieldType cnn.Execute sql MsgBox "数据表<" & 数据表清单.Text & ">中的字段<" & 字段清单.Text _ & ">的类型被改变。", vbInformation + vbOKOnly, "改变字段类型" End Sub Private Sub 删除数据表_Click() Dim sql As String '判断是否选择了要删除的数据表 If 数据表清单.ListIndex = -1 Then MsgBox "没有选择要删除的数据表。", vbCritical, "警告" Exit Sub End If '确认是否删除选择的数据表 If MsgBox("是否要删除数据表<" & 数据表清单.Text & ">？", _ vbQuestion + vbYesNo) = vbNo Then Exit Sub '删除选定的数据表 sql = "drop table " & 数据表清单.Text cnn.Execute sql MsgBox "数据表<" & 数据表清单.Text & ">被成功删除。", _ vbInformation + vbOKOnly, "删除数据表" '刷新“数据表清单”列表框 Call 获取数据表清单 '删除“字段清单”列表框中的项目 字段清单.Clear End Sub Private Sub 删除字段_Click() Dim sql As String '判断是否选择了要删除的字段 If 字段清单.ListIndex = -1 Then MsgBox "没有选择要删除的字段。", vbCritical, "警告" Exit Sub End If '确认是否删除选择的字段 If MsgBox("是否要删除字段<" & 字段清单.Text & ">？", _ vbQuestion + vbYesNo) = vbNo Then Exit Sub '删除选定的字段 sql = "alter table " & 数据表清单.Text & " drop " & 字段清单.Text cnn.Execute sql MsgBox "数据表<" & 数据表清单.Text & ">中的字段<" & 字段清单.Text _ & ">被成功删除。", vbInformation + vbOKOnly, "删除字段" '刷新“字段清单”列表框 Call 获取字段清单 End Sub '单击“数据表清单”列表框，调用子过程，用于刷新所选表的字段列表 Private Sub 数据表清单_Click() Call 获取字段清单 End Sub '子过程“获取字段清单”用于获取所选表的字段，并显示在列表框中 Public Sub 获取字段清单() Dim sql As String, i As Integer '查询数据表，将字段名清单设置给“字段清单”列表框 sql = "select * from " & 数据表清单.Text Set rs = New ADODB.Recordset rs.Open sql, cnn, adOpenKeyset, adLockOptimistic With 字段清单 .Clear For i = 0 To rs.Fields.Count - 1 .AddItem rs.Fields(i).Name Next i .ListStyle = fmListStyleOption End With rs.Close Set rs = Nothing End Sub Private Sub 添加字段_Click() Dim sql As String, myNewField As String '判断是否选择了要添加字段的数据表 If 数据表清单.ListIndex = -1 Then MsgBox "没有选择要添加字段的数据表。", vbCritical, "警告" Exit Sub End If reStart: '指定新字段名称 myNewField = InputBox("请输入新字段名称和类型：", "输入新字段", "地址 Text(50)") If Len(Trim(myNewField)) = 0 Then MsgBox "没有输入有效的字段名。", vbCritical, "警告" Exit Sub End If '确认是否添加字段 If MsgBox("是否要向数据表<" & 数据表清单.Text & ">中添加字段<" _ & myNewField & ">？", vbQuestion + vbYesNo) = vbNo Then Exit Sub '检查是否存在同名字段 Set rs = cnn.OpenSchema(adSchemaColumns) Do Until rs.EOF If LCase(rs!Column_name) = LCase(myNewField) Then MsgBox "数据表<" & 数据表清单.Text & ">中已经存在字段<" _ & myNewField & ">！", vbCritical, "警告" GoTo reStart End If rs.MoveNext Loop '添加字段 sql = "alter table " & 数据表清单.Text & " add " & myNewField cnn.Execute sql MsgBox "数据表<" & 数据表清单.Text & ">中成功添加了字段<" _ & myNewField & ">", vbInformation + vbOKOnly, "添加字段" '刷新“数据表清单”列表框 Call 获取字段清单 Set rs = Nothing End Sub Private Sub 退出_Click() cnn.Close Set rs = Nothing Set cnn = Nothing Unload 数据库维护 End Sub Private Sub 重命名数据表_Click() Dim sql As String, myNewName As String '判断是否选择了要重命名的数据表 If 数据表清单.ListIndex = -1 Then MsgBox "没有选择要重命名的数据表。", vbCritical, "警告" Exit Sub End If '确认是否重命名选择的数据表 If MsgBox("是否要重命名数据表<" & 数据表清单.Text & ">？", _ vbQuestion + vbYesNo) = vbNo Then Exit Sub reStart: '指定数据表的新名称 myNewName = InputBox("请输入数据表新名称：", "输入数据表名称") If Len(Trim(myNewName)) = 0 Then MsgBox "没有输入有效的数据表名。", vbCritical, "警告" Exit Sub End If '检查是否存在同名数据表 Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If LCase(rs!table_name) = LCase(myNewName) Then MsgBox "数据表<" & myNewName & ">已经存在，请重新输入。", _ vbCritical, "警告" GoTo reStart End If rs.MoveNext Loop '查询原数据表，生成新表名，删除原表达到重命名的效果 sql = "select * into " & myNewName & " from " & 数据表清单.Text cnn.Execute sql sql = "drop table " & 数据表清单.Text cnn.Execute sql MsgBox "成功将数据表<" & 数据表清单.Text & ">名称改为<" _ & myNewName & ">", vbInformation + vbOKOnly, "数据表重命名" '刷新“数据表清单”列表框 Call 获取数据表清单 '删除“字段清单”列表框中的项目 字段清单.Clear Set rs = Nothing End Sub '单击“字段清单”列表框，调用子过程，将所选字段信息显示在文本框中 Private Sub 字段清单_Click() Call 获取字段信息 End Sub '子过程“获取字段信息”用于获取所选字段的信息，并显示在文本框中 Public Sub 获取字段信息() Dim sql As String, i As Integer '查询选择的数据表 sql = "select * from " & 数据表清单.Text Set rs = New ADODB.Recordset rs.Open sql, cnn, adOpenKeyset, adLockOptimistic '将字段的名称、类型、大小输出到对应文本框 字段名称.Value = rs.Fields(字段清单.Text).Name 字段类型.Value = IntToString(rs.Fields(字段清单.Text).Type) 字段大小.Value = rs.Fields(字段清单.Text).DefinedSize End Sub '自定义函数，用于将数据类型整数值转换为类型字符串 Function IntToString(myInt As Integer) As String Dim myStr As String '定义类型字符串变量，用于存储转换后的类型字符串 Select Case myInt Case 20: myStr = "adBigInt" Case 128: myStr = "adBinary" Case 11: myStr = "adBoolean" Case 8: myStr = "adBSTR" Case 136: myStr = "adChapter" Case 129: myStr = "adChar" Case 6: myStr = "adCurrency" Case 7: myStr = "adDate" Case 133: myStr = "adDBDate" Case 134: myStr = "adDBTime" Case 135: myStr = "adDBTimeStamp" Case 14: myStr = "adDecimal" Case 5: myStr = "adDouble" Case 0: myStr = "adEmpty" Case 10: myStr = "adError" Case 64: myStr = "adFileTime" Case 72: myStr = "adGUID" Case 9: myStr = "adIDispatch" Case 3: myStr = "adInteger" Case 13: myStr = "adIUnknown" Case 205: myStr = "adLongVarBinary" Case 201: myStr = "adLongVarChar" Case 203: myStr = "adLongVarWchar" Case 131: myStr = "adNumeric" Case 138: myStr = "adPropVariant" Case 4: myStr = "adSingle" Case 2: myStr = "adSmallInt" Case 16: myStr = "adTinyInt" Case 21: myStr = "adUnsignedBigInt" Case 19: myStr = "adUnsignedInt" Case 18: myStr = "adUnsignedSmallInt" Case 17: myStr = "adUnsignedtinyInt" Case 132: myStr = "adUserDefined" Case 204: myStr = "adVarBinary" Case 200: myStr = "adVarChar" Case 12: myStr = "adVariant" Case 139: myStr = "adVarNumeric" Case 202: myStr = "adVarWChar" Case 130: myStr = "adWChar" Case Else: myStr = "Error" End Select IntToString = myStr End Function
标签：
数据库
本文转载自: https://blog.csdn.net/liu17234050/article/details/135354556
版权归原作者 刘鑫磊up 所有， 如有侵权，请联系我们删除。
版权归原作者 刘鑫磊up 所有， 如有侵权，请联系我们删除。