点上方关注我们,每天早上5分钟,让Excel多点轻松
更多SQL图文教程,菜单【进阶教程】→【SQL大法】1,
诸君好,又见面了。女生拥抱男生握手。
要不要先讲个小笑话,活跃下氛围?毕竟下面两个段落都是僵硬的概念,乏味的很哩。
——不听就算了。
打个响指,通过前面的章节,我们已经知道,查询是SQL最频繁也是最核心的语句;而ADO对象与之相对应的是记录集的概念,所谓记录集就是指从数据库中检索到的数据的集合,由记录和字段两个部分构成。
通常有两种方法创建查询记录集。
一种是我们前面讲过的Connection对象的Execute方法。
Excel VBA+ADO+SQL入门教程022:Connection对象的Execute方法
另外一种就是我们今天要讲的Recordset对象的Open方法。
2,
Recordset对象是ADO中最重要也是最常用的对数据库数据进行操作的对象;功能强大,属性、方法和事件众多;不过……放轻松,事件我们基本用不上,属性和方法经常用到的也不多,且大都易于理解和操作。
下面演示如何使用VBA代码引用Recordset对象,并创建一个记录集。
假设有一张工作表,名为“数据表”,内容如下图所示:
现在需要在“查询”表里查询年龄大于18岁的人员明细。查询结果如下:
示例代码如下:
Sub CreateRecordset() Dim cnn As Object Dim rst As Object Dim strPath As String Dim strSQL As String Dim lngCount As Long Dim i As Integer Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.RecordSet") '后期引用Recordset对象 strPath = ThisWorkbook.FullName '指定ADO连接的文件路径(本工作簿) cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Extended Properties=Excel 12.0;" _ & "Data Source=" & strPath strSQL = "SELECT * FROM [数据表$] WHERE 年龄>18" 'SQL语句 rst.Open strSQL, cnn, 1, 3 '使用Open方法建立记录集 'rst.Open strSQL, cnn, AdOpenKeyset, AdLockOptimistic Cells.ClearContents For i = 0 To rst.Fields.Count - 1 '遍历读取记录集中的字段 Cells(1, i + 1) = rst.Fields(i).Name Next i Range("A2").CopyFromRecordset rst '读取记录集中的记录 lngCount = rst.RecordCount '记录集中记录的条数 MsgBox "共查询到:" & lngCount & "条记录。" rst.Close cnn.Close Set rst = Nothing Set cnn = NothingEnd Sub
3,Open
上述代码首先使用Connection对象建立和代码所在工作簿的连接,然后使用Recordset对象的Open方法创建查询记录集。
Open语法格式如下:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
示例语句如下:
rst.Open strSQL, cnn, 1, 3
参数Source是可选的,可以是Command对象、SQL语句、数据库的表名等。对我们而言,通常就是SQL语句。
参数ActiveConnection是可选的,用于指定Connection对象变量名;字符串或包含ConnectionString的参数。对我们而言,通常也就是Connection对象。
参数CursorType是可选的,用于指定当打开Recordset时提供者应使用的游标类型,其值可以是下表所列举的常量之一。作为新手,固定使用AdOpenKeyset(值为1)即可。
参数LockType是可选的,用于确定提供者打开Recordset时应该使用的锁定类型,其值可以是下表所列举的常量之一。如果需要对数据库进行修改、删除、更新等操作,必须设定为AdLockOptimistic。作为新手,管他三七二十一,固定使用AdLockOptimistic(值为3)就好了。
参数Options是可选的,表示提供者如何计算Source参数(如果它代表的不是Command对象),或者从以前保存Recordset的文件中恢复Recordset。该参数可以是一个或多个CommandTypeEnum值或ExecuteOptionEnum值——这厮我们一般用不上,可以假装生命中没有它。
小贴士:
使用CreateObject函数后期绑定ADO类库时,ADO对象的相关参数不能使用常量名称,只能使用参数的值。例如,示例代码中使用以下语句会造成程序运行错误。
rst.Open strSQL, cnn, AdOpenKeyset, AdLockOptimistic
4,Fields
上述代码的以下部分将记录集中的字段名写入工作表。
For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1) = rst.Fields(i).NameNext i
rst.fields返回Recordset对象的Fields集合,该集合包含了和当前记录集有关的所有字段。
rst.Fields.Count返回字段的数量。
rst.Fields(0).Name表示记录集的第1个字段的标题名,也就是“编号”。
rst.Fields(0).Value表示记录集第1个字段的当前记录,也就是100007……除了使用索引法,也可以使用rst.Fields("编号").Value来返回指定字段当前的记录。
5,RecordCount
rst.RecordCount返回Recordset对象中的记录数目。通过它,我们可以判断是否存在符合条件的查询结果。
小贴士:
还记得吗?在Connection的Execute那一节,我们特别说明过,Execute方法返回的记录集无法使用RecordCount属性得到正确的结果,原因是……不记得了?那就倒带重看吧。
6,CopyFromRecordset
以下代码将记录集中的记录复制到工作表左上角为A2单元格的区域
Range("A2").CopyFromRecordset rst
CopyFromRecordset是Excel Range对象的方法,用于将记录集中的记录复制到单元格区域。我们之前的代码常用它,但一直没抓到机会详细介绍,这儿一并说了。
其语法格式如下:
Range.CopyFromRecordset(Data,[MaxRows],[MaxColumns])
参数Data是必需的,表示复制到指定区域的Recordset对象。
参数MaxRows是可选的,表示复制到工作表的记录个数上限。如果忽略该参数,将复制所有记录。
比如,记录集有10条记录,我们只需要前5条,代码如下:
Range("A2").CopyFromRecordset rst , 5
参数MaxColumns是可选的,表示复制到工作表的字段个数上限。如果忽略该参数,将复制所有字段。
后面两个可选的参数,虽然有用,但实际用到的情况并不多,So——仅供了解先。
……
……
The End
下期见。
示例文件下载:
https://pan.baidu.com/s/1PE0xew4lhj3H-jbYmmbmkQ
提取码:3xaa
系统学习Excel,推荐加入我的Excel社群 ▼更多教程&练习
教程1:零基础学Excel(一)什么是Excel?
作业:学习不练习=没学习丨七道练习题试试你能答对吗?
©看见星光