How To Retrieve an ADOMD Cellset as an ADODB Recordset (213364)

MORE INFORMATION

The following code demonstrates using ADODB to retrieve a flattened rowset based on an OLAP multidimensional cube. This code sample requires the MSOLAP OLEDB provider on the SQL Server machine with the FoodMart or FoodMart 2000 OLAP database. The MSOLAP OLEDB provider is installed when you install OLAP client components from the SQL Server 7.0 or later CD.

Paste the following code into a new Visual Basic ActiveX DLL Project class. Set a reference to the Microsoft ActiveX Data Objects (version 2.0 or later) Library. Change the project name to ADOBusObj and then compile the application.

Option Explicit
Private strSQL As String
Private strConnect As String
Private adoCn As ADODB.Connection

Public Function GetRs() As ADODB.Recordset
    If Not adoCn Is Nothing Then
    Else
        Err.Raise vbObjectError + 98, "GetRs", "No valid Connection"
    End If

    Dim adoRs As ADODB.Recordset

    Set adoRs = New ADODB.Recordset
    With adoRs
        .CursorLocation = adUseClient
        .ActiveConnection = adoCn
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Open strSQL
    End With

    'disConnect the Recordset.
    Set adoRs.ActiveConnection = Nothing

    'return the Recordset
    Set GetRs = adoRs
End Function

Private Property Get ConnectStr() As String
    ConnectStr = strConnect
End Property

Private Property Let ConnectStr(strCn As String)
    strConnect = strCn
End Property

Public Property Get SQL() As String
    SQL = strSQL
End Property

Public Property Let SQL(nSQL As String)
    strSQL = nSQL
End Property

Public Sub ADOConnect(strConnect As String, Optional CmdTimeOut As Integer = 20)
    Set adoCn = New ADODB.Connection
    With adoCn
        .ConnectionString = strConnect
        .CursorLocation = adUseClient
        .CommandTimeout = CmdTimeOut
        .Open
    End With

    ConnectStr = adoCn
End Sub
				

Paste the following code into a new Visual Basic Standard EXE Project Form in the General Declarations section. Set a reference to the ActiveX Data Objects (version 2.0 or later) Library. Set a reference to the ActiveX ADOBusObj created above. Change the connection string and the SQL string to reflect your OLAP server's configuration.

Option Explicit
'For SQL 2000, use the FoodMart 2000 database.
Const strConnect = "Data Source=<DataSource>;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart"

Private Sub Form_Click()
    On Error GoTo ErrorHandler

    Dim adoRs As ADODB.Recordset
    Dim objAdoData As New ADOBusObj.Class1

    With objAdoData
        .SQL = "Select {[Measures].[Unit Sales]} on columns, " & _
                   "Non Empty [Store].[Store Name].members on rows From Sales"
        .ADOConnect strConnect, 20 'Establish connection.
    End With
    
    'Return the Resultset from Data Object.
    Set adoRs = objAdoData.GetRs
    
    Debug.Print adoRs.RecordCount
    
    While Not adoRs.EOF
        Debug.Print adoRs.Fields(0).Value
        adoRs.MoveNext
    Wend
    
    MsgBox "Success", vbOKOnly, "Data Object"
    Exit Sub
    
    ErrorHandler:
        MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object"
    Exit Sub
End Sub