Shape Append Command

See Also   

The Shape APPEND command assigns a child Recordset to the Value property of Field objects in a parent Recordset.

Syntax

"SHAPE {parent-command} [[AS] table-alias]

APPEND {child-command}

RELATE(parent-column TO child-column)"

Description of parts

The parts of this command are:

• parent-command, child-command—A query command that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be Structured Query Language (SQL), although ADO doesn't require any particular query language.
  
  
• parent-column—A column in the Recordset returned by the parent-command.
  
  
• child-column—A column in the Recordset returned by the child-command.
  
  
• table-alias—An alias used to refer to the Recordset returned by the parent-command.

Operation

The Client Cursor Engine will issue the parent-command to the provider, which will return a parent Recordset. Then the child-command will be issued, which will return a child Recordset.

For example, the parent-command could return a Recordset of customers for a company from a Customers database, and the child-command could return a Recordset of orders for all customers from an Orders database.

The parent and child Recordset objects must have a column in common. The columns are named in the RELATE clause, parent-column first, then child-column. The columns may have different names in their respective Recordset objects, but must refer to the same information in order to specify a meaningful relation.

For example, the Customers and Orders recordsets could have CustomerID and BuyerID fields in common.

The Client Cursor Engine internally creates a new column and literally appends it to the parent Recordset. The values of the fields in the new column are references to rows in the child recordset which satisfy the RELATE clause.

The appended column will be automatically named "chapter" and be of data type adChapter. If you wish to navigate through the child Recordset, specify the appended column in the Recordset object Fields collection and retrieve the Recordset from the Field object Value property. Assign the retrieved Recordset to an empty Recordset object, then navigate through that Recordset as you would any other.

Parameterized Commands

Shape commands may be parameterized. For example, you can specify the following:

"SHAPE {SELECT * FROM customer} 
APPEND {SELECT * FROM orders WHERE cust_id = ?} 
RELATE (cust_id TO PARAMETER 0)"

In this case, parent and child tables happen to have a column name in common, cust_id. The child-command has a placeholder (that is, "?"), to which the RELATE clause refers (that is, "...PARAMETER 0". In effect, the relation is between the explicitly identified parent-column, and the child-column implicitly identified by the placeholder).

When the Shape command is executed, this is what happens in effect:

• An empty column is appended to the parent Recordset.
  
  
• The parent-command is executed and returns a row from the customer table.
  
  
• The value of the customer.cust_id column replaces the placeholder and the child-command is executed.
  
  
• All the rows from the orders table where the orders.cust_id column matches the customer.cust_id column are retrieved.
  
  
• A reference to the retrieved child rows is placed in the current row of the column appended to the parent Recordset.
  
  
• Then the next parent row is retrieved from the customer table and the cycle repeats until all rows are retrieved.