Microsoft Office 2000/Visual Basic Programmer's Guide

Working with the Data Source Control's Object Model

You specify the source for the data in the Data Source control by setting the control's ConnectString property. You then add one or more recordset definitions by using the AddNew method of the RecordsetDefs collection. The following example illustrates how to initialize the Data Source control and then use it to provide data to a Chart control:

Sub CreateChart()
   Dim cnnConnection
   Dim strSQL

   Set cnnConnection = CreateObject("ADODB.Connection")
   cnnConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
   cnnConnection.Open "c:\OPG\Samples\CH05\solutions9.mdb"
   DataSourceControl1.ConnectionString = cnnConnection.ConnectionString

   strSQL = "SELECT DISTINCTROW Employees.LastName, "
   strSQL = strSQL & "Sum(OrderDetailsExtended.ExtendedPrice) AS [Order Amount] "
   strSQL = strSQL & "FROM Employees "
   strSQL = strSQL & "INNER JOIN (Products "
   strSQL = strSQL & "INNER JOIN (Orders "
   strSQL = strSQL & "INNER JOIN OrderDetailsExtended "
   strSQL = strSQL & "ON Orders.OrderID = OrderDetailsExtended.OrderID) "
   strSQL = strSQL & "ON Products.ProductID = OrderDetailsExtended.ProductID) "
   strSQL = strSQL & "ON Employees.EmployeeID = Orders.EmployeeID "
   strSQL = strSQL & "GROUP BY Employees.LastName;"
   
   DataSourceControl1.RecordsetDefs.AddNew strSQL, dscCommandText, "EmployeeSales"
   
   ChartSpace1.DataSource = DataSourceControl1
   ChartSpace1.DataMember = "EmployeeSales"
   ChartSpace1.Charts.Add
   ChartSpace1.Charts(0).Type = ChartSpace1.Constants.chChartTypeBarClustered
   ChartSpace1.Charts(0).SetData ChartSpace1.Constants.chDimCategories, 0, "LastName"
   ChartSpace1.Charts(0).SetData ChartSpace1.Constants.chDimValues, 0, "Order Amount"
End Sub

You can add multiple recordsets to the control and then use VBScript code to specify which recordset to display. The DataSourceControl.htm file in the ODETools\V9\Samples\OPG\Samples\CH12 subfolder on the Office 2000 Developer CD-ROM contains VBScript code that illustrates how to programmatically work with many of the properties and methods of the Data Source control.

For complete documentation of the Data Source control's object model, see the Msowcvba.chm Help file, which is located in the C:\Program Files\Microsoft Office\Office\1033 subfolder.

Note   The path to the Msowcvba.chm Help file reflects the language ID folder (1033) for U.S. English language support in Office. The language ID folder below C:\Program Files\Microsoft Office\Office differs for each language.