Data data configuration typically requires two providers. The data-Configuration service of the service provider OLE DB provides data-configuration functionality, while the data provider, such as the OLE DB provider of SQL Server, provides data rows for the filled-in-form Recordset.
The name of the service provider can be specified as the value of the Connection object Provider property, or the connection string keyword "provider=".
The name of the data provider can be specified as the value of the "Data Provider" dynamic property, which is added by the data shaping Service for OLE DB to the Connection object Properties collection. Alternatively, you can specify the connection string keyword "Data provider=".
Without a filled recordset (for example, if all the columns of the formed recordset are created with the NEW keyword), the data provider is not required. Specify "Data Provider=none" in this case.
Example
Dim CNN as New ADODB. Connection
Cnn. Provider = "MSDataShape"
Cnn. Open "Data provider=msdasql;dsn=vfox;uid=sa;pwd=vfox;database=pubs"
General Shape Command
Data configuration defines the columns of the forming Recordset, the relationships between the entries represented by the columns, and how data is filled into the recordset.
A formed Recordset can consist of columns of the following types:
Column type description
The data is the field of the recordset returned by using the query command for the data provider, table, or previously formed Recordset.
A subset of a reference to another Recordset, called a subset. A subset column makes it possible to define a "parent-child" relationship in which the "parent" is a recordset that contains a subset of columns, and a "child" is a recordset represented by a subset.
The value of the aggregate column is obtained by performing an "aggregate function" on all rows, or a column of all rows of the child Recordset. (See the aggregate functions in the following table.) ）
Evaluating the value of an expression column is obtained by calculating the Visual Basic for Applications expression for the columns in the same row of the Recordset. An expression is an argument to a CALC function. (See the calculation expressions in the following table.) ）
Creates a new empty, fictional field that can then be filled with data. Columns are defined using the NEW keyword. (See the NEW keyword in the following table.) ）
The Shape command can contain clauses that specify query commands for the base data provider and that will return the Recordset object. The syntax of the query depends on the requirements of the base data provider. Although ADO does not require the use of any of the specified query languages, it is usually the use of Structured Query Language (SQL).
You can associate two tables with SQL JOIN clauses, but a hierarchical Recordset can express information more effectively. Each row of the Recordset created by the JOIN repeats the information in one table unnecessarily. In multiple child Recordset objects of a hierarchical Recordset, each object has only one parent recordset.
The Shape command can be emitted only by the Recordset object.
The shape command can be nested, that is, the parent command or subcommand itself can be another Shape command.
For more information about locating a hierarchical Recordset, see Accessing Rows in a hierarchical Recordset.
For more information about the correct syntax for shape commands, see shape syntax formatting.
Aggregate functions, CALC functions, and NEW keywords
The data configuration supports the following functions. The Chapter-alias is specified to contain the subset name that contains the column to be manipulated.
The Chapter-alias (subset-alias) can be complete, consisting of each subset column name that points to a subset containing column-name, all separated by a period. For example, if the parent-child set CHAP1 contains a subset of the quantity-listed AMT, the full name is Chap1.chap2.amt.
Aggregate function Description
SUM (Chapter-alias.column-name) calculates and of all values in the specified column.
AVG (Chapter-alias.column-name) calculates the average of all values in the specified column.
Max (Chapter-alias.column-name) calculates the maximum value in the specified column.
Min (chapter-alias.column-name) calculates the minimum value in the specified column.
Count (Chapter-alias[.column-name]) calculates the number of rows in the specified alias or column.
STDEV (Chapter-alias.column-name) calculates the standard deviation in the specified column.
The value of the Any (chapter-alias.column-name) column (the value of the column is the same for all rows).
Calculation expression Description
CALC (expression) computes any expression, but only for the Recordset row that contains the CALC function. Can be any Visual Basic for Applications (VBA) function or expression.
NEW keyword Description
NEW (field type [(width | scale][,precision])] Adds an empty column of the specified type to the Recordset.
