《09-ExcelVBA学习总结-通用ADO数据访问模型》由会员分享，可在线阅读，更多相关《09-ExcelVBA学习总结-通用ADO数据访问模型（10页珍藏版）》请在金锄头文库上搜索。
1、Excel VBA 学习总结 - 通用 ADO 数据访问模型ADO 是基于 OLE DB 的数据访问技术。它不直接与数据交互，而是把这个任务交给了 OLE DB，这么做带来了相当好的可扩展性和适应性。它提供了编程语言和统一数据访问方式；它允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心数据库的连接。由于 ADO 是基于 COM 实现的，所以基本上任何语言都可以使用这种数据访问技术，VBA 也不例外。一、ADO 对象模型ADO 对象库中主要有 9 个对象，即：Connection、Command 、Recordset、Record 、Field、Error 、Propert
2、y 、Parameter 和 Stream。呈现的形式基本是 5 大可以独立创建的基本对象，4 大对象集合，如下图所示。这 9 个对象中以 Connection、Command、Recordset 是最为常用的，很多情况下只需要这三个对象即可完成数据的读取和操作，对于一些很简单的应用，甚至使用它们中的任何一个就可以独立完成任务。下面是各个对象/集合的简略说明：Connection：代表与数据源的连接与操作环境，基本任何的操作都是针对特定的 Connection 完成的。常用属性：ConnectionString( 最重要)，ConnectionTimeout，State( 连接的状态)，Err
3、ors常用方法：Open，Execute，Close常用事件：ExecuteComplete，ConnectComplete虽然 Command 对象与 Recordset 对象都可以在需要的时候自己隐式的创建一个 Connection 对象，但是对于需要多次执行命令或查询的场景，还是需要提供一个公共的 Connection 对象(这个对象的创建与销毁都是需要时间的) 来共用。而且对于同一个连接字符串，ADO 会采用连接池(存放 Connection 对象) 的方式减少资源的浪费。ConnectionString 中主要需要设置 Provider，Data Source，Initial Cat
4、alog，User ID，Password，Integrated Security 等值，这些大家应该都很熟悉了。当然有些值(像 Provider，Mode 这种)也可以在 Connection 的相关属性中设置。Connection 需要先执行 Open 方法打开后，才能 Execute 一些命令，最后需要使用 Close 关闭( 通常为了保险起见，需要先检查 State，再关闭)以释放资源。Command：代表执行的添加、修改，删除、查询数据源的命令。常用属性：ActiveConnection(设置连接字符串)，CommandText( 核心属性) ，CommandType，Paramet
5、ers( 一般是存储过程的参数)常用方法：CreateParameter ，Execute(可以有返回值) 。Recordset：代表执行查询命令后的结果集。常用属性：ActiveConnection(设置连接字符串)，BOF，EOF ，CursorLocation，Filter，Sort，State，Fields( 返回数据的类型)常用方法：Open，Close，Move ，MoveFirst ，MoveNext，Find，NextRecordset通常配合 Range 对象的 CopyFromRecordset 方法获取数据，但是这个方法只能获得值，一般需要匹配 Fields 属性获取列信
6、息。如果需要精确控制每行每列的值，可以使用 RecordCount，Fields 以循环的形式获取结果集中的每个值。For i = 1 To rst.RecordCountFor j = 0 To rst.Fields.Count - 1Sheet1.Cells(i + 1, j + 1) = rst.Fields(j)Next jrst.MoveNextNext i如果返回的结果集为空，则 BOF 与 EOF 都为 True。通常它们也可以配合 Fields 精确控制结果集中的每个值。Find 方法基本上是支持与 SQL 中 Where 语句基本相同的语法。Error 与 Errors：代表
7、与数据源相关的操作的详细错误信息，Errors 是 Connection 对象的属性。Parameter 与 Parameters：代表基于参数化查询或存储过程的 Command 对象相关联的参数或自变量，Parameters 是 Command 对象的属性。Field 与 Fields：代表使用普通数据类型的数据的列，Fields 是 Recordset 对象的属性。Property 与 Properties： 代表 ADO 对象的描述或控制对象的行为，分为内置属性(通过对象直接调用)和动态属性(通过集合使用 MyObject.Properties(0) 或 MyObject.Propert
8、ies(Name) 语法来引用)。Record：代表记录集中的一行、或文件系统的一个文件或一个目录。Stream：用于读写以及处理二进制数据或文本流。二、ADO 编程模型ADO 的目标是访问、编辑和更新数据源，而编程模型体现了为完成该目标所必需的系列动作的顺序。ADO 提供类和对象完成以下活动： 连接到数据源 (Connection 对象)，并可选择开始一个事务。 可选择创建对象来表示 SQL 命令 (Command)。 可选择在 SQL 命令中指定列、表和值作为变量参数 (Parameter)。 执行命令 (使用 Command、Connection 或 Recordset 对象完成)。 如
9、果命令按行返回，则将行存储在缓存中 (使用 Recordset 对象)。 可选择创建缓存视图，以便能对数据进行排序、筛选和定位 (使用 Recordset 对象 )。 通过添加、删除或更改行和列编辑数据 (使用 Recordset 对象)。 在适当情况下，使用缓存中的更改内容来更新数据源 (使用 Recordset 对象)。 如果使用了事务，则可以接受或拒绝在完成事务期间所作的更改；结束事务 (Connection)。 释放相关对象(通常是把对象设为 Nothing，别忘了 Set 关键字) 。 不管是否显式的使用了 Connection 对象，这个对象在整个访问数据库的过程中，是始终存在的。
10、其实除了连接字符串以及数据库特有的一些特性(比如 SqlServer 支持存储过程)外，访问各种数据库的基本流程和处理的语句，包括 SQL 语句都是差不多的。三、ADO 实践ADO 对象创建 对于 COM 对象的创建方式，大家应该很熟悉了，有两种方式：后期绑定：使用 CreateObject 方法。Dim cnn As Object, rst As ObjectSet cnn = CreateObject(ADODB.Connection)Set rst = CreateObject(ADODB.Recordset)前期绑定：先引用“Microsoft ActiveX Data Objects
11、 2.x Library”(尽量选择高版本)，然后直接就可以使用了。下面两种写法都可以：Dim cnn1 As ADODB.ConnectionSet cnn1 = New ADODB.ConnectionDim cnn2 As New ADODB.Connection前期绑定能更好的利用 VBE(或者说是 VBIDE)的 Intellisense。使用 ADO 访问各种类型的数据库，基本上除了连接字符串不同，专有特性不同，使用 ADO 的其它过程基本都差不多，所以下面几种类型数据文件的操作，重点都是介绍各自不同的地方。使用 ADO 访问 Access 中的数据连接字符串示例：Provider
12、 和 Data Source 是必须的Mode 控制访问数据库方式，本例中是排他访问Public Const AccessConnection As String = _Provider= Microsoft.ACE.OLEDB.12.0; & _Data Source=C:FilesNorthwind 2007.accdb; & _Mode=Share Exclusive; & _User ID=Admin; & _Password=password在本例中，使用的是 Access 2007，所以使用的 Provider 是“Microsoft.ACE.OLEDB.12.0”。下面是一个伪代
13、码例子，基本上涵盖了所有的步骤：Public Sub PlainTextQuery()省去了定义的部分sConnect = Provider=Microsoft.ACE.OLEDB.12.0; & _Data Source=C:FilesNorthwind 2007.accdb sSQL = SELECT.Set rsData = New ADODB.RecordsetrsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdTextIf Not rsData.EOF ThenSheet1.Range(A2).Cop
14、yFromRecordset rsDatarsData.CloseWith Sheet1.Range(A1:B1).Value = Array(Company, Contact Name).Font.Bold = TrueEnd With调整列宽Sheet1.UsedRange.EntireColumn.AutoFitElsersData.CloseMsgBox Error: No records returned., vbCriticalEnd If销毁对象Set rsData = NothingEnd Sub像上面所说那样，解决一个问题，可以使用不同的对象。这里仅使用 Recordset
15、就解决了所有的问题，但是当需要使用 Connection 或者 Command 对象的时候，毫不犹豫的使用它们。时刻谨记，对象的创建是需要开销的，当可以重用一个对象的时候(例如 Command 对象，当执行不同的命令时，只需要把 CommandText 换掉就可以了)，毫不犹豫的使用它。使用 Insert，Update，Delete 语句后，可以查询影响的行数来确定操作是否成功了，如果失败了，可以使用 Err.Raise 来引发一个错误。Access 中支持在数据库中产生和存储 SQL 语句，想了解更多的可以参看其它资料。SQL 语句中，可以使用 IIF 简单的进行一些替换处理。使用 ADO
16、访问 SqlServer 中的数据连接字符串示例：常见的两种 SqlServer 连接字符串形式Public Const SQLConnection1 As String = _Provider=SQLOLEDB; & _Data Source=ComputerNameSQLServerName; & _Initial Catalog=Northwind; & _User ID=User;Password=password; & _Network Library=dbmssocnPublic Const SQLConnection2 As String = _Provider=SQLOLEDB; & _Data Source=ComputerNameSQLServerName; & _Initial Catalog=Northwind; & _Integrated S
