Using the Recordset Object

Using the Recordset Object

You can use the Command and Recordset objects to retrieve data from a multidimensional source as well as from a tabular source. However, the data is returned in a flattened rowset. For more information about flattened rowsets, see Working with OLAP Data.

Examples

A. Using the Recordset Object

The following example retrieves a list of products and their sales from the Sales cube in the FoodMart 2000 database and places them in a Microsoft® FlexGrid control. The control is filled with the values retrieved by an SQL query using the Microsoft ActiveX® Data Objects (ADO) Connection and Recordset objects.

A connection to the FoodMart 2000 database is declared, using MSOLAP as a provider. The connection is then opened. Next, an SQL query is created inside a string variable. A Recordset object is then opened with this SQL query as its source.

A loop is then entered, which fills the FlexGrid control with the retrieved values until the end of the recordset is reached.

For more information about using the ADO Command and Recordset objects, see the ADO documentation. For more information about the FlexGrid control, see the Microsoft Visual Basic® documentation.

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim fld As ADODB.Field

    cn.ConnectionString = "Provider=MSOLAP; Datasource=LocalHost; Initial Catalog=FoodMart 2000"
    cn.Open

    sql = "Select [Product:Product Name],[Measures:Store Sales] from Sales"
    set rs.ActiveConnection = cn
    rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly
    MSFlexGrid1.Clear
    MSFlexGrid1.AddItem "Product" & Chr(9) & "Value"
    Do While (Not rs.EOF)
        MSFlexGrid1.AddItem rs.Fields(0).Value & Chr(9) & rs.Fields(1).Value
        rs.MoveNext
    Loop