21中文 Excel 2000 编程 24 学时教程.pdf

下载下载第21学时使用ADO访问数据在这之前，你已经访问了外部数据，但是是依靠录制的代码来实现外部数据访问的。如果用户不可选用MS Query加载宏时，该怎么办呢？如果要获得比使用 MS Query更多的控制和更好的性能时，该怎么办呢？那么就需要使用 A D O。这个学时的重点包括： 讨论什么是A D O ADO对象概述 怎样添加到A D O库的引用 使用A D O来导入数据到工作表中21.1 ADO是什么ActiveX Data Object（A c t i v e X数据对象，缩写为A D O ）能够让开发人员开发通过 O L ED B提供者来访问和操作数据的应用程序。我们不用使用数据透视表和 MS Query，而可以直接使用A D O同数据进行交互，这意味着只需要更少的系统开销，就能够对数据进行更多的控制。新术语OLE DB是一套Component Object Model（组件对象模型，缩写为C O M）接口，这套接口提供了访问存储在各种信息源中数据的统一接口。A D O的目的是获取对数据源的访问、编辑和更新，这意味着可以使用 A D O把数据导入到工作表中，然后更新数据，再把数据返回到表中。21.2 访问数据库的对象正如A D O这个名称所隐含的，A c t i v e X数据对象为开发人员在访问和操作数据时提供了一个可以使用的对象模型。在使用A D O这个模型时，将使用到下面这些关键的对象： C o n n e c t i o n（连接）对象这个对象代表了到达要使用的数据源的连接。有了到达数据源的连接以后，就能够对那个数据源执行命令了。 C o m m a n d（命令）对象这个对象代表命令，命令就是能够被数据源处理的查询和语句。 P a r a m e t e r（参数）对象这个对象需要同C o m m a n d对象一起使用，它代表的是命令的参数。 R e c o r d s e t（记录集）对象这个对象是A D O对象模型的真正核心。使用“记录集”对象来通过连接对数据进行操作。这个对象代表了来自表和查询结果的所有记录集合。 Field（字段）对象这个对象代表了“记录集”中的列。 E r r o r（错误）对象这个对象代表了从数据源返回的错误。包含 A D O对象的任何操作能够生成一个或者多个提供者错误。当每个错误发生时，就有一个或者多个“错误”对象放置到“连接”对象的E r r o r s集合中。21.3 使用ADO绝大多数E x c e l开发人员在使用A D O时有一个目的：从数据库获取数据导入到工作簿中。实现这个目的的基本步骤如下：1）建立到数据源的连接。2）获取对记录集的访问。3）从记录集得到记录。4）关闭到数据源的连接。21.4 添加到ADO库的引用在开始编写代码之前，需要创建一个到 A D O库的引用，使用下面的步骤来实现这一点：1）选择“工具” 、 “引用” ， “引用”对话框显示出来。2）找到Microsoft ActiveX Data Objects 2.0 Library复选框并选中它。3）单击“确定”按钮，对A D O库的引用就添加到应用程序中了。21.5 编写从数据库到工作表的数据因为编写从数据库到工作表的数据是绝大多数 E x c e l开发人员想做的关键事情之一，所以下面将开始这项工作来学习A D O。在新建的工作簿中，创建程序清单 2 1 - 1中给出的过程。程序清单21-1 GoGetProducts过程1: Sub GoGetProducts ()2: Dim rsProducts As ADODB.Recordset3: Set rsProducts = New ADODB.Recordset4: rsProducts.Open Source:= P r o d u c t s, _5: activeconnection:= Provider=Microsoft.Jet.OLEDB.4.0;Data Source= _6: C:Program FilesMicrosoft Off i c e O ff i c e S a m p l e s N o r t h w i n d . m d b, _7: CursorType:=adOpenStatic, _8: LockTy p e : = a d L o c k O p t i m i s t i c , _9: Options:=adCmdTa b l e10: With Wo r k s h e e t s (S h e e t 1)11: .Range(A 1) . C u r r e n t R e g i o n . C l e a r代码中的长语句分为几行是出于本书每行长度的限制。笔者建议你在自己的过程中输入虚线作为一个长行。12: Application.Intersect(.Range(.Rows(1),. R o w s ( r s P r o d u c t s . R e c o r d C o u n t ) ) ,. R a n g e ( . C o l u m n s ( 1 ) , . C o l u m n s ( r s P r o d u c t s . F i e l d s . C o u n t ) ) ) .Value = Tr a n s p o s e A r r a y ( r s P r o d u c t s . G e t R o w s( r s P r o d u c t s . R e c o r d C o u n t ) )13: End Wi t h1 4 :15: rsProducts.Close16: End Sub这个过程从一开始就使用记录集对象，现在，我们来看一看记录集对象的 O p e n方法。在第21学时 使用 ADO访问数据使用177下载这个示例中，它的 S o u r c e参数设置为表的名称， a c t i v e c o n n e c t i o n参数的值提供了必要的连接信息。现在连接的是叫做N o r t h w i n d的A c c e s s数据库。Dim rsProducts As ADODB.RecordsetSet rsProducts = New ADODB.RecordsetrsProducts.Open Source:= P r o d u c t s, _activeconnection:= Provider=Microsoft.Jet.OLEDB.4.0; Data Source= _C:Program FilesMicrosoft Off i c e O ff i c e S a m p l e s N o r t h w i n d . m d b, _C u r s o r Ty p e : = a d O p e n S t a t i c , _L o c k Type:=adLockOptimistic, _O p t i o n s : = a d C m d Ta b l e一旦能够访问记录集以后，就可以把记录集中的数据导入到工作表中，这时可以看到一个稍微有点儿奇怪的现象： A D O记录集中的数据是采用二维表形式来组织存放的，不过却是用每列来代表记录集中的一个记录，每行代表一个字段，这跟通常的表现形式不一样。实际上，仍然可以行列颠倒过来看待数据就跟通常的一样了。正因为这样，所以在把记录集中的记录复制到工作表之前，需要把记录集的行列互换，让每行代表一个记录，每列代表一个字段。如果你是一个经验丰富的 E x c e l用户，可能会想到使用E x c e l的Tr a n s p o s e函数。这样做可能产生的问题在于对于大量的数组， Tr a n s p o s e函数可能导致失败。为了彻底解决行列互换的问题，下面将创建自己的 Tr a n s p o s e函数版本，如程序清单2 1 - 2中所示。在G o G e t P r o d u c t s过程中，首先清空了单元格 A 1所在的当前区域，然后结合使用了I n t e r s e c t方法和自己版本的Tr a n s p o s e过程（M y Tr a n s p o s e） 。I n t e r s e c t方法返回了一个代表两个或者更多个区域矩形交集的 R a n g e（区域）对象，使用这个方法是因为想要在记录集所代表的数组和工作表上区域之间创建一个交集：With Wo r k s h e e t s (S h e e t 1). R a n g e (A 1) . C u r r e n t R e g i o n . C l e a rApplication.Intersect(.Range(.Rows(1), .Rows(rsProducts.RecordCount),_.Range(.Columns(1), .Columns( r s P r o d u c t s . F i e l d s . C o u n t ) ) ) . Value = _M y Tr a n s p o s e ( r s P r o d u c t s . G e t R o w s( r s P r o d u c t s . R e c o r d C o u n t )End Wi t h这个过程完成的最后一件事情是关闭到记录集的连接：rsProducts. Close早先，笔者提到过需要创建一个你自己的 Tr a n s p o s e函数版本。请继续学习，输入这个过程的代码。程序清单21-2 MyTr a n s p o s e函数1: Function MyTranspose (ByRef ArrayOriginal As Variant)As Va r i a n t2: Dim x As Integer3: Dim y As Integer4: Dim i As Integer5: Dim j As Integer6: Dim ArrayTranspose () As Va r i a n t7 :178使用中文Excel 2000编程 2 4学时教程下载8: x = UBound(ArrayOriginal, 1)9: y = UBound(ArrayOriginal, 2)1 0 :11: ReDim ArrayTr a n s p o s e ( y, x )1 2 :13: For i = 0 To x14: For j = 0 To y15: ArrayTranspose (j, i) = ArrayOriginal (i, j)16: Next17: Next1 8 :19: MyTranspose = ArrayTr a n s p o s e2 0 :21: End Function这个函数所做的工作就是把一个二维数组的行元素和列元素互换。现在准备运行G o G e t P r o d u c t s过程，切换到工作簿中，运行该过程。在很短的时间内，数据就导入到工作簿中，如图2 1 - 1所示。21.6 学时小结在这个学时中，我们快速介绍了 A D O概念的总体情况，然后，举例说明了怎样使用 A D O把数据从表导入到你自己的工作表中。在下一个学时中，将建立 A D O的知识体系，将创建一个用户窗体，这个用户窗体能够允许用户浏览和更新记录。21.7 专家答疑问题：使用A D O只能够访问A c c e s s这一种数据库格式吗？解答：实际上，可以访问好几种其他格式的数据库，其中包括 SQL Server和F o x P r o。第21学时 使用 ADO访问数据使用179下载图21-1 使用A D O把数据导入到工作表中问题：记录集总是表吗？解答：不是，记录集也可以基于一个命令，也就是说，记录集可以是一个查询的结果集。21.8 课外作业思考题和练习题是为了使你能够进一步地理解所学内容，答案请参考附录。21.8.1 思考题1）O p e n方法的哪个参数用来传递用作记录集的对象的名称？2）判断题：要通过A D O去访问数据的话，只要编写合适的代码就可以了。3）哪个对象存储了有关在使用A D O时所发生的问题的信息？4）判断题：使用A D O只能够访问Microsoft Access数据库中的数据。5）哪个对象代表了通过A D O来使用的那些记录？6）怎样终止到A D O数据源的连接？7）请说出使用A D O的四个步骤。21.8.2 练习题创建一个带有 3个选项按钮的用户窗体，选项按钮的标题分别是 S u p p l i e r s、 P r o d u c t s、O r d e r s。编写必要的代码，使得用户能够选择从什么表把数据导入到用户的工作簿中去。180使用中文Excel 2000编程 2 4学时教程下载