ACC2002: How to Bind a Microsoft Access PivotTable List to OLAP Data Sources (304149)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304149
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

In Microsoft Access 2002, you can view tables, queries, views, functions, and forms in PivotTable view. This is possible because Access 2002 natively hosts the Microsoft Office Web Components. This article shows you how to use the Office Web Components object model to build a PivotTable view in a Microsoft Access form that is connected to an OLAP data source. Method 1 shows you how to do this by using the user interface. Method 2 shows you how to do this by using Visual Basic for Applications code.

MORE INFORMATION

The following methods demonstrate how to bind a Microsoft Office PivotTable 10.0 control to an OLAP multidimensional cube. These examples require the MSOLAP OLEDB provider on the SQL Server computer with the FoodMart or FoodMart 2000 OLAP database. The MSOLAP OLEDB provider is installed when you install OLAP client components from the Microsoft SQL Server 7.0 or later CD.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Method 1

  1. Open the sample database Northwind.mdb.
  2. Create a new form in Design view.
  3. On the Insert menu, click ActiveX Control, click Microsoft Office PivotTable 10.0, and then click OK.
  4. On the View menu, click Properties.
  5. In the property sheet for the PivotTable control, click the Other tab, and then click the Build (...) button next to the Custom property.
  6. Click Connection, and then click Edit. This starts the Data Link Properties dialog box.
  7. On the Provider tab, click Microsoft OLEDB Provider for OLAP Services, and then click Next.
  8. In the Data Source box, type the name of the OLAP server.
  9. Type the appropriate authentication information.
  10. Click the name of the OLAP data store in the dropdown box.
  11. Click OK to close the Data Link Properties dialog box.
  12. In the Office PivotTable Properties dialog box, click Sales in the Data member, table, view, or cubename box.
  13. Click OK to close the Office PivotTable Properties dialog box.
  14. View the PivotTable field list. Drag the fields that you want to create a PivotTable view that is based on the OLAP cube.

Method 2

  1. Open the sample database Northwind.mdb.
  2. Create a new form in Design view.
  3. On the Insert menu click ActiveX Control, click Microsoft Office PivotTable 10.0, and then click OK.
  4. On the View menu, click Properties.
  5. In the property sheet of the PivotTable control, click the Other tab, and then type PivotTable in the Name property.
  6. Save the form as frmPivotTable, and then close it.
  7. On the Insert menu, click Module. This starts the Visual Basic Editor and inserts a new, blank module.
  8. On the Tools menu, click References.
  9. Click Browse.
  10. Browse to and select the following file, and then click Open. This adds a reference to the Microsoft Office XP Web Components library.

    C:\Program Files\Common Files\Microsoft Shared\Web Components\10\OWC10.DLL

  11. Click OK to close the References dialog box.
  12. Add the following Visual Basic for Applications (VBA) code to the new module:
    Sub BindPivotTableToOLAP()
    Dim frm As Access.Form
    DoCmd.OpenForm "frmPivotTable", acFormPivotTable
        
    Set frm = Forms("frmPivotTable")
    
    Dim pTable As OWC10.PivotTable
    Set pTable = frm.PivotTable
    
    If pTable.ConnectionString = "" Then
        strConnect = "Provider=MSOLAP.2;Integrated Security=SSPI;Data Source=<server name>;Initial Catalog=FoodMart 2000"
        pTable.ConnectionString = strConnect
            
       'Set DataMember property to the name of the OLAP
       'cube. In this case, you are using Sales from Foodmart.
        pTable.DataMember = "Sales"
    End If
End Sub
  13. On the View menu, click Immediate Window to open the Immediate window.
  14. Type the following line into the Immediate window, and then press ENTER:

    BindPivotTableToOLAP

  15. On the File menu, click Close and Return to Microsoft Access.
  16. View the PivotTable field list. Drag the fields that you want to create a PivotTable view that is based on the OLAP cube.

REFERENCES

For more information about the PivotTable object model, see the "Microsoft Office Web Components Visual Basic Reference" Help file, Owcvba10.chm. This Help file is located in the language-specific folder at the following location:

C:\Program Files\Common Files\Microsoft Shared\Web Components\10

For example, if you are using the English version of Microsoft Office XP, this Help file is in the 1033 folder:

C:\Program Files\Common Files\Microsoft Shared\Web Components\10\1033

Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto KB304149
©2004 Microsoft Corporation. All rights reserved.