Why Not Use the Data Environment Designer? | Ado Examples and Best Practices

Team-Fly

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:

The grid is a PIA (that's a technical term) to set up in code, and because I didn't use the Data Environment Designer to create the parent-child relationships (I could have) I would have to wade into the grid's properties to set it up correctly. The grid properties don't seem to permit changing much at runtime anyway. For example, there's no (apparent) way to change the cell (grid column on a band) width at runtime (or design-time).

The help topics for the grid assume you'll use it with the Data Environment Designer and don't really help to show how to interactively set it up through the grid's property page dialogs.

Note

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.

Unless you tell it otherwise, the Data Environment Designer does not use the right (default) ADO connection or Recordset properties. This means you have to remind it to stop prompting for the user ID and password while opening.
The Data Environment Designer (unless you code otherwise) launches itself before Form_Load time. This means that if something goes wrong, your error handler won't be there to catch it. Deactivating the Data Environment Designer isn't easy if you want the bound controls to autosynchronize at design time.
The Data Environment Designer does not wait for parameters to be passed into the query you're running. Because we'll be running parameter-based queries (almost) exclusively, this simply won't work.
Even if we trap the WillExecute event and pass in first-time parameters, we will still have to manually rebind (in code) any simple bound controls (like TextBox controls) to the newly created Data Environment Designer Recordset. While this is not a problem with the MSHFlexGrid (it's a "complex" bound control), it is an issue for many of my designs.

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.

click to expand
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.

Tip

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.

click to expand
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.

Team-Fly