ADOConnection Property [Excel 2003 VBA Language Reference]

  • Article

Returns an ADO connection object if the PivotTable cache is connected to an OLE DB data source. The ADOConnection property exposes Microsoft Excel's connection to the data provider allowing the user to write code within the context of the same session that Excel is using with ADO (relational source) or ADOMD (OLAP source). Read-only.

expression.ADOConnection

expression Required. An expression that returns one a PivotCache object.

Remarks

The ADOConnection property is available only for sessions where the data source is an OLE DB data source. When there is no ADO session the query will result in a run-time error.

The ADOConnection property can be used for any OLEDB-based cache with ADO. The ADO connection object can be used with ADOMD for finding information about OLAP Cubes on which the cache is based.

Example

This example sets an ADODB Connection object to the ADOConnection property of the PivotTable cache. The example assumes a PivotTable report exists on the active worksheet.

Sub UseADOConnection()

    Dim ptOne As PivotTable
    Dim cmdOne As New ADODB.Command
    Dim cfOne As CubeField

    Set ptOne = Sheet1.PivotTables(1)
    ptOne.PivotCache.MaintainConnection = True
    Set cmdOne.ActiveConnection = ptOne.PivotCache.ADOConnection

    ptOne.PivotCache.MakeConnection

    ' Create a set.
    cmdOne.CommandText = "Create Set [Warehouse].[My Set] as '{[Product].[All Products].Children}'"
    cmdOne.CommandType = adCmdUnknown
    cmdOne.Execute

    ' Add a set to the CubeField.
    Set cfOne = ptOne.CubeFields.AddSet("My Set", "My Set")

End Sub

This example adds a calculated member, assuming a PivotTable report exists on the active worksheet.

Sub AddMember()

    Dim cmd As New ADODB.Command

    If Not ActiveSheet.PivotTables(1).PivotCache.IsConnected Then
        ActiveSheet.PivotTables(1).PivotCache.MakeConnection
    End If

    Set cmd.ActiveConnection = ActiveSheet.PivotTables(1).PivotCache.ADOConnection

    ' Add a calculated member.
    cmd.CommandText = "CREATE MEMBER [Warehouse].[Product].[All Products].[Drink and Non-Consumable] AS '[Product].[All Products].[Drink] + [Product].[All Products].[Non-Consumable]'"
    cmd.CommandType = adCmdUnknown
    cmd.Execute

    ActiveSheet.PivotTables(1).PivotCache.Refresh

End Sub

Applies to | PivotCache Object