The user's selection from the SnapShot Manager form should invoke a single paramaterized stored procedure call on the server.
The types of snapshot result sets that are brought down to a SnapShot form should be carefully thought out. The application should be designed so the user needs to take additional snapshots infrequently. For example, if the users are regional managers, then a snapshot might contain all data for a particular region, so that a user need only take one snapshot to get all his or her pertinent data.
Performance should also be considered. Taking the SnapShot can often be a lengthy step, since it involves a server-based query, data retrieval, and the creation of a new OLE object. In general, the SnapShot result set should be about 100 rows, with 1000 rows as an upper limit. Although Microsoft Excel can handle >30,000 rows easily, the length of time it takes to place that amount of rows in a worksheet via OLE is usually unacceptable.
Users will quickly learn the SnapShot concept, and be able to understand how to make the application perform best for their needs.
Be creative with the SnapShot filtering and drill-down selection mechanism. Outline controls, such as the TList custom control from Bennet-Tec, are a good way of allowing the user to select from a hierarchy.
Once the SnapShot is selected, a child SnapShot form is created to manipulate and present the result set to the user. The SnapShot form creates a Microsoft Excel object through OLE automation and loads the result set into a pivot table. Note that we are using automation to manipulate the pivot table, and this does not require the use of the OLE custom control (MSOLE2.VBX).
Microsoft Excel pivot tables allow the Visual Basic application to manipulate the result set without executing another SQL statement or requiring complex Visual Basic calculation code. Pivot tables are much more flexible and faster than SQL. The pivot table object model is extremely powerful, and is documented very well in the Office Developer's Kit (ODK).
This architecture provides a lot of the benefits of emerging multidimensional on-line analytical processing (OLAP) database servers with a mainstream product, Microsoft Excel. Instead of storing data in a proprietary format on the server, it is stored in a standard relational format.
The most difficult part of using pivot tables through Visual Basic is setting them up with data. The VBEXCEL library takes care of this for you.
I personally do not like placing any source code in Microsoft Excel Visual Basic for Applications or using Microsoft Excel Visual Basic for Applications exclusively as the front-end (bypassing Visual Basic version 3.0 entirely). I prefer the tight control over the client environment and application distribution that Visual Basic version 3.0 provides. Microsoft Excel applications are often accidentally broken by end-users doing things they are not supposed to. Microsoft Excel does not provide a good environment for team development.
There are several bugs when using Visual Basic version 3.0 and OLE Automation. You should check the TechNet CD or Microsoft Knowledge Base for the latest list. One of the more notorious is a GPF that will eventually occur when using nested OLE "dot" calls. For example, the call:
xlMyWorkSheet.Range(Cells(3,4), (Cells(5,6))
should be replaced with the equivalent, but less elegant:
xlMyWorkSheet.Range("C4:E6")
The way you present the snapshot data on the Visual Basic child form will vary from application to application— it should be customized to your user's requirements. It should take advantage of the flexibility of the pivot table, and allow the user to rotate data, drill down, change row/column order, etc.
It should allow the user to view the data in both table and chart form, and allow the user to view both on screen and printer. Graphing VBX tools include First Impression by Visual Tools and ChartFX by Software FX. Table tools include Formula One by Visual Tools and Spread/VBX by Farpoint Technologies.
Unfortunately, the most obvious choice, Microsoft Excel itself, is not suitable. In order to display a dynamic Microsoft Excel table or chart with a Visual Basic version 3.0 container, you need to use OLE in-place activation. In-place activation, however, doesn't work as advertised right now with Visual Basic version 3.0 and Microsoft Excel. For example, Visual Basic version 3.0 has very little control over the visual characteristics of the workbook and a nasty bug causes Alt-Tab to lock the system. Microsoft has indicated on CompuServe that these bugs in the MSOLE2.VBX control will probably not be fixed until Visual Basic version 4.0.