SQL入门教程第02课：什么是ADO？

职场 2024-09-29 13:08 福建

HI，大家好，我是星光。

上一期教程咱们认识了SQL，这一期我们聊下ADO。不过需要提前说明的是，对VBA无感或无基础者，本章可跳过，这并不影响之后的SQL学习。

1 ADO是什么

ADO是什么？为什么要学ADO？

ADO (ActiveX Data Objects，ActiveX数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据……更多概念信息请自行咨询百度君，无赖脸。

之所以要学习ADO，其中一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的；而首要原因是，在EXCEL VBA中，一般只有通过ADO，才可以使用强大的SQL访问外部数据源，进而对数据查、改、增、删。

后面这话延伸在具体编程操作上，就形成了四步走发展战略……

1▶ VBA引用ADO类库

2▷ ADO建立对数据源的链接

3▶ ADO执行SQL语言

4▷ VBA处理SQL查询结果

嗯，这就好比你先找个女（男）朋友，然后谈恋爱，尔后结婚，最后离婚……

2 怎么引用ADO

在VBA中引用ADO类库一般有两种方式。

一种是前期绑定。所谓前期绑定，是指在VBE中手工勾选引用Microsoft ADO相关类库。

在Excel中，按<Alt+F11>快捷键打开VBA编辑窗口，依次单击【工具】→【引用】，打开【引用-VBAProject】对话框。在【可使用的引用】列表框中，勾选“Microsoft ActiveX Data Objects 2.8 Library”库，或“Microsoft ActiveX Data Objects 6.1 Library”库，单击【确定】按钮关闭对话框。

另一种是使用代码后期绑定。

Sub LateWife()  Dim cnn As Object  Set cnn = CreateObject("adodb.connection")End Sub

两种方式的主要区别是：前期绑定后，在代码编辑过程中，VBE支持“自动列出成员”功能，可以自动提供ADO的属性和方法，这便于代码准确快捷的编写，但当他人的Excel工作簿并没有手工前期绑定ADO类库时，相关代码将无法运行；而后期绑定的话，在代码编写过程中，VBA不提供“自动列出成员”功能，但代码复制即可使用。因此后期代码绑定ADO的通用性会更强些。

星光俺老油……老江湖的经验是，代码编写及调试时，使用前期绑定，代码编写完善后，再修改为后期绑定发布使用。

……

不论我们使用SQL语言对数据源作何操作，都得首先使用ADO创建并打开一个到数据源的链接；这就好比得先修路，才能使用汽车运输货物。

在VBA中，我们通常使用ADO的Connection.Open语句来显式建立一个到数据源的链接。

Connection.Open语法如下：

代码看不全可以左右拖动...▼

connection.Open ConnectionString, UserID, Password, Options

ConnectionString是可选的，是一个字符串，包含连接信息。

UserID是可选的，包含建立连接时所使用用户名。

Password是可选的，包含建立连接时所使用密码。

Options是可选的，决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回，默认是同步，异步参数是adAsyncConnect。

语法看起来似乎很复杂？挥挥手，不必烦扰，现在，对我们而言，重点只是大体了解一下参数ConnectionString，也就是连接字符串。虽然不同的数据库或文件有不同的连接字符串，但常用的数据库或文件的连接字符串均是固定的。

举个例子，如果将代码所在的Excel作为一个外部数据源建立链接，代码如下：

代码看不全可以左右拖动..▼

Sub MyCnn()    Dim cnn As Object '定义变量    Set cnn = CreateObject("adodb.connection") '后期绑定ADO    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullName    '建立链接    cnn.Close '关闭链接    Set cnn = Nothing '释放内存End Sub

说一下上面代码连接字符串中各关键字（第4行代码）的意思。

Provider是Connection 对象提供者名称的字符串值，03版Excel是“Microsoft.jet.OLEDB.4.0”，其它版本可以使用“Microsoft.ACE.OLEDB.12.0”；

Extended Properties是Excel版本号及其它相关信息，03版本是Excel 8.0，其它版本可以使用Excel 12.0。

其中HDR项是引用工作表是否有标题行，默认值HDR=Yes，意思是引用表的第一行是标题行。标题只能一行，不能多行，亦不能存在合并单元格。HDR=no，意思是引用表不存在标题行，也就是说第一行开始就是数据记录了；此时，相关字段名在SQL语句中可以使用f加序列号表示，第1列字段名是f1，第2列字段名是f2，其余以此类推，f是英文field(字段)的缩写。

IMEX项是汇入模式，默认为0（只读模式），1是只写，2是可读写。当参数设置为1时，除了只写，还有默认全部记录数据类型为文本的用途，关于这一点及其限制我们以后再谈。

Data Source是数据来源工作薄的完整路径。

VBA代码Application.Version可以获取计算机的Excel版本号，因此以下代码兼顾了03及各高级版本Excel的情况：

Sub Mycnn2()  Dim cnn As Object  Dim strPath As String  Dim str_cnn As String  Set cnn = CreateObject("adodb.connection")  strPath = ThisWorkbook.FullName '当前工作簿的完整路径  If Application.Version < 12 Then '判断Excel版本号，以使用不同的连接字符串    str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath  Else    str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath  End If  cnn.Open str_cnn  cnn.Close  Set cnn = NothingEnd Sub

最后，需要提醒大家的是，链接是一种昂贵的资源（官方语），因此在代码运行完毕后，请养成关闭链接（cnn.Close）并释放内存（Set cnn = Nothing）的好习惯。

本节小贴士：

1）

通常来说，连接字符串中各关键字和大小写无关，例如Provider，可以写成provider或者PROVIDER。不过，虽然关键字和大小写无关，但和拼写正确与否……当然是有关的！（想啥呢大哥）当手打的连接字符串代码运行出错时，建议先复制正确的运行，再仔细核对个人错漏之处。

2）

连接字符串中各关键字之间使用英文分号（;）间隔，例如（关键字1=值1;关键字2=值2;关键字3=值3……），另外，任何包含分号、单引号或双引号的值必须用双引号引起来，由于在VBA中连接字符串的外层已经存在了一个双引号，因此通常使用英文单引号进行转义，例如上例中的Extended Properties='Excel 12.0;HDR=yes;IMEX=2'，抄写时，千万别漏了英文单引号喔。

3）

歪兔酥锐，我掐指一算，相当一部分童鞋英语水平堪忧，想来拼写这段英文连接字符串错漏百出是很有可能的，因此特呈上锦囊一份，参见下图。别问我这图是哪来的，如果不知道，佛山无银脚，出门右拐重看本系列教程开篇第一章吧~

如果这锦囊您也不想用——其实收藏本帖，用到时打开帖子复制粘贴相关代码就可以了——不错，这才是最常用的一招。

3 ADO怎么用

聊完了如何绑定ADO以及建立与数据源的链接……

最后说下如何使用ADO执行SQL语句。

我们可以使用ADO的Connection对象或Recordset、Commannd执行SQL语句；详细内容我们放到ADO部分再讲；这里大家只需要先了解Connection对象的Execute方法就可以了。

这是一个最常用的VBA+ADO+SQL套路化查询代码，通常，我们只需要修改其中的SQL语句以及放置查询结果的工作表名称即可。

代码看不全可以左右拖动..▼

Sub DoSql_Execute()    Dim cnn As Object, rst As Object    Dim strPath As String, str_cnn As String, strSQL As String    Dim i As Long    Set cnn = CreateObject("adodb.connection")    '以上是第一步，后期绑定ADO    '    strPath = ThisWorkbook.FullName    If Application.Version < 12 Then        str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath    Else        str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath    End If    cnn.Open str_cnn    '以上是第二步，建立链接    '    strSQL = "SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80"    'Sql语句，查询Sheet1表成绩大于80……姓名和成绩的记录    Set rst = cnn.Execute(strSQL)    'Execute()执行SQL语句，始终得到一个新的记录集rst    '以上是第三步，编写并执行SQL    '    Worksheets("结果表").Select '选中存放结果的工作表    Cells.ClearContents '清空值    For i = 0 To rst.Fields.Count - 1    '利用fields属性获取所有字段名，fields包含了当前记录有关的所有字段,fields.count得到字段的数量    '由于Fields.Count下标为0，又从0开始遍历，因此总数-1        Cells(1, i + 1) = rst.Fields(i).Name    Next    Range("a2").CopyFromRecordset rst    '使用单元格对象的CopyFromRecordset方法将rst内容复制到D2单元格为左上角的单元格区域    '以上是第四步，将SQL查询结果和字段名写入表格指定区域    '    cnn.Close '关闭链接    Set cnn = Nothing '释放内存End Sub

呵，总结一下：

对于新手而言，本章的重点是了解VBA执行SQL的操作过程，以及懂得复制第4节的代码执行SQL语句，仅此而已，至于其它部分，看过就算了，看一眼，留个印象，以后再见面好说话就行。

示例文件下载，百度网盘▼
https://pan.baidu.com/s/1CrquOygTiRuHh185l4-cxA
提取码: 9y67

⏩需要系统学习Excel，却找不到优质教程？学习Excel的过程中遇到疑难问题，却找不到人及时作出解答？

加入我的付费社群，学习+训练+答疑，与6000+在线会员一起，同微软最有价值专家MVP同行，全面精进Excel之道

🚂>>~

加入我的付费会员，全面学习Excel

透视表函数图表 VBA PQ想学啥学啥

👀

本文由公众号“Excel星球”首发。

点击阅读原文系统学习Excel！

http://mp.weixin.qq.com/s?__biz=MzI0MTAxNTc5NA==&mid=2468675987&idx=2&sn=1d2f400f1a598b65ec08dee43598f6f2

Excel星球

微软全球最有价值专家(Excel MVP)，上千篇原创图文和视频教程随学随用，随用随查，建议常用Excel的职场人关注。

最新文章

学会套用这7个基础函数公式，解决86%的数据统计求和问题

VIP会员学习资料全目录，函数、透视表、VBA、PQ、图表、SQL…

简单介绍一下TOROW/TOCOL函数

Excel终身会员：一次付费，学习问题永久答疑，学习资料永久共享……

连代码调试都一窍不通，还谈啥会VBA？

零基础学习Python第21课：字符串合并与拆分

JSA常用小代码合集

全面学习Excel：函数、透视表、VBA、PQ、图表、SQL想学啥就学啥……

推荐WPS一个很好用的函数：SheetsName

Excel终身会员：一次付费，学习问题永久答疑，学习资料永久共享……

如果这四个函数都不会，就不要再说会Excel了

SQL入门教程第07课：基础条件查询

给大家推荐一个冷门又实用的功能：自定义视图

全面学习Excel：函数、透视表、VBA、PQ、图表、SQL想学啥就学啥……

一文详解单元格自定义格式

VIP会员学习资料全目录，函数、透视表、VBA、PQ、图表、SQL…

很多朋友会在单元格中写公式，却用不好条件格式自定义规则？

全面学习Excel：函数、透视表、VBA、PQ、图表、SQL想学啥就学啥……

圆环图这么做，也可以很漂亮~

Excel如何制作动态模糊匹配的下拉菜单？

大家做的都是柱形图，为啥你的那么丑？

Excel终身会员：一次付费，学习问题永久答疑，学习资料永久共享……

12个典型案例丨数据查询必备XLOOKUP函数

全面学习Excel：函数、透视表、VBA、PQ、图表、SQL想学啥就学啥……

VLOOKUP最典型的五种用法

VIP会员学习资料全目录，函数、透视表、VBA、PQ、图表、SQL…

实话实说，这个函数的神奇功能，连VBA都羡慕！

超实用，VBA常用脚本代码合集

Excel最智能的一个快捷键，居然很多朋友还不知道…

HYPERLINK函数的8种用法，都会的算你牛

Excel终身会员：一次付费，学习问题永久答疑，学习资料永久共享……

=A2&B2，如此简单的数据合并公式，为什么结果会出错？

VBA到底应不应该手工释放变量内存？

最后一天，我的Excel VIP 会员优惠活动

关于单元格自定义格式，看这一篇教程就够了

做图很难？学会这一个函数，直接搞定六种常用图表~

最后两天，我的Excel VIP 会员国庆优惠活动

SQL入门教程第06课：排序及技巧应用

我的Excel VIP 会员国庆优惠活动

SQL入门教程第05课：DISTINCT去重

SQL入门教程第04课：什么是表

我的Excel VIP 会员国庆优惠活动