Microsoft KB Archive/304149

= ACC2002: How to Bind a Microsoft Access PivotTable List to OLAP Data Sources =

Article ID: 304149

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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
 Open the sample database Northwind.mdb. Create a new form in Design view. On the Insert menu click ActiveX Control, click Microsoft Office PivotTable 10.0, and then click OK. On the View menu, click Properties. In the property sheet of the PivotTable control, click the Other tab, and then type PivotTable in the Name property. Save the form as frmPivotTable, and then close it.</li> On the Insert menu, click Module. This starts the Visual Basic Editor and inserts a new, blank module.</li> On the Tools menu, click References.</li> Click Browse.</li> 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

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

BindPivotTableToOLAP

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

<div class="references_section">