Recordset 属性 - Microsoft Excel Visual Basic 2002 参考

返回介绍

Recordset 属性

发布于 2019-08-07 字数 2206 浏览 858 评论 0

返回或设置一个 Recordset 对象,该对象作为指定查询表的数据源。


说明


如果用该属性来覆盖一个已经存在的记录集,当 Refresh 方法运行之后,改动才会起作用。


示例


本示例对用于第一张查询表(第一张工作表上)的 Recordset 对象进行更改,然后刷新该查询表。


With Worksheets(1).QueryTables(1)
.Recordset = _
OpenDatabase("c:Nwind.mdb") _
.OpenRecordset("employees")
.Refresh
End With

本示例在活动工作表的 A3 单元格上,通过连接到 Microsoft Jet 上的 ADO 创建一个新的数据透视表高速缓存,然后再基于该高速缓存新建一个数据透视表。


Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
‘ Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:perfdaterecord.mdb"
End With
‘ Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With
‘ Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
‘ Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With
With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With
‘ Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

支持 Markdown 语法,需要帮助?

目前还没有任何评论,快来抢沙发吧!