The Data Environment Designer (shown in Figure 12-3) seemed to show a lot of potential—at first. However, as the development community and I waded into its functionality, the crooked seams started to show. I expect the Data Environment Designer was intended to replace and augment the functions and features of the User Connection Designer. Let's look at the Data Environment Designer's laundry list of features:
Figure 12-3: The Visual Basic 6.0 Data Environment Designer with Four Commands
Now let's take a closer look at the Data Environment Designer and how it manages (or at least attempts) to do all of this.
There are several ways to use the Data Environment Designer. Many developers tell me that they find adequate functionality in the basic techniques, but that they stay away from the rest. Others say that the way the Data Environment Designer manages shapes and hierarchical result sets makes development of reports and complex shape operations far easier. I'll let you judge for yourself. The Data Environment Designer is also a "must-use" component of the Data Object Wizard, which constructs procedure-based applications and custom data-bound components based on Data Environment Designer Commands.
You can use the Data Environment Designer to create a persisted Connection object very simply—just drag a Table, View, or stored procedure from one of your existing Data Links (created with the Data View window) over to the Data Environment Designer window. This operation also creates a DataEnvironment Command object that returns the result set from the table, view, or stored procedure. However, you don't want to drag a table to the Data Environment Designer—not without clarifying the query—because the DataEnvironment runtime will construct an ADO Command object that will return all rows and all columns from the target table.
To correct this problem, click on the newly created Command object and right-click to change its properties. Click on SQL Statement in the source of data frame and click on SQL Builder. This launches the four-paned query editor as shown in Figure 12-4.
Figure 12-4: The Data Environment Designer's query editor
It's essential that you pare down the query to return just the required rows and columns from your table(s). It's easy to use the Query Designer window—just drag the tables needed from the Data Environment Designer or Data View window. The SQL query is created for you in the third pane, and you can select the required columns in the second. The top pane graphically shows the join relationships. If you right-click on the first pane and choose run, the Data Environment Designer will run the query and fill the fourth pane with the Recordset returned. Change any of the panes, and the changes are reflected in the other panes automatically. Very cool.
When you save your application, or just the .DSR file generated by the Data Environment Designer, the designer saves all of the parameters, properties, and other settings you provided when dragging and dropping and setting property page values. All of these values are stored in the binary (proprietary) DSR file and become a part of your project. The DSR file is processed again at runtime by the Data Environment Designer runtime engine, which reconstructs the DataEnvironment objects that you specified at design time. The DSR file can be shared with others in your development team. They can simply add the DSR to their project and then simply refer to the DataEnvironment object Connections and Commands as if they had constructed them on their own.
If you save the Data Environment Designer-generated .DSR file at this point and run your program, the Data Environment Designer runtime engine simply instantiates the DataEnvironment object and nothing else happens. The connection is not opened and the query is not executed. However, if you take the next GUI step and drag your Data Environment Designer Command object to a form, the designer creates a set of controls (right-click and you get to choose) and binds the Command object to these controls. However, there is no accommodation for input parameters for your query. In this case, when you run your program, the DataEnvironment object behaves (or misbehaves) like a Data control and starts to run before Form_Load—long before your error handlers get a chance to start (as if they did any good).
To run the DataEnvironment Command object against this connection without binding, you have to write a little code. To make it easier to code, I like to dereference the DataEnvironment object. Besides that, there's only one line of code required to run the query. Let's assume I have created a DataEnvironment Command object that executes a stored procedure called MySP. I created this Command object by dragging the stored procedure from the Data View window to the Data Environment Designer window. The MySP stored procedure requires three parameters, as shown in the following code, which executes this stored procedure at runtime:
Dim DE as DataEnvironment1 Set DE = New DataEnvironment1 DE.MySP "cat", 5, 32.5
The ADO Recordset resulting from this query is returned in an object exposed off of the DataEnvironment object and named after the query. In this case, the Recordset is returned in an object named rsMySP. However, to prevent losing it once we reference the Recordset, we have to create a separate Recordset variable to manage it.
Dim rs as Recordset Set rs = DE.rsMySP
To rerun the query, close the Recordset and simply use the same code (DE.MySP...), as shown previously, with new parameters. No, there is no Refresh method.
DE.rsMySP.Close DE.MySP "dog", 8, 45
Note that none of this code explicitly opened the connection. This was handled behind the scenes by the DataEnvironment object runtime when the DataEnvironment Command is referenced for the first time. So, what if the connection cannot be established? Well, just as with ADO, you have to set up your own error traps to catch these not-so-unlikely errors. If the DataEnvironment gets in trouble when connecting, one of the first strategies it uses is to reprompt the user with a dialog box asking for the UserID and Password, along with options to change to another database. Unfortunately, this prompting behavior does not pay any attention to your Visual Basic error handler. This means that the user gets to guess at the password (if this is the problem) until they get bored or get it right. And the problem might not be the password at all. I simply paused the server and triggered this error scenario.
This is why it's essential that you tell the Data Environment Designer to let you handle the errors in your own code. It's not obvious how to do this, but it can be done:
Figure 12-5: The Data Environment Designer Property page where you set prompt behavior
While the Data Environment Designer goes a long way toward addressing development problems, it seems to paint itself in a corner on many of these functions. Unless you go to a lot of trouble, you're likely to revert back to raw code. Let's look at what's missing or simply broken: