09-Excel VBA 学习总结 - 通用ADO数据访问模型-五星文库
免费文档下载
09-Excel VBA 学习总结 - 通用ADO数据访问模型
导读：ExcelVBA学习总结-通用ADO数据访问模型，ADO是基于OLEDB的数据访问技术，它不直接与数据交互，它提供了编程语言和统一数据访问方式，它允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心数据库的连接，所以基本上任何语言都可以使用这种数据访问技术，一、ADO对象模型，很多情况下只需要这三个对象即可完成数据的读取和操作，下面是各个对象/集合的简略说明：Connecti
Excel VBA 学习总结 - 通用ADO数据访问模型
ADO是基于OLE DB的数据访问技术。它不直接与数据交互，而是把这个任务交给了OLE DB，这么做带来了相当好的可扩展性和适应性。它提供了编程语言和统一数据访问方式；它允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心数据库的连接。由于ADO是基于COM实现的，所以基本上任何语言都可以使用这种数据访问技术，VBA也不例外。
一、ADO对象模型
ADO对象库中主要有9个对象，即：Connection、Command、Recordset、Record、Field、Error、Property、Parameter和Stream。呈现的形式基本是5大可以独立创建的基本对象，4大对象集合，如下图所示。
这9个对象中以Connection、Command、Recordset是最为常用的，很多情况下只需要这三个对象即可完成数据的读取和操作，对于一些很简单的应用，甚至使用它们中的任何一个就可以独立完成任务。下面是各个对象/集合的简略说明： Connection：代表与数据源的连接与操作环境，基本任何的操作都是针对特定的Connection完成的。
常用属性：ConnectionString(最重要)，ConnectionTimeout，State(连接的状态)，Errors
常用方法：Open，Execute，Close
常用事件：ExecuteComplete，ConnectComplete
虽然Command对象与Recordset对象都可以在需要的时候自己隐式的创建一个Connection对象，但是对于需要多次执行命令或查询的场景，还是需要提供一个公共的Connection对象(这个对象的创建与销毁都是需要时间的)来共用。而且对于同一个连接字符串，ADO会采用连接池(存放Connection对象)的方式减少资源的浪费。
ConnectionString中主要需要设置Provider，Data Source，Initial Catalog，User ID，Password，Integrated Security等值，这些大家应该都很熟悉了。当然有些值(像Provider，Mode这种)也可以在Connection的相关属性中设置。
Connection需要先执行Open方法打开后，才能Execute一些命令，最后需要使用Close关闭(通常为了保险起见，需要先检查State，再关闭)以释放资源。 Command：代表执行的添加、修改，删除、查询数据源的命令。
常用属性：ActiveConnection(设置连接字符串)，CommandText(核心属性)，CommandType，Parameters(一般是存储过程的参数)
常用方法：CreateParameter，Execute(可以有返回值)。
Recordset：代表执行查询命令后的结果集。
常用属性：ActiveConnection(设置连接字符串)，BOF，EOF，CursorLocation，Filter，Sort，State，Fields(返回数据的类型)
常用方法：Open，Close，Move，MoveFirst，MoveNext，Find，NextRecordset
通常配合Range对象的CopyFromRecordset方法获取数据，但是这个方法只能获得值，一般需要匹配Fields属性获取列信息。
如果需要精确控制每行每列的值，可以使用RecordCount，Fields以循环的形式获取结果集中的每个值。
For i = 1 To rst.RecordCount
For j = 0 To rst.Fields.Count - 1
Sheet1.Cells(i + 1, j + 1) = rst.Fields(j)
rst.MoveNext
Next i 如果返回的结果集为空，则BOF与EOF都为True。通常它们也可以配合Fields精确控制结果集中的每个值。
Find方法基本上是支持与SQL中Where语句基本相同的语法。
Error与Errors：代表与数据源相关的操作的详细错误信息，Errors是Connection对象的属性。
Parameter与Parameters：代表基于参数化查询或存储过程的 Command 对象相关联的参数或自变量，Parameters是Command对象的属性。
Field与Fields：代表使用普通数据类型的数据的列，Fields是Recordset对象的属性。
Property与Properties： 代表ADO 对象的描述或控制对象的行为，分为内置属性(通过对象直接调用)和动态属性(通过集合使用 MyObject.Properties(0) 或 MyObject.Properties(&Name&) 语法来引用)。
Record：代表记录集中的一行、或文件系统的一个文件或一个目录。 Stream：用于读写以及处理二进制数据或文本流。
二、ADO编程模型
ADO 的目标是访问、编辑和更新数据源，而编程模型体现了为完成该目标所必需的系列动作的顺序。ADO提供类和对象完成以下活动：
? 连接到数据源 (Connection对象)，并可选择开始一个事务。
可选择创建对象来表示 SQL 命令 (Command)。
可选择在 SQL 命令中指定列、表和值作为变量参数 (Parameter)。
? 执行命令 (使用Command、Connection 或 Recordset对象完成)。
? 如果命令按行返回，则将行存储在缓存中 (使用Recordset对象)。
可选择创建缓存视图，以便能对数据进行排序、筛选和定位 (使用R
ecordset对象)。
? 通过添加、删除或更改行和列编辑数据 (使用Recordset对象)。
在适当情况下，使用缓存中的更改内容来更新数据源 (使用Records
? 如果使用了事务，则可以接受或拒绝在完成事务期间所作的更改；结
束事务 (Connection)。
? 释放相关对象(通常是把对象设为Nothing，别忘了Set关键字)。
不管是否显式的使用了Connection对象，这个对象在整个访问数据库的过程中，是始终存在的。
其实除了连接字符串以及数据库特有的一些特性(比如SqlServer支持存储过程)外，访问各种数据库的基本流程和处理的语句，包括SQL语句都是差不多的。
三、ADO实践
ADO对象创建
对于COM对象的创建方式，大家应该很熟悉了，有两种方式：
后期绑定：使用CreateObject方法。 Dim cnn As Object, rst As Object
Set cnn = CreateObject(&ADODB.Connection&)
Set rst = CreateObject(&ADODB.Recordset&)
前期绑定：先引用“Microsoft ActiveX Data Objects 2.x Library”(尽量选择高版本)，然后直接就可以使用了。下面两种写法都可以：
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection
Dim cnn2 As New ADODB.Connection
前期绑定能更好的利用VBE(或者说是VBIDE)的Intellisense。
使用ADO访问各种类型的数据库，基本上除了连接字符串不同，专有特性不同，使用ADO的其它过程基本都差不多，所以下面几种类型数据文件的操作，重点都是介绍各自不同的地方。
使用ADO访问Access中的数据
连接字符串示例：
'Provider和Data Source是必须的
'Mode控制访问数据库方式，本例中是排他访问
Public Const AccessConnection As String = _
&Provider= Microsoft.ACE.OLEDB.12.0;& & _
&Data Source=C:\Files\Northwind 2007.& & _
&Mode=Share E& & _
包含总结汇报、资格考试、考试资料、教学教材、word文档、专业文献、应用文书、党团工作以及09-Excel VBA 学习总结 - 通用ADO数据访问模型等内容。本文共3页
相关内容搜索Microsoft Office应用
EXCEL中的CopyFromRecordset这个方法怎么使用呢
我想用vbscript写一个.vbs文件，操作一些EXCEL文件
但是到把记录集写到EXCEL中这一步遇到问题，如果将recordset中的内容一项项写到表中用的时间太长。网上说有
Workbook.Worksheets.Range有一个方法CopyFromRecordset可以把记录集中的内容写到某个工作表中
但是我使用的时候报错：
错误：&被调用的对象已与其客户端断开连接
conn.Open&"provider=microsoft.jet.oledb.4.0;extended&properties=excel&8.0;data&source="&FilePath
rs.Open&"Select&*&from&[Sheet1$]",conn,1,3
exlWorkbook1.Worksheets(1).Range("A1").CopyFromRecordset&rs
exlWorkbook1是文件1，FilePath是另外一个XLS文件2，我将文件2中的内容进行SQL操作，结果在rs中，现在第三句的时候报以上错误
-----------------------------
是不是我使用上有问题？应该怎样正确使用呢
exlWorkbook1.Worksheets(1).Range("A1").CopyFromRecordset&rs.execute(sql)
呃，少些一段，应该这样
conn.Open&"provider=microsoft.jet.oledb.4.0;extended&properties=excel&8.0;data&source="&FilePath
sql&=&"Select&*&from&[Sheet1$]"
exlWorkbook1.Worksheets(1).Range("A1").CopyFromRecordset&rs.execute(sql)
引用&1&楼&laoyebin&的回复:试试这样
exlWorkbook1.Worksheets(1).Range("A1").CopyFromRecordset&rs.execute(sql)
我试了一下，还是有一样的那个报错
那个文件是xls还是xlsx文件？
引用&4&楼&laoyebin&的回复:那个文件是xls还是xlsx文件？
是.xls文件
那就不清楚错误在哪了，能不能传源文件？
我试了一个,如果在&文件1&中做查询,然后将结果复制到&文件2&中,这样用CopyFromRecordset是没问题的
但我现在做的操作是在&文件1&中做查询,结果也在复制到&文件1&中,
conn.Open&"provider=microsoft.jet.oledb.4.0;extended&properties=excel&8.0;data&source="&FilePath
exlApp.Workbooks.Open(FilePath)
这两个FilePath是同一个文件,似乎是这样操作的时候会有我前面说的那个报错
我知道原因了.
"被调用的对象已与其客户端断开连接",这个是因为对象被关闭后又对它进行了操作
由于我当时conn.open和workbook.open打开的是同一个文件,有冲突所以关闭了前面打开的工作簿,后面才会出错的
回复
即使是一小步也想与你分享您所在位置： &
&nbsp&&nbsp&nbsp&&nbsp
Excel VBA_编程难点_版主高手实例集锦.doc107页
本文档一共被下载：
次 ,您可全文免费在线阅读后下载本文档。
文档加载中...广告还剩秒
需要金币：150 &&
Excel VBA_编程难点_版主高手实例集锦
你可能关注的文档：
··········
··········
，常用的、带解释的 VBA 短句
by:yuhongpu
http://club.excelhome.net/dispbbs.asp?boardID 2&ID 169024&page 1&px 0
VBA起步]常用的、带解释的 VBA 短句
[A65536].End xlUp .Row 'A列末行向上第一个有值的行数
[A1].End xlDown .Row 'A列首行向下第一个有值之行数
[IV1].End xlToLeft .Column '第一行末列向左第一列有数值之列数。
[A1].End xlToRight .Column '第一行首列向右有连续值的末列之列数
mandBars "Standard" .Controls 2 .BeginGroup True '在常用工具栏的第二个按钮前插入分隔符
Cells.WrapText
False '取消自动换行 If Len Target
5 Then '如果当前单元格中的字符数超过5个,执行下一行 Target.WrapText
True '自动换行 End If
[A1:B10].SpecialCells xlCellTypeBlanks .Rows.Hidden
True '有空格即隐藏行
[A2].parent.name '返回活动单元格的工作表名
[A2].parent.parent.name '返回活动单元格的工作簿名
如下代码可使工作簿打开后30秒 或闲置30秒 内不输入、不重新选择等,自动关闭工作簿
Private Sub Workbook_Open '工作簿打开事件 tt '工作簿打开时启动 tt 过程
Private Sub Workbook_SheetChange ByVal Sh As Object, ByVal Target As Range
'工作表变化事件 tt '工作表中任一单元格有变化时启动 tt 过程
Private Sub Workbook_SheetSelectionChange ByVal Sh As Object, ByVal Target As Range
'工作表选择变化事件 tt '工作表中单元格的选择有变化时启动 tt 过程
Sub tt 'tt 过程 Dim myNow As Date, BL As Integer '定义myNow为日期型;定义BL为长整型 myNow
Now '把当前的时间赋给变量myNow Do '开始循环语句Do BL
Second Now
- Second myNow '循环中不断检查变量BL的值 If BL
30 Then GoTo Cl '当BL 30即跳转到CL DoEvents '转让控制权,以便sheets可继续操作 Loop Until BL
30 '当BL 30即跳出循环 Exit Sub
Cl: Application.EnableEvents
False '避免引起其他事件 ActiveWor
正在加载中，请稍后...Copy specific fields from recordset to excel specific columns - Access World Forums
Remember Me?
Show Threads
Show Posts
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Copy specific fields from recordset to excel specific columns
i looked around the internet and this forum to find a solution but was unable to do so. so here is the question:
i make this SQL based recordset and then want to copy specific field data into a specific excel column ( say for example i have this field called Sender_Name in the recordset, i want to copy all names from this field into a specific column in excel).
xlws.columns(1)=rst.fields(0)
where xlst would be the excel worksheet i am copying to, and the rst = recordset. but the code would return only the first record in the field and copy all that in column one of excel all the way...say the first record in the recordset in field(0) had the value = &john&...it would only copy John to column 1 but not the rest of the names....
any help would be appreciated.
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 28,869
Thanked 3,226 Times in 3,172 Posts
Re: Copy specific fields from recordset to excel specific columns
Look at CopyFromRecordset in Excel VBA help, or you'll need to loop through your recordset and insert one value at a time (using a variable for the row that you increment each pass).
__________________
Microsoft Access MVP
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
thanks for the reply
the sad thing with copyfromrecordset, i have tried it before, is that it doesn't support Automation. e.g:
xlsheet.columns(1).copyfromrecordset recordset.fields(0)
i get an error telling me you can't Automate it, ie, i can't tell the procedure to only copy that specific field. unless there is another way or something
and with looping, can you show me a sample code.
thanks again
Administrator
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanked 297 Times in 205 Posts
Re: Copy specific fields from recordset to excel specific columns
Create the recordset with only one field in it then refer to that in your CopyFromRecordset command.
.CopyFromRecordset Rs
__________________
David Crake
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. The Home of Simple Software Solutions.
O/S Windows XP (SP3)
& Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)
Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
lol i thought about that once myself, but that just kills the whole purpose of 'automation' and programming. i'm nearly doing everything myself and it becomes really inconvenient ...the true thing is i want to find a code that will prevent me from doing that, couz with it other problems could rise, where you could have in the final spreadsheet, unmatching records...i'm thinking
but anyhow thanks for the suggestion mate
still waiting to get some help on the loop idea.
Former Moderator
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,482
Thanks: 94
Thanked 1,780 Times in 1,565 Posts
Re: Copy specific fields from recordset to excel specific columns
Originally Posted by teasugar
the sad thing with copyfromrecordset, i have tried it before, is that it doesn't support Automation.
Umm, yes it does. But you have to use it in the correct way. Your code below is completely incorrect
xlsheet.columns(1).copyfromrecordset recordset.fields(0)
is NOT the way to do it. The correct way will only take the entire recordset and put it into the worksheet. The correct way is (using Excel COM code from Access):
Dim rst As DAO.Recordset
Dim objXL As Object
Set rst = CurrentDb.OpenRecordset (&Select SingleFieldNameHere FROM TableOrQueryNameHere WHERE SomeField = & & Me!SomeFieldOnTheForm)
Set objXL = CreateObject(&Excel.Application&)
With objXL
.Visible = True
.Workbooks.Open(&C:\YourPathAndFileName.xls&)
.Activesheet.Range(&C2&).CopyFromRecordset rst
.ActiveWorkbook.Close True
Set objXL = Nothing
Set rst = Nothing
__________________
Free tools, code and samples here:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
thanks bob, what you showing me is what dcrake is suggesting. looks like thats my only way - making about 30 recordset for the massive no. fields i have
Administrator
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanked 297 Times in 205 Posts
Re: Copy specific fields from recordset to excel specific columns
Which is exactly what I advocated in my previous post Bob.
__________________
David Crake
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. The Home of Simple Software Solutions.
O/S Windows XP (SP3)
& Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)
Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
Former Moderator
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,482
Thanks: 94
Thanked 1,780 Times in 1,565 Posts
Re: Copy specific fields from recordset to excel specific columns
Originally Posted by teasugar
lol i thought about that once myself, but that just kills the whole purpose of 'automation' and programming.
No, not really because you can set up a function which does the actual export based on parameters you pass.
So you, in essence need to set it up differently than you might expect but that doesn't mean that automation is not there.
So, how do you define which columns you want from your recordset going into which columns in the Excel file?
If you aren't doing a bulk output then CopyFromRecordset isn't necessarily the right tool for the job.
i'm nearly doing everything myself and it becomes really inconvenient ...the true thing is i want to find a code that will prevent me from doing that, couz with it other problems could rise, where you could have in the final spreadsheet, unmatching records...i'm thinking
That's too broad of a statement for reality.
So, my question would still be - how do you define which columns of an existing recordset go to which columns of the Excel spreadsheet.
So, you have to define it somewhere, so you can define it by parameters you pass a function.
As for looping it would simply be something like this:
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim lngCount As Long
Set objXL = CreateObject(&Excel.Application&)
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open(&C:\YourFileAndPathToExistingFile.xls&)
Set xlWS = xlWB.Worksheets(&SheetNameHere&)
Set rst = CurrentDb.OpenRecordset(&Select * FROM TableOrQueryNameHere&)
lngCount = 1
Do Until .EOF
xlWS.Range(&A& & lngCount).Value = rst!FieldNameHere
xlWS.Range(&C& & lngCount).Value = rst!Field2NameHere
xlWS.Range(&F& & lngCount).Value = rst!Field3NameHere
' and so on
lngCount = lngCount + 1
Set rst = Nothing
xlWB.Close True
ObjXL.Quit
Set ObjXL = Nothing
__________________
Free tools, code and samples here:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Former Moderator
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,482
Thanks: 94
Thanked 1,780 Times in 1,565 Posts
Re: Copy specific fields from recordset to excel specific columns
Originally Posted by DCrake
Which is exactly what I advocated in my previous post Bob.
And I just expanded upon it with a full set of code to better show the syntax and usage.
__________________
Free tools, code and samples here:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
So, how do you define which columns you want from your recordset going into which columns in the Excel file? If you aren't doing a bulk output then CopyFromRecordset isn't necessarily the right tool for the job.
thats about half of my problem, and thats why you were seeing me using the recordset.field(x) method over in my first code you rejected...i wanna to refer to only speficic fields in the recordsets and copy that, but with copyfromrecordset you can't specify fields and thats why i meant it can't automate.
thanks for the loop, i'll look into it
Newly Registered User
Join Date: Dec 2010
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
the loop did the job, i'm very thankful to the suggestion and to its expansion by bob. just with the code bob, you forgot a last 'end with' statement.
and when i wanted to refer to a specific cell i used this method:
to past [field1] starting from D3 and [field2] from E2, i did the following, correct me if there is a more appropriate method:
lngcount =3 ' changed it to my value
lng = 2 'introduced another variable already above
Do Until .EOF
xlWS.Range(&D& & lngCount).Value = rst!field1
xlWS.Range(&E& & lng).Value = rst!field2
' and so on
lngCount = lngCount + 1
Former Moderator
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,482
Thanks: 94
Thanked 1,780 Times in 1,565 Posts
Re: Copy specific fields from recordset to excel specific columns
I was working on a sample on how to do it all with copy from recordset but it is really complex.
So, I'll just say good that you have the loop going and we'll let it go at that.
__________________
Free tools, code and samples here:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Newly Registered User
Join Date: Jun 2012
Thanked 0 Times in 0 Posts
Re: Copy specific fields from recordset to excel specific columns
Little bit of a timewarp but if someone else wanders to this post there are additional parameters that can be input to the 'CopyFromRecordset' method.
The first one is unknown, the others are described below:
outputrange.CopyFromRecordset rs, {max rows}, {max columns}
i.e. outputrange.CopyFromRecordset rs, 4, 1
I believe the first column is zero so in the example above the first 2 fields of the first 4 rec useful if the data you need is in the first/second field.
Forum Jump
User Control Panel
Private Messages
Subscriptions
Who's Online
Search Forums
Forums Home
Access World
Access World News
Site Suggestions
Introduce Yourself
The Watercooler
Microsoft Access Discussion
Modules & VBA
Theory and practice of database design
Access Web
Microsoft Access Reference
Access FAQs
Code Repository
Sample Databases
Microsoft Access Tutorials
Microsoft Access User Groups
Apps and Windows
SQL Server
Crystal Reports
Visual Basic
Web Design and Development
ASP and ASP.NET
PHP & MySQL
Other Software
Hardware Questions and Answers
Non-Access Issues
Politics & Current Events
Sports, Health & Fitness
Small Business
Similar Threads
Modules & VBA
07-06-2010
08-31-2009
Modules & VBA
02-27-2008
08-28-2006
10-07-2005
All times are GMT -8. The time now is .
Powered by vBulletin& Copyright &2000 - 2016, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World