We could have used the MSHFlexGrid to display the parents and all of the children. I didn't choose that approach for a variety of reasons:
I pressed F1 with the cursor focus on the MSHFlexGrid, expecting MSDN to show me help topics about the grid. It didn't. MSDN complained, "The compiled help (.chm) file does not contain context IDs." I guess that's because I'm using the most recent (July 1999) version of MSDN. Sigh. There's plenty of help on the grid—just do a "Search" for it in MSDN. I found 166 entries mentioning MSHFlexGrid.
The Data Environment Designer approach does seem tempting. It makes a flashy demo and what you get is a codeless component that shows all parent rows with each child's rows tucked neatly underneath. For example, Figure 10-4 illustrates what the MSHFlexGrid looks like after having been populated from a Data Environment Designer-based query. Notice that the individual rows are already expanded. If you use the CollapseAll method, the grid will only show the parent rows. Clicking the + signs expands the child rows.
Figure 10-4: An MSHFlexGrid populated from a Data Environment Designer-generated query
Granted, this is cool. And it did not take long to set up using the drag-and-drop techniques. But the impact it has on your system is substantial. To get the flexibility, performance, and scalability I'm sure you will demand, you will want a code-based approach.
If you insist on using the Data Environment Designer to build your relationships and bind to the MSHFlexGrid, be sure to reset the Data Environment Designer's Connection object's properties using its Visual Basic-based property page, as shown in Figure 10-5.
Figure 10-5: The Data Environment Designer Connection (Visual Basic) property page
Figure 10-5. Set the Run (and Design) PromptBehavior properties to "4 – adPromptNever." This gives your code a chance to capture the login ID failures instead of having ODBC (or OLE DB) expose login ID dialogs. These dialogs give the user an opportunity to reguess their login ID and password—over and over again—until they get it right—something I prefer to avoid.