HOWTO: Retrieve an ADOMD Cellset As an ADODB Recordset

• Microsoft Visual Basic Enterprise Edition for Windows, version 6.0
• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

The ActiveX Data Objects Multi-Dimensional Library (ADOMD) 2.1 documentation ("Using ADO with ADO MD") indicates that you can access a multidimensional data provider using ADOMD cellsets or ADODB recordsets. The operation of flattening a dataset to produce a rowset can be done by using ADODB instead of ADOMD. Since ADODB tries to get a rowset, the MSOLAP OLE DB provider gives a flattened rowset.

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 OLAP database. The MSOLAP OLEDB provider is installed when you install OLAP client components from the SQL Server 7.0 CD.

Paste the following code into a new Visual Basic ActiveX DLL Project class. Set a reference to the ADO 2.1 Object 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 

Option Explicit
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 

REFERENCES

SQL Server 7 Books Online 'Building OLAP Services Applications'.
SQL Server 7 Books Online 'Installing OLAP Services'.
For more information about SQL Server 7.0 and OLAP Services see - SQL7 and OLAP Information

Additional query words: kbDSupport kbAdo

Keywords : kbADO kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto