Binding Data to Controls in Microsoft Office 2000

Ed Schultz
Microsoft Corporation

January 1999

Note   For the latest information, see http://msdn.microsoft.com/officedev/.

Summary: Describes some of the issues related to binding data to controls in Microsoft® Office 2000. (6 printed pages) Covers:

Data Providers
Data-Aware Controls
Data Binding to Non-Data-Aware Controls
Deployment
Office vs. UserForms Hosting
Visual Basic vs. Visual Basic for Applications Implementations

Data Providers

A data provider is a control or object that provides data for use with another control or program. The data provider makes data connectivity much easier by hiding most of the implementation from the user so the user can concentrate on using the data. Instead of writing complicated code, a user can often just select data items from a drop-down list and the connection is created.

The two providers supplied with Microsoft Office 2000 Developer are the Microsoft ADO Data Control (msadodc.ocx) and Microsoft Data Environment Designer (msde.dll).

Microsoft ADO Data Control (ADODC)

To use the ADODC control on UserForms

  1. Insert a UserForm in your solution.

  2. Right-click the Toolbox and select Additional Controls.

  3. Select Microsoft ADO Data Control, version 6.0 (OLEDB) and click OK.

  4. Select the ADODC Control from the Toolbox and draw it on the form.

  5. Fill in the ConnectionString and RecordSource properties accordingly.

To use the ADODC control on Office hosts (for example, Excel)

  1. Open the Control Toolbox.

  2. Click More Controls.

  3. Select Microsoft ADO Data Control, version 6.0 (OLEDB).

  4. Draw the control on the spreadsheet.

  5. Right-click the object and then select ADODC Object and ADODC Properties.

  6. Fill in the necessary information on the General and RecordSource tabs.

Data Environment Designer

Using the Data Environment Designer is much more involved than using the data control because designers are not currently supported by Office hosts and Microsoft Visual Basic® for Applications projects. Therefore, to take advantage of the Data Environment Designer, you need to create the designer in another project and compile it into a dynamic-link library (DLL) that can then be used. This is a two-step process.

To create the de.dll

  1. Open the Visual Basic Editor in your Office host.

  2. Select New Project from the File menu.

  3. Double-click Add-In Project.

  4. From the Insert menu, select Components.

  5. Select Data Environment from the Designers tab and click OK.

  6. From the Insert menu, select Data Environment.

  7. Switch to the Data Environment window.

  8. Right-click Connection1, select Properties, and fill in values accordingly.

  9. Right-click Connection1 and select Add Command.

  10. Right-click Command1, select Properties, and fill in values accordingly.

  11. From the File menu, select Make AddInProject1.dll.

To use the de.dll

  1. In the Visual Basic Editor, select VBAProject in the Project window.

  2. Select References from the Tools menu.

  3. Select AddInProject1 and click OK.

  4. In your code add the following: 
    Dim DE as New DE.DataEnvironment1.





Data-Aware Controls



Data-aware controls know how to use data provided by a data provider. The primary data-aware controls shipping with Office 2000 Developer include: Data Grid, Data Combo, Data List, and Hierarchical FlexGrid. 



Connecting these controls to data is usually as simple as connecting a data provider control to the RowSource or DataSource properties of a data-aware control. Additionally, if you are using the Data Environment, you will also need to set the appropriate RowMember or DataMember properties.



The following table represents the code snippets to use for the various combinations. The snippets are listed for completeness. This code can also be found in the Office 2000 Developer Code Librarian under "Data."







Data-Aware controls
Data provider
Office host
UserForm host





Data Grid
ADODC
1
1





Data List
ADODC
2
5





Data Combo
ADODC
3
6





Hierarchical FlexGrid
ADODC
4
4





Data Grid
DE
7
7





Data List
DE
8
11





Data Combo
DE
9
12





Hierarchical FlexGrid
DE
10
10







Code 1



Set DataGrid1.DataSource = Adodc1




Code 2



