MDX query to OLAP from .adp client

G

Guest

I have a SQL Server 2000 with a SQL server database and an OLAP cube in Analysis server, both on the same machine. I need to access both the OLAP qube and the SQL database from my Access forms. The MDX query for the OLAP Cube is quite simple, but I cannot make it work. I have tried to use principles described in:

Retrieve an ADOMD Cellset as an ADODB Recordset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q21336

Please find my code below. The client VBA code displays the number of records (the cube cells are successfully loaded into the adodb.recordset from the olap cube), but my form refuse to accept adoRS (ADODB.Recordset) as its source recordset. I get the following error: The Object you entered is not a valid recordset property

The comments indicate where the error occurs. Any help will be appreciated. Here is the code that fails
'----------------------------------------------------------------------------------------------------------
Option Compare Databas
Option Explici

Private Sub Form_Open(Cancel As Integer
Const strConnect = "Data Source=dfjdt50j;PROVIDER=MSOLAP;INITIAL CATALOG=TeleMarketing
On Error GoTo ErrorHandle
Dim objAdoData As New ADOBusOb
Dim adoRS As New ADODB.Recordse

With objAdoDat
.SQL = "Select "
& " non empty{[TMD_Products].[All TMD_Products].[Datatjenester].[Bedriftsnett] }on columns, "
& " non empty{[TMD_KIDs].[Kid].members} on rows "
& " from TMC_Products "
& " where [Measures].[AntallAb]
.ADOConnect strConnect, 20 'Establish connection
End Wit

'adoRS.CursorLocation = adUseClien
'adoRS.CursorType = adOpenStati
Set adoRS = objAdoData.GetR

MsgBox adoRS.RecordCount 'Works perfectly, displays number of record
Set Me.Recordset = adoRS '!!!!!Get error: The object you entered is not a valid recordset propert

Exit Su
ErrorHandler
MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object
Exit Su
End Su
'-------------------------------------------------------------------------------------------------------------------------

Regard

Tor
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Buncha guesses:

You may wish to see this MS KB article (Disconnected Recordsets):

http://support.microsoft.com/default.aspx?scid=kb;en-us;184397

and you may want to set the form's Recordset to the Recordset object
returned by the ADOBusObj.dll, instead of setting it to an
intermediary Recordset object (adoRS). I.e.,

This:

Set Me.Recordset = objAdoData.GetRs

instead of this:

Set adoRS = objAdoData.GetRs
...
Set Me.Recordset = adoRS


It may be that the form's Recordset cannot use the returned RS
directly, but, using VBA, you can iterate thru the RS & load the
form's controls. You'll notice that the examples in the MS KB
articles only show the RS results in a Debug.Print rs.Field(i).Value
way.


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQE69lYechKqOuFEgEQIOKQCghHdxlZIHvJWLq+9oKW2SJu0By74AoI/7
I20CWWWenia48YGQ9z6dKxav
=Zvqe
-----END PGP SIGNATURE-----


I have a SQL Server 2000 with a SQL server database and an OLAP cube in Analysis server, both on the same machine. I need to access both the OLAP qube and the SQL database from my Access forms. The MDX query for the OLAP Cube is quite simple, but I cannot make it work. I have tried to use principles described in:

Retrieve an ADOMD Cellset as an ADODB Recordset:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q213364

Please find my code below. The client VBA code displays the number of records (the cube cells are successfully loaded into the adodb.recordset from the olap cube), but my form refuse to accept adoRS (ADODB.Recordset) as its source recordset. I get the following error: The Object you entered is not a valid recordset property.

The comments indicate where the error occurs. Any help will be appreciated. Here is the code that fails:
'-----------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
Const strConnect = "Data Source=dfjdt50j;PROVIDER=MSOLAP;INITIAL CATALOG=TeleMarketing"
On Error GoTo ErrorHandler
Dim objAdoData As New ADOBusObj
Dim adoRS As New ADODB.Recordset

With objAdoData
.SQL = "Select " _
& " non empty{[TMD_Products].[All TMD_Products].[Datatjenester].[Bedriftsnett] }on columns, " _
& " non empty{[TMD_KIDs].[Kid].members} on rows " _
& " from TMC_Products " _
& " where [Measures].[AntallAb] "
.ADOConnect strConnect, 20 'Establish connection.
End With

'adoRS.CursorLocation = adUseClient
'adoRS.CursorType = adOpenStatic
Set adoRS = objAdoData.GetRs

MsgBox adoRS.RecordCount 'Works perfectly, displays number of records
Set Me.Recordset = adoRS '!!!!!Get error: The object you entered is not a valid recordset property

Exit Sub
ErrorHandler:
MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object"
Exit Sub
End Sub
'--------------------------------------------------------------------------------------------------------------------------
Click to expand...
 
G

Guest

Your suggestion on how to set the Me.recordset property did not work for me, same error response
Thank you anyway

Tore G.
 
Top