VBA中SQL与记录集使用及代码调试全解析

### VBA中SQL与记录集使用及代码调试全解析 #### 1. SQL与记录集基础 SQL（结构化查询语言）是从Access表、Microsoft SQL Server、Oracle等众多数据库产品中提取信息的标准语言。在VBA中使用SQL和记录集时，有几个关键概念需要了解。 ##### 1.1 游标类型 | 常量名称 | 描述 | | ---- | ---- | | adOpenForwardOnly | 仅向前游标，与静态游标类似，但游标只能在表中向前移动。如果不指定游标类型，这是默认设置。 | | adOpenKeyset | 键集游标，类似于动态游标，但其他用户添加的记录不会添加到记录集中，其他用户删除的记录对当前记录集不可访问。 | 需要注意的是，`.RecordCount` 和 `.AbsolutePosition` 属性只有在使用静态游标类型时才会返回正确的值。使用动态游标时，它们总是返回 -1，因为记录集内记录的数量和位置可能会发生变化。 ##### 1.2 记录集中的字段引用 在记录集中，每条记录是一个字段集合。可以通过字段在记录中的位置或名称来引用它们： - 通过位置引用：`myRecordSet.Fields(0)` 引用记录中的第一个字段，`myRecordset.Fields(1)` 引用第二个字段，依此类推。 - 通过名称引用：`myRecordSet.Fields("fieldname")`，其中 `fieldname` 是表中定义的字段名称，必须与表设计视图中显示的名称完全一致（包括空格）。 ##### 1.3 基于查询创建记录集 记录集不必基于单个表，可以基于查询创建。但在 `myRecordset.Open` 语句中不能使用查询名称，只能使用表名。如果要基于查询创建记录集，需要使用查询的SQL语句。 #### 2. SQL语句基础 SQL语句的基本语法如下： ```plaintext SELECT fields1 FROM table(s) [WHERE criterion] [ORDER BY fields2] ``` - `fields1`：表示表中的字段列表（或 `*` 表示所有字段）。 - `table(s)`：表示存储数据的表名。 - `criterion`：表示过滤记录的表达式，例如 `State="CA"`。 - `fields2`：表示用于对记录进行排序的字段。 `WHERE` 和 `ORDER BY` 部分是可选的。 编写SQL语句经过一些练习后会变得相当容易。每次使用设计视图创建查询时，实际上就是在编写SQL语句。查询中选择的字段成为记录集中包含的字段，但只有在“显示”框中有勾选标记的字段才会实际包含在内。从哪个表中选择记录在网格顶部清晰可见，“排序”行定义 `ORDER BY` 子句，“条件”行指定 `WHERE` 子句。 要查看为任何查询创建的SQL语句，可以在查询设计视图中右键单击标题栏或文档选项卡，然后从快捷菜单中选择“SQL视图”；或者单击功能区的“查询工具 - 设计”选项卡，在“结果”组中选择“视图” -> “SQL视图”。 #### 3. 在VBA中使用SQL语句 将SQL语句复制到VBA代码的 `myRecordSet.Open` 语句中时，需要进行一些修改： 1. 移除SQL语句末尾的分号（;）。 2. 如果粘贴的SQL语句跨越多行，将这些行合并为一行（或者使用续行字符进行分行）。 3. 如果SQL语句中包含双引号（"），将其替换为单引号（'）。 以下是操作步骤： 1. 选择SQL语句：通过拖动鼠标指针选中整个SQL语句，然后按 `Ctrl+C` 或右键单击高亮显示的文本并从快捷菜单中选择“复制”，将SQL语句复制到剪贴板。 2. 在代码窗口的过程中，输入 `recordset.Open` 语句，后跟两组双引号，将光标置于双引号之间，例如： ```vba myRecordSet.Open "|" ``` 3. 按 `Ctrl+V` 将SQL语句粘贴到双引号之间。 4. 删除末尾的分号：光标位于SQL语句末尾，分号右侧，按 `Backspace` 键删除分号。 5. 合并多行语句：如果SQL语句分为多行，将光标移到第一行末尾。如果行末有引号，删除它，然后按 `Delete` 键删除换行符，将下一行与当前行合并，在完整单词之间留一个空格，重复此过程直到整个SQL语句在代码窗口中成为一长行。 6. 替换双引号：检查SQL语句中的双引号，将语句内的双引号替换为单引号。 #### 4. 拆分长SQL语句 为了避免将SQL语句作为一长行处理，可以将其存储为字符串变量，然后在 `myRecordset.Open` 语句中使用该变量名。具体步骤如下： 1. 声明一个字符串变量来存储SQL语句，例如： ```vba Dim mySQL As String ``` 2. 为字符串分配SQL语句，遵循以下规则： - 每个代码块都用双引号完全括起来。 - 如果单词后面有空格，在该行中保留该空格。 - 每行后面跟一个与号（&）字符（连接字符串运算符）、一个空格和续行字符（_）。 - 在 `recordset.Open` 语句中使用变量名。 示例代码如下： ```vba 'Form a SQL statement from “chunks”. Dim mySQL As String mySQL = "SELECT Orders.*, [Address Book].* FROM [Address Book] " & _ "INNER JOIN Orders ON [Address Book].ContactID = Orders.ContactID " & _ "WHERE ((([Add ```