DataList1.ListField = "CustomerID"
Set DataList1.RowSource = Adodc1




Code 3



DataCombo1.ListField = "CustomerID"
Set DataCombo1.RowSource = Adodc1




Code 4



Set MSHFlexGrid1.DataSource = Adodc1




Code 5



Dim dl As DataList
DataList1.ListField = "country"
Set dl = DataList1
Set dl.RowSource = Adodc2




Code 6



Dim dc As DataCombo
DataCombo1.ListField = "country"
Set dc = DataCombo1
Set dc.RowSource = Adodc2




Code 7



DataGrid1.DataMember = "Customers"
Set DataGrid1.DataSource = DE




Code 8



DataList1.ListField = "CustomerID"
DataList1.RowMember = "Customers"
Set DataList1.RowSource = DE




Code 9



DataCombo1.ListField = "CustomerID"
DataCombo1.RowMember = "Customers"
Set DataCombo1.RowSource = DE




Code 10



MSHFlexGrid1.DataMember = "Customers"
Set MSHFlexGrid1.DataSource = DE




Code 11



Set x = DataList1
Set x.RowSource = DE
x.RowMember = "Customers"
x.ListField = "CustomerID"




Code 12 



Set y = DataCombo1
Set y.RowSource = DE
y.RowMember = "Customers"
y.ListField = "CustomerID"






Data Binding to Non-Data-Aware Controls



You can also bind data to a non-data-aware control via the Binding Manager. The Binding Manager supplies an object called the BindingCollection, which takes as input a data provider and connects it appropriately to non-data aware controls as specified.



To use the Binding Manager


    
	
  1. Open the Visual Basic Editor in Visual Basic for Applications.

    2. 

	
  2. Select References from the Tools menu.

    3. 

	
  3. Select and check Microsoft Data Binding Collection and click OK.
    4. 




The Binding Manager is now available for use. The following paragraphs provide code you can use to bind data.



Connecting Binding Manager to a control (for example, TextBox) using ADODC



Set bc = New BindingCollection
Set bc.DataSource = Adodc1
bc.Add TextBox1, "value", "EmployeeID"




Connecting Binding Manager to an Excel spreadsheet using ADODC



Set bc = New BindingCollection
Set bc.DataSource = Adodc1
bc.Add Range("B5"), "value", "LastName"




Connecting Binding Manager to a control (for example, TextBox) using DE



Set bc = New BindingCollection
bc.DataMember = "Employees"
Set bc.DataSource = DE
bc.Add TextBox1, "value", "EmployeeID"




Connecting Binding Manager to an Excel spreadsheet using DE



Set bc = New BindingCollection
bc.DataMember = "Employees"
Set bc.DataSource = DE
bc.Add Range("B5"), "value", "LastName"






Deployment



For a solution that utilizes the Data Environment and you wish to deploy the solution, you will need to ship msderun.dll with the solution.





Office vs. UserForms Hosting



There is a slight problem in hosting controls in Office versus UserForms for controls that have the RowSource property. For Office 2000 Developer, this primarily affects the Data List and Data Combo controls. 



The problem occurs because RowSource is both a property of UserForms and of certain controls. When late binding, Visual Basic for Applications defers to the UserForms implementation and not that for the controls. The appropriate fix for this is to early bind (or type cast) an object to be of the type needed. This eliminates the ambiguity for VBA.





Visual Basic vs. Visual Basic for Applications Implementations



There are two primary differences in implementations of data binding between Visual Basic and Visual Basic for Applications.



The first relates to designers. Visual Basic directly supports designers, whereas Visual Basic for Applications does not. The work needed to use and implement designers in Visual Basic for Applications is described in the preceding section.



The second relates to RowSource and data properties associated with a control. Because RowSource is a property of the UserForm, this property is not displayed in the Properties windows in Visual Basic for Applications, but it is displayed in Visual Basic. Furthermore, Visual Basic for Applications does not display any of the data properties in the Properties window, whereas Visual Basic does. However, these properties are available programmatically.