Microsoft KB Archive/213364

= How To Retrieve an ADOMD Cellset as an ADODB Recordset =

Article ID: 213364

Article Last Modified on 7/2/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q213364



SUMMARY
The ActiveX Data Objects Multi-Dimensional Library (ADOMD) 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 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=;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

