VBA中练习ADO：ActiveX Data Object
前期绑定，要先添加引用---"Microsoft ActiveX Data Objects 6.1"
ADO学习的权威参考可点击：w3school
ADO简单理解：是几个ActiveX对象（类），用于数据(Data)访问。
Connection：ADO Connection 对象用于创建一个到达某个数据源的开放连接。通过此连接，可以对一个数据库进行访问和操作。
Command：ADO Command 对象用于执行面向数据库的一次简单查询。此查询可执行诸如增删查改等动作。
Command 对象的主要特性是有能力使用存储查询和带有参数的存储过程。
Recrodset:ADO Recordset 对象用于容纳一个来自数据库表的记录集。一个 Recordset 对象由记录和列（字段）组成。
在 ADO 中，此对象是最重要且最常用于对数据库的数据进行操作的对象。
(三个对象的使用场景可先跳到本文末尾看一下留个印象)
案例：ado.xlsm的[Sheet1]工作表中有一些数据，文件的保存路径：C:\Users\stone\Desktop\ado.xlsm
ado方式访问excle数据，无论表格是否打开状态，都可读取、修改数据，还是很有用的。
一、执行非查询语句。（增、删、改）
执行非查询，一般目的是直接对数据库操作，不返回结果。不需要使用Recordset对象。
1、Connection：使用connection执行一个非查询
1 Sub AdoCnn()
2 Dim cnn As New ADODB.Connection
3 '定义cnn属性
4 cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
5 '定义连接字符串
6 cnn.ConnectionString = "Data Source=C:\Users\stone\Desktop\ado.xlsm;Extended Properties=""Excel 12.0"";"
7 cnn.Mode = adModeReadWrite
8 '打开连接
9 cnn.Open
10 Dim sql As String
11 'sql脚本
12 'sql = "update [Sheet1$] set 年龄=年龄-10 where 姓名=""张三""" '改
13 sql = "insert into [Sheet1$] values(""王七"",50)" '增
14 '执行语句
15 Dim count As Integer
16 cnn.Execute sql, count, adCmdText
17
18 Debug.Print "共影响了" & count & "行"
19 cnn.Close
20 End Sub
Function Execute(CommandText As String, [RecordsAffected], [Options As Long = -1]) As Recordset
Connection对象的Execute方法有三个参数:
1、CommandText：要执行的 SQL 语句、表名称、存储过程、URL 或提供者特有的文本。
2、RecordsAffected：返回受影响的行数(像一个输出参数)。
3、Options：指示如何设置计算 commandtext 参数。可以是一个或多个 CommandTypeEnum 或 ExecuteOptionEnum 值。
默认adCmdUnspecified（不指定命令类型的参数）。
adCmdText：指示CommandText是sql命令
adCmdStoreProc：指示CommandText是存储过程名
4、它的返回值是Recordset对象，始终是只读的、仅向前的游标。(见下文“执行查询语句”部分)
(本例中执行的是“非查询语句”，没有使用返回值。)
2、Command：使用command对象执行一个带有"参数"的sql非查询语句(sql语句的参数化)
注：参数化，是防止SQL注入的有效方法。
1 Sub AdoCmd()
2 Dim str As String
3 str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\stone\Desktop\ado.xlsm;Extended Properties=""Excel 12.0"";"
4 Dim cnn As New ADODB.Connection
5 '在open方法中，给连接字符串赋值
6 cnn.Open str
7 Dim sql As String
8 'sql脚本,在T-SQL语法中，用"@变量名"表示变量，这种在Sql语句中使用变量的方法叫做：参数化
9 sql = "update [Sheet1$] set 年龄=年龄-10 where 姓名=@name"
10 Dim cmd As New ADODB.Command
11 cmd.CommandText = sql
12 cmd.ActiveConnection = cnn
13 '给sql语句中使用到的参数赋值
14 cmd.Parameters("@name").Value = "张三"
15 cmd.Execute
16
17 ' '另一种参数化的方法，替换掉上面13-15行
18 'Dim p As New ADODB.Parameter
19 ' p.Name = "@name"
20 ' p.Value = "张三"
21 'Dim count As Integer
22 'cmd.Execute count, p, adCmdText
23 ' '输出受影响的行数
24 'Debug.Print count
25 cnn.Close
26 End Sub
Function Execute([RecordsAffected], [Parameters], [Options As Long = -1]) As Recordset
1、第一参数返回受影响的行数（输出参数）
2、第二参数传入需要使用的Parameter。(代码第22行)本例中参数只有一个@name，直接使用了Parameter。
3、第二参数如果有多个可以使用Array(p1,p2...)：cmd.Execute count, Array(p1, p2),adCmdText
二、执行查询语句：
执行查询语句，一般都要对查询结果进行处理,比如在查询得到的记录集的基础中修改数据，以至达到更新修改源数据的目的。先转载一个抄来的一段对RecordSet的描述：原文参考请点
Recordset对象的作用是由数据库返回记录集。根据查询结果返回一个包含所查询数据的记录集。
因为删除、更新、添加操作不需要返回记录集，因此可以直接使用连接对象或是命令对象的Exexut方法，但是利用记录集对象有时会更简单
此外，通过记录集对象能够实现比较复杂的数据库管理任务。
Recordset对象可以用来代表表中的记录，可以把记录集看成是一张虚拟的表格，包含一条或多条记录（行），每条记录包含一个或多个字段
但任何时候只有一条记录为当前记录。
1、使用Connection对象或Command对象的Execte方法取得Recordset，得到的是仅向前的、只读的记录集，无法修改更新。
通过下面的测试可以说明，重点看CursorType和LockType两个属性：
1 '测试Connection的Execute方法得到记录集
2 Sub AdoCnn2()
3 Dim cnn As New ADODB.Connection
4 cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
5 cnn.ConnectionString = "Data Source=C:\Users\stone\Desktop\ado.xlsm;Extended Properties=""Excel 12.0"";"
6 cnn.Open
7 Dim sql As String
8 sql = "select * from [Sheet1$]"
9 '定义记录集对象，接收查询结果
10 Dim rst As New ADODB.Recordset
11 Set rst = cnn.Execute(sql)
12 Debug.Print rst.State
13 '输出游标类型
14 Debug.Print rst.CursorType
15 '输出锁定类型
16 Debug.Print rst.LockType
17 rst.Close
18 cnn.Close
19 End Sub
20
21 '测试Command的Execute方法得到记录集
22 Sub AdoCmd2()
23 Dim str As String
24 str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\stone\Desktop\ado.xlsm;Extended Properties=""Excel 12.0"";"
25 Dim cnn As New ADODB.Connection
26 cnn.ConnectionString = str
27 cnn.Open
28 Dim sql As String
29 sql = "select * from [Sheet1$]"
30 Dim cmd As New ADODB.Command
31 cmd.CommandText = sql
32 cmd.ActiveConnection = cnn
33 Dim rst As New ADODB.Recordset
34 Set rst = cmd.Execute
35
36 Debug.Print rst.State
37 Debug.Print rst.CursorType
38 Debug.Print rst.LockType
39 rst.Close
40 cnn.Close
41 End Sub
上面分别测试了Connect和Command对象的Execute方法得到Recordset，其中的CursorType和LockType属性的值见下表（抱歉再次抄袭了大师的表）：
|
CursorType参数值
|
||
|
参数
|
值
|
说明
|
| AdOpenForwardOnly | 0 | 向前指针，默认值。只能用MoveNext方法或GetRows方法向前单向移动指针，所耗系统资源最少，执行速度也最快，但很多属性和方法将不能用 |
| AdOpenKeyset | 1 | 键集指针，记录集中可以前后移动。某一用户修改数据后，其他用户可以立即显示，但禁止查看其他用户添加和删除的记录 |
| AdOpenDynamic | 2 | 动态指针，记录集中可以前后移动。所有修改会立即在其他客户端显示，功能强大，但所耗系统资源也多 |
| AdOpenStatic | 3 | 静态指针，记录集中可以前后移动。所有修改不会在其他客户端显示 |
|
表6-18 LockType参数值
|
||
|
参数
|
值
|
说明
|
| AdLockReadOnly | 1 | 只读，默认值，适用于仅浏览数据 |
| AdLockPessimistic | 2 | 只能同时被一个用户所修改，修改时锁定，完毕解锁 |
| AdLockOptimistic | 3 | 可以同时被多个用户所修改，直到用update方法更新记录才锁定 |
| AdLockBatchOptimistic | 4 | 数据可以被修改，且不锁定其他用户，指定数据成批更新 |
测试结论：Connect和Command对象的Execute方法，得到的RecordSet记录集的属性是AdOpenForwardOnly(仅向前)、AdLockReadOnly（只读）。
2、Recordset对象：如果有在记录集的基础上修改数据的需求时，就要使用RecordSet对象的方法来实现：
1 Sub AdoRst()
2 Dim cnn As New ADODB.Connection
3 cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
4 cnn.ConnectionString = "Data Source=C:\Users\stone\Desktop\ado.xlsm;Extended Properties=""Excel 12.0"";"
5 cnn.Open
6 Dim sql As String
7 sql = "select * from [Sheet1$]"
8 Dim rst As New ADODB.Recordset
9 '使用rst的Open方法取得记录
10 rst.Open sql, cnn, adOpenStatic, adLockPessimistic
11 '输出记录集的内容
12 PrintRecordset rst
13 '游标移动到第一条记录
14 rst.MoveFirst
15 '执行记录集的修改方法，修改当前游标位置的记录(现在是修改第一条记录)
16 rst.Update "姓名", "王麻子"
17 rst.Update "年龄", "88"
18 ' '上面的Update方法也可以使用下面的方式来替代
19 ' rst(0).Value = "张三"
20 ' rst(1).Value = 66
21 ' rst.Update
22 Debug.Print "华丽的分割线-----------------"
23 '重新输出修改后的记录
24 PrintRecordset rst
25 rst.Close
26 cnn.Close
27 End Sub
28
29 '输出记录集的内容
30 Function PrintRecordset(rst As ADODB.Recordset)
31 '定义记录集字段类型
32 Dim fd As ADODB.Field
33 Dim s As String
34 '输出记录集的字段名，本例中只有两个字段分别是:姓名、年龄
35 Debug.Print rst.Fields(0).Name & " " & rst.Fields(1).Name
36 Do
37 s = ""
38 For Each fd In rst.Fields
39 '当前游标位置，对应的字段的值
40 s = s & fd.Value & " "
41 Next
42 Debug.Print s
43 '游标向前移动一次
44 rst.MoveNext
45 '当游标已经到了记录集的末尾时停止
46 Loop Until rst.EOF
47 End Function
48
49 Function PrintRecordset2(rst As ADODB.Recordset)
50 Dim fd As ADODB.Field
51 Dim s As String
52 Debug.Print rst(0).Name & " " & rst(1).Name
53 s = ""
54 Do
55 s = rst(0) & " " & rst(1)
56 Debug.Print s
57 rst.MoveNext
58 Loop Until rst.EOF
59 End Function
输出结果的内容：(可以看到第一条记录已经修改，并且update方法的作用会使修改结果立即更新到数据库中)
姓名 年龄
张三 32
李四 20
华丽的分割线-----------------
姓名 年龄
王麻子 88
李四 20
RecordSet对象非常强大，提供了对记录集操作的许多方法，比如添加AddNew、删除Delete、更新Update等：
注释：并非所有的提供者（providers）支持 Recordset 对象的所有方法和属性。
|
表6-23 Recordset记录集对象的方法
|
|
|
方法
|
说明
|
| Open | 打开记录集 |
| Close | 关闭记录集 |
| Requery | 重新打开记录集 |
| Move | 指针移至指定记录 |
| MoveFirst | 指针移至第一条记录 |
| MoveLast | 指针移至最后一条记录 |
| MovePrevious | 指针移至上一条记录 |
| MoveNext | 指针移至下一条记录 |
| Find | 搜索一个 Recordset 中满足指定某个条件的一条记录 |
| AddNew | 添加记录 |
| Delete | 删除记录 |
| Update | 更新记录 |
| CancelUpdate | 取消更新 |
| UpdateBatch | 把所有 Recordset 中的更改存入数据库。请在批更新模式中使用 |
| GetRows | 从记录集得到多行记录 |
| GetString | 将 Recordset 作为字符串返回 |
| Resync | 与数据库服务器同步更新 |
| Save | 把 Recordset 对象保存到 file 或 Stream 对象中 |
GetRows方法：Function GetRows([Rows As Long = -1], [Start], [Fields])
第一参数用于设置要返回多少条记录，默认为-1，也就是全部记录。把多条记录从一个 Recordset 对象中拷贝到一个二维数组中，第一维表示列，第二维表示行。该方法得到的数据是行列倒置的。
rst.MoveFirst
Dim arr
arr = rst.GetRows
AddNew方法：总是在记录集的最后面添加一条记录
1 '第一种方式
2 '添加新记录到记录集，并直接更新到数据源
3 rst.AddNew Array("姓名", "年龄"), Array("王二", 18)
4
5 '第二种方式
6 '添加记录
7 rst.AddNew
8 rst("姓名") = "张飞"
9 rst("年龄") = 50
10 '将添加的新记录，更新到数据库
11 rst.Update
Delete方法：Excel表中数据似乎不能使用ADO方式删除数据
Save方法：
rst.MoveFirst
rst.Save "C:\Users\stone\Desktop\ado.xml", adPersistXML
如果把记录集一次性写入单元格，可使用单元格Range对象的CopyFromRecordset方法，例如：
rst.MoveFirst
ActiveCell.CopyFromRecordset rst
最后总结一下Connection、Command、RecordSet三个重要对象的使用情景：
0、三个对象是结合在一起使用的
1、对数据源做一次性的简单更新操作(update、delete、insert)时，可选择使用[Connection||Command].Execute,不需要Recordset
2、读取(Select)但不更新原数据，使用 Set RecordsetObj = [Connection||Command].Execute，此时记录集是仅向前的、只读的。
3、在第1-2的情景中，如果在Sql命令中加入参数化形式(避免sql注入漏洞)，要使用“Command+参数化的SQL”。Connection单干不行。
4、对数据源做复杂的更新操作时，“[Connection&command]+SQL脚本”不能胜任，把数据取出来放到Recordset里，借用记录集对象的一套丰富的方法，先更改记录集再把更新同步至数据源显然非常的灵活方便。
关于连接自符串：
连接字符串是很难去记住的一项内容，但如果字符串写不对，就好像有豪车没有钥匙，白瞎了。可以使用Excel程序自带的功能来助记。
1、使用“数据选项卡”-->“现有连接”（Alt+A+X）-->“浏览更多”-->找到要连接的文件类型，比如*.xlsm
注：如果选择连接的文件是access文件，也可以得到与之对应的连接字符串。
2、在打开要连接文件的“连接属性中”，可以找到需要的连接信息（不用愁Provider、Extended Properties的拼写了）：
3、想要ADO连接excel文件，复制上面的文本，改一下：Extended Properties="Excel 8.0"(Excel2003)或"Excel 12.0"(Excel2007以后) ，8.0、12.0两个数字而已，就比较好记了。
