Many software components are involved in retrieving data from a database and presenting it in a useful report format. The components can range from proprietary database drivers and ODBC (open database connectivity) DLLs to the software used to display and print the report. Each component introduces its own level of complexity and detailed knowledge requirements. The techniques presented in this chapter hide this complexity by providing three distinct components: a business object, the control application, and the report output medium.
By using this object-based approach, you reduce data retrieval to the mechanics of deciding which components should obtain the data and control the report output production. Of the three components available, the control application and the report output medium are best suited for controlling report production. The developer then decides how to split the control between the two components depending on user preference and the nature of the requirement.
It might be appropriate to provide a solution that appears as though it originated from the output report medium familiar to the user, for example, Microsoft Excel. Or if more than one medium is to be used, say, the user requires the report to be produced in Excel and Word from one application, you might want to have a controlling application place data into the output report medium. Table 3-3 shows three available data retrieval techniques.
Table 3-3
Data Retrieval Techniques
| Technique | Control Component | Example |
| Push | The control application processes data directly into the output medium component. | ReportInExcelAndWord (See page 141.) |
| Pull | No control application exists; the output medium component is powerful enough to process data directly. | ReportInExcel (See page 147.) |
| Combined | The push and pull techniques are combined. |
The ReportInExcelAndWord example illustrates the push technique with no code in the receiving application, using Visual Basic as the controlling application to place data into Excel and Word. The ReportInExcel example illustrates the pull technique, with partial code in Excel that pulls data into a worksheet. An example of the push technique with partial code in the receiving application would be a Visual Basic application that creates a text file and passes control to an Excel VBA macro to read the file and populate a worksheet.
Office 97 Considerations
You can effectively use all three techniques described in Table 3-3 in Office 97 because of the introduction of a common VBA development environment. You can even develop fully functional applications from within Word and Excel without using Visual Basic 5 at all. This raises a pertinent question: do we need Visual Basic 5 in the first place if all the functionality required is available in the report output application? The answer is not a clear-cut yes or no, although a number of fundamental issues sway my opinion toward yes.
Implementing a fully functional system within a document or a spreadsheet is not ideal for version control; most source control applications rely heavily on the fact that the source is based in a text format that can be easily read for differences and stored in a compressed format. If an application is contained within a document or a spreadsheet, all the benefits of source control are lost because the contents of a document or spreadsheet are stored in a binary format along with other nonrelated details that are difficult to decipher.
The ability to store source code separately from the resultant program is critical in ensuring that only one copy of the source code exists and that it is locked away from the prying eyes of users and other developers. Releasing a Word application to ten users results in the proliferation of ten copies of source code, providing ample opportunity for users and developers alike to adjust the code in one copy and not in the others.
Finally, VBA code held within Word and Excel is interpreted rather than compiled as with Visual Basic 5, which negates many of the possible performance improvements.
Each VBA component has a place—if used in moderation, the VBA development environment within Word and Excel is ideal for prototypes and small-scale reporting systems. The larger, more complex systems that involve more than one component work best as Visual Basic 5–developed applications, which allow for source control, version control, and performance improvements.