Visual Basic Concepts
The Data Environment designer allows the quick and easy building of hierarchical recordsets at design time. Programming the resulting DataEnvironment object, however, requires a knowledge of the ADO programming model combined with characteristics of hierarchical recordsets. Below are the major points to visit when programming the DataEnvironment object.
Note The phrase "Data Environment designer" refers to the designer and its features. The phrase "DataEnvironment object" refers to the resulting programming object of the Data Environment designer.
Requerying a DataEnvironment-built recordset presents a few issues to be aware of. If you are using controls that are bound to the DataEnvironment, you must rebind those controls after requerying the recordset. You can rebind the controls on a form to the DataEnvironment object by setting the DataMember, DataSource, and DataField properties wherever applicable.
Whenever you query the database using the Requery method, you must rebind the controls to the appropriate recordset objects, as shown in the example below:
deNwind.rsCustomers.Requery
' Rebind a TextBox control named txtCustomer and a DataGrid
' control named grdData.
With txtCustomer
Set .DataSource = deNwind
.DataMember = "Customers"
.DataField = "CompanyName"
End With
With grdData
.DataMember = "customers"
Set .DataSource = deNwind
End With
Note The Data Environment designer automatically prepends references to Command objects with "rs"—thus Customers becomes rsCustomers. Also notice that the DataEnvironment object contains a "Customers" method. By invoking the method, you can open its recordset.
When you close a recordset, the controls bound to that recordset no longer listen for events broadcasted by that recordset to notify them of changes. If you reopen the recordset, the data for that recordset will not appear in the controls until you rebind them.
The example below demonstrates how to create a parameterized query with the Data Environment designer.
To create a parameterized query
SELECT * FROM Authors Where Au_ID = ?
A new Input parameter will have been created for you. You can create further parameters, if needed, or set properties of the parameter. If you are using the Access OLE DB Provider or ODBC Driver, see the section below entitled "Paramaterized Queries and the Access OLE DB Provider and ODBC Driver."
Option Explicit
Private Sub Command1_Click()
' You must close the recordset before changing the parameter.
If DataEnvironment1.rsCommandQuery.State = adStateOpen Then
DataEnvironment1.rsCommandQuery.Close
End If
' Reopen the recordset with the input parameter supplied by
' the TextBox control.
DataEnvironment1.CommandQuery Text1.Text
With Text2
.DataField = "AU_LName"
.DataMember = "CommandQuery"
Set .DataSource = DataEnvironment1
End With
End Sub
Private Sub Form_Load()
' Supply a default value.
Text1.Text = "172-32-1172"
' Change the CommandButton caption.
Command1.Caption = "Run Query"
End Sub
It's possible to build a parameterized query with the Data Environment designer and have controls bound to that DataMember. That query will run when the form is loaded. If you don't have the value of the parameter set ahead of time, the query will fail. You'll have a closed recordset and you'll need to re-bind your controls before explicitly running the query. To avoid this, at design time be sure to supply a default value for the parameter (on the Parameters tab). Alternatively, at run time, simply make sure you call the Command explicitly before displaying a control bound to that Command.
Due to limitations of the Microsoft® Access OLE DB provider and ODBC driver, parameterized queries are not fully supported in the Data Environment when connecting to an Access database, because the driver and provider do not return information about parameters (like data type and direction) in the query.
When running stored procedures from the DataEnvironment, how the Required property of a parameter is set depends on whether or not the stored procedure command is a child command in a hierarchy.
Although it's possible to insert a stored procedure into a hierarchy as a child command, there are two conditions that must be met:
To create a stored procedure as a child command
When using SQLServer and stored procedures, it's possible to create stored procedures that have optional parameters. However, if the stored procedure is not a child object in a hierarchy, optional parameters are not accepted, and instead must be supplied by your code.
An example of a stored procedure with an optional parameter (@idCol) is shown below:
CREATE PROCEDURE OptionalParametersNotAllowed
(@IDCol smallint = NULL)
AS
IF @IDCol = NULL
select count(SupplierID) from Products
ELSE
select count(SupplierID) from Products where SupplierID= @IDCol
With the Data Environment designer, you can insert the stored procedure by right-clicking a Connection object and clicking Insert Stored Procedures. As shown above, you can set properties of the stored procedure's parameters by right-clicking the resulting Command object, clicking Properties, then clicking the Parameters tab. While it's possible to set the Required option for the parameter to False, the DataEnvironment object will generate an error message ("Argument not optional") unless a value is supplied. To work around this situation, be sure to supply a value as shown in the code below:
Private Sub Command1_Click()
' Open the connection.
DE.Connection1.Open , "sa"
' Run the stored procedure with a value.
DE.dbo_ OptionalParametersNotAllowed 8
Debug.Print DE.rsdbo_OptionalParametersNotAllowed.Fields(0).Value
' Free resources
DE.Connection1.Close
Set DE = Nothing
End Sub
If you use your DataEnvironment object in an MTS component with its default settings, the work done by the DataEnvironment is not in the MTS transaction. Thus the SetAbort method will not roll back the work done by the DataEnvironment object. To ensure that the SetAbort method will roll back the DataEnvironment work, there are two methods:
DataEnvironment1.Connection1.Properties("Prompt") = adPromptNever
DataEnvironment1.Connection1.Open
Important Not all OLE DB Providers and ODBC Drivers support suppressing the prompt if login fails. And not all database management systems support the two-phase commit protocol required to support an MTS transaction.
When the DataReport is displayed for the first time, it must read the contents of the recordset. Once it has read the contents of the recordset, it leaves the recordset at BOF. Since this work is done asynchronously, you can't simply reset the bookmark after invoking the Show method. There is no event to signify when the DataReport has finished reading the recordset. This situation also affects the DataEnviroment because you may want to display the same data on a form and in a DataReport. If you display the data in textboxes on the form and then have a command button to display/print the data via the DataReport, the textboxes will be blank because the textboxes are using the same recordset as the DataReport whose bookmark is currently at BOF. See the Knowledge Base article Q190607 for more information.
The behavior of the Hierarchical FlexGrid differs from that of the DataGrid. While both are data-aware controls, the HFlexGrid control does not allow the user to edit data and propagate the changes back to the database. Instead the data is read into the grid once. Once the data is displayed, the control no longer acts like a bound control. Navigating through the recordset does not affect the active row in the MSHFlexGrid, and vice versa. Changes made to the recordset (insertions, updates, deletions, requeries) do not affect the MSHFlexGrid.
There are two distinct methods for requerying a database:
The differences may account for some confusion when trying to program the DataEnvironment object.
When you create a hierarchical recordset (adding Child commands and using the Relationship tab of the Properties dialog box to associate them), you are actually creating several recordsets. The resultant family of recordsets cannot easily be duplicated by using a standard SQL statement. In fact, the Data Environment designer uses a different language syntax called the Shape command. You can see the Shape command of any hierarchical recordset by checking the Source property either at run time or at design time:
SHAPE {SELECT * FROM `Customers`} AS Customers APPEND (( SHAPE {SELECT * FROM `Orders`} AS Orders APPEND ({SELECT * FROM 'Order Details`} AS OrderDetails RELATE 'OrderID' TO 'OrderID') AS OrderDetails) AS Orders RELATE 'CustomerID' TO 'CustomerID') AS Orders
You can reproduce the hierarchical recordset by assigning the Source property to the Shape command. However, as shown above, you must rebind the controls to the proper recordset objects. An example is shown below:
With grdData
' Be sure to reset the DataMember property to the name of the
' Command object you want to bind to.
.DataMember = "Orders"
Set .DataSource = deNwind
End With
With txtCustomer
.DataMember = "Customers"
.DataField = "CompanyName"
Set .DataSource = deNwind
End With
A hierarchical recordset consists of several recordsets presented in a stair-like, parent-child structure. In the Data Environment, each recordset is created by a child Command. To "walk" the structure in code, you must declare an ADO Recordset object variable for each child recordset and assign a reference to it. The reference must be to the Value property of a Field object that links two tables. In that case, note that the Value property returns an object reference to the recordset:
Dim rsTop As ADODB.Recordset
Dim rsChild1 As ADODB.Recordset, rsChild2 As ADODB.Recordset
' Open the recordset.
DataEnvironment1.titleauthors
' Set rsTop to the topmost Command object.
Set rsTop = DataEnvironment1.rstitleauthors
' Set the first child object variable to the Value property of the
' Field named authors.
Set rsChild1 = rsX.Fields("authors").Value
' Set the second (grandchild) object variable.
' Note that we use the reference to the child recordset (rsChild1).
Set rsChild2 = rsChild1.Fields("titles").Value
You can also determine if the Field object's Value property returns a reference to a child recordset by checking if its Type property returns adChapter, as shown in the example below:
Dim f As Field, f2 As Field
Dim rs2 As Recordset
DataEnvironment1.Command1
Set rs2 = DataEnvironment1.rsCommand1.Fields("Command2").Value
For Each f In rs2.Fields
Debug.Print f.Name
If f.Type = adChapter Then
For Each f2 In f.Value.Fields
Debug.Print , f2.Name
Next f2
End If
Next f
After assigning references to the child recordsets to the object variables, you can then bind controls to the recordset objects.
After configuring a DataEnvironment object as a hierarchical recordset, it's logical to examine the code editor and check for the events for each of the objects in the hierarchy. However you will find that only the topmost Command object has a set of ADO Recordset object events (WillMove, WillChangeField, MoveComplete, etc.). The question then becomes how to program the events for objects which don't exist.
Instead of declaring the object variables within the procedure (as shown above), declare them in the Declarations section of the code editor using the Private (or Public) and WithEvents keywords. Consequently the events for the ADO Recordset object will become available for programming, as shown below:
Option Explicit
Private WithEvents rsChild1 As Recordset
Private WithEvents rsChild2 As Recordset
Private Sub BindRecordsets()
DataEnvironment1.titleauthors ' Open recordset.
Set rsTop = DataEnvironment1.rstitleauthors
Set rsChild1 = rsX.Fields("authors").Value
Set rsChild2 = rsChild1.Fields("titles").Value
' Bind the child recordsets to two DataGrid controls.
Set DataGrid1.DataSource = rsChild1
Set DataGrid1.DataSource = rsChild2
End Sub
Private rsChild1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Debug.Print pRecordset!OrderDate
End Sub