Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: Binding Hierarchical Recordset in Data Environment


View products that this article applies to.

This article was previously published under Q190605

↑ Back to the top


Symptoms

In Data Environment, when a hierarchical recordset is used, and the parent/child/grandchild recordsets are bound, the expected behavior is for all the child/grandchild recordsets to "stay in sync" with the parent. However, when record in the parent table moves, the grandchild recordset does not receive notification that it needs to retrieve the current chapter. This creates a situation in which the grandchild recordset becomes out of sync.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

The following Visual Basic code reproduces the problem described above. Access database NWIND.MDB is used here: Customers table is the parent, Orders table, the child, and OrderDetails table, the grandchild. Two workarounds are provided by binding the parent/child/grandchild recordsets in code to the DataGrid controls. Workaround 1 takes the recordset from the Data Environment, and sets the DataSource property with the recordsets; Workaround 2, bypassing the Data Environment, uses the ADO SHAPE command to generate the hierarchical recordset.

Steps to Reproduce Behavior

Task One: Data Environment
  1. Start a new project in Visual Basic and choose Standard EXE. Form1 is created by default.
  2. On the Project menu, select Components.
  3. On the Designers tab, select Data Environment.
  4. On the Project menu, select Add Data Environment. If Add Data Environment item is not found directly under the Project menu, select More ActiveX Designers, and then click Data Environment. This brings up a Data Environment Window, named DataEnvironment1 by default.
  5. Right-click Connection1, then select Properties. Enter appropriate information to establish a connection to Access sample database NWIND.MDB. Click OK to save the information.
  6. Right-click Connection1, and then select Add Command. Command1 is created by default. Rename it as Customers, and set the following property values for Customers:
    Property     Value
    ----------------------
    CommandText  Customers
    CommandType  adCmdTable
    					
  7. Right-click Customers, and then select Add Child Command. Command2 is created by default. Rename it as Orders.
  8. Right-click Orders, and then select Properties.
  9. On the General tab, under the Source of Data section, click Database Object, and then select Table from the dropdown combobox. Select Orders table as the Object Name from the dropdown combobox.
  10. On the Relation tab, place a check next to Relate to a Parent Command Object. Select Customers as the Parent Command. Then relate the Parent-child with CustomerID field, and then click Add button.
  11. Click OK to save the information, and return to the Data Environment Window.
  12. Add a child command to the Orders command and rename it OrderDetails.
  13. Right-click OrderDetails, and then select Properties to bring up the OrderDetails Properties dialog. On the General tab, under Source of Datasection, click Database Object, and then select Table from the dropdown combobox. Select OrderDetails table as the Object Name from the drop-down combobox. On the Relations tab, select Orders as the Parent Command and relate the two commands on the OrderID field.
Task Two: Other Controls and Visual Basic Code
  1. From the Project menu, select Components, and then place a check next to Microsoft DataGrid Control 6.0 (OLE DB).
  2. Add three DataGrid controls, DataGrid1, DataGrid2, and DataGrid3 to Form1.
  3. Add three CommandButton controls, Command1, Command2, and Command3 to Form1.
  4. Paste the following code in the General Declaration section of Form1:
          Option Explicit
    
          Dim cn as ADODB.Connection
          Dim rsCustomers As ADODB.Recordset
          Dim rsOrders As ADODB.Recordset
          Dim rsOrderDetails As ADODB.Recordset
    
          Private Sub Form_Load()
    
             Command1.Caption = "Re-pro"
             Command2.Caption = "Workaround I"
             Command3.Caption = "Workaround II"
    
          End Sub
    
          Private Sub Command1_Click()
    
             Set DataGrid1.DataSource = DataEnvironment1
             DataGrid1.DataMember = "Customers"
             Set DataGrid2.DataSource = DataEnvironment1
             DataGrid2.DataMember = "Orders"
             Set DataGrid3.DataSource = DataEnvironment1
             DataGrid3.DataMember = "OrderDetails"
    
          End Sub
    
          Private Sub Command2_Click()
    
             Set DataGrid1.DataSource = Nothing
             DataGrid1.DataMember = ""
             Set DataGrid2.DataSource = Nothing
             DataGrid2.DataMember = ""
             Set DataGrid3.DataSource = Nothing
             DataGrid3.DataMember = ""
    
             Set rsCustomers = DataEnvironment1.rsCustomers
             Set rsOrders = rsCustomers.Fields("Orders").Value
             Set rsOrderDetails = rsOrders.Fields("OrderDetails").Value
    
             Set DataGrid1.DataSource = rsCustomers
             Set DataGrid2.DataSource = rsOrders
             Set DataGrid3.DataSource = rsOrderDetails
    
          End Sub
    
          Private Sub Command3_Click()
             Dim cn As New ADODB.Connection
             With cn
                .Provider = "MSDataShape"
                .CursorLocation = adUseClient
                .ConnectionString = "dsn=nwind;"
                .Open
             End With
    
             Dim rsCustomers As New ADODB.Recordset
             Dim rsOrders As ADODB.Recordset
             Dim rsOrderDetails As ADODB.Recordset
             rsCustomers.Source = "SHAPE {SELECT * FROM Customers} " & _
                                 "APPEND ((SHAPE {SELECT * FROM Orders} " & _
                                 "AS Orders " & _
                                 "APPEND ({SELECT * FROM [Order Details]} " & _
                                 "AS OrderDetails " & _
                                 "RELATE OrderID TO OrderID)) " & _
                                 "AS Orders RELATE CustomerID TO CustomerID)"
    
            rsCustomers.Open , cn, adOpenStatic, adLockOptimistic
    
            Set rsOrders = rsCustomers.Fields("Orders").Value
            Set rsOrderDetails = rsOrders.Fields("OrderDetails").Value
    
            Set DataGrid1.DataSource = Nothing
            DataGrid1.DataMember = ""
            Set DataGrid2.DataSource = Nothing
            DataGrid2.DataMember = ""
            Set DataGrid3.DataSource = Nothing
            DataGrid3.DataMember = ""
    
            Set DataGrid1.DataSource = rsCustomers
            Set DataGrid2.DataSource = rsOrders
            Set DataGrid3.DataSource = rsOrderDetails
          End Sub
    
    						
    NOTE: Without the workaround, when you move a record from parent table in DataGrid1, the child recordset in DataGrid2 is updated accordingly. However, the grandchild recordset in DataGrid3 disappears.

↑ Back to the top


References

For additional information about SHAPE APPEND syntax and how to traverse hierarchical recordsets, click the article numbers below to view the articles in the Microsoft Knowledge Base:
189657� HOWTO: Use the ADO SHAPE Command
185425� ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java

↑ Back to the top


Keywords: KB190605, kbprb, kbdatabinding, kbdatabase, kbbug

↑ Back to the top

Article Info
Article ID : 190605
Revision : 4
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 429