Working with Data Shaping

To work with data shaping in your VBA procedure, you need two providers: one for the data shaping functionality and the other one for the data itself. Therefore, before you can create shaped (hierachical) recordsets in your programs, you will need to specify: ■ The name of a service provider

The data shaping functionality is provided by the Data Shaping Service for OLE DB. The name of this service provider is MSDataShape and it is specified as the value of the Connection object's Provider property like this:

conn.Provider = "MSDataShape"

Creating and Manipulating Databases with ADO

or can be a connection string like this: "Provider=MSDataShape" ■ The name of a data provider

Because a shaped recordset needs to be populated with rows of data, you must specify the name of a data provider as the value of the Data Provider property of the Connection object:

conn.DataProvider = "Microsoft.Jet.OLEDB.4.0;"

or the connection string like this:

"Data Provider=Microsoft.Jet.OLEDB.4.0;"

The following is a code fragment from the procedure in Hands-On 16-4 that demonstrates how to specify the names of the data and service providers:

' define database connection string ' using the OLE DB provider ' and Northwind database as Data Source strConn = "Data Provider=Microsoft.Jet.OLEDB.4.0;" strConn = strConn & "Data Source = C:\Program Files\" & _ "Microsoft Office\Office11\Samples\Northwind.mdb"

' specify Data Shaping provider ' and open connection to the database Set conn = New ADODB.Connection With conn

.ConnectionString = strConn .Provider = "MSDataShape" .Open End With

0 0

Post a comment