Sample Application: PIVOT

PIVOT drafts Microsoft Excel's pivot table feature into serving as a reporting engine for Access. Many companies that I work with use Microsoft Excel as a database reporting engine because doing so prevents data from running into a dead end on the paper trail. Instead of (or in addition to) delivering paper reports, these companies deliver "live" reports in Microsoft Excel. This allows users who are more familiar with spreadsheets than relational databases to analyze data, roll it up into summary form or expand it into full detail, chart it, link it to Word for publishing purposes, and in general, extend and expand the data's useful business life.

PIVOT has two components:

An Access database, PIVOT.MDB, which serves as the main application controlling Microsoft Excel
A Microsoft Excel add-in, Pivot.XLA, which controls creation of the pivot table

The database, PIVOT.MDB, contains three related tables that store data on inventory: Categories, Products, and Suppliers. It also contains functions that summarize the inventory data in a Microsoft Excel pivot table by product category and by the supplier's geographic zone. For example, the pivot table lets you see at a glance the value of your inventory in beverages or the dollar amount that comes from European suppliers. Even though the Access crosstab query summarizes data in much the same way, it doesn't let you manipulate that data in any way, which is why it's worthwhile to use a Microsoft Excel pivot table.

When you open Pivot.MDB, the application uses an AutoExec macro to display the Pivot toolbar, which has three buttons: Show, Close, and Crosstab. Click on the Show button to create a pivot table; click on Close to close the pivot table (and Microsoft Excel, too, if it wasn't running when you created the pivot table); and click on Crosstab to run the crosstab query so that you can compare it with the pivot table.

PIVOT has three main routines, which I explain in the following sections:

The CreatePIVOTTable function uses OLE automation to launch Microsoft Excel (if necessary), record current display settings, and then use Pivot.XLA to create a pivot table.
Pivot.XLA uses ODBC to retrieve data from Access into the PivotTableWizard method.
The ClosePIVOTTable function closes the pivot table. It also closes Microsoft Excel if Microsoft Excel wasn't running when the user created the pivot table.

Product Sheet: PIVOT
Purpose:	PIVOT uses a Microsoft Excel pivot table to summarize inventory data stored in an Access database. It demonstrates how to use OLE automation and ODBC to turn Microsoft Excel into a reporting engine for database applications. Such applications generally include a front end for entering data and a set of tools for reporting on that data. PIVOT uses Microsoft Excel as one of those tools.
Development tools used:	Access, Microsoft Excel, Windows API, OLE Automation, ODBC
How PIVOT works:	To run PIVOT: 1. Place Pivot.MDB and Pivot.XLA in the same directory, and then register Pivot.MDB with the ODBC driver manager. 2. When you open Pivot.MDB, specify shared, not exclusive, access. If Pivot.MDB isn't shared, Microsoft Excel can't use ODBC to query the database and retrieve data for the pivot table. 3. To create the pivot table, click the Show button on the Pivot toolbar. 4. Use the Close button on the Pivot toolbar to close the pivot table. This command closes Microsoft Excel if it wasn't open when you first created the pivot table; otherwise, it closes only the workbook used for the pivot table. This command also restores Microsoft Excel's original display settings.