Data Sources, Data Sinks. Article from Visual Developer Magazine.

Write your own data-aware controls in VB? Don’t panic! It’s not the black art that it used to be, thanks to new power in VB6.

Visual Basic has a lot of strong points, but I think that the strongest of its strong points is its database programming capabilities. I read that, according to Microsoft, 85% of Visual Basic applications involve database access of some sort. Nowhere, to my reasonably extensive knowledge (hey, I’m a columnist, so I must know something!), can you find a combination of a powerful programming environment and ready-to-use database tools that even comes close to Visual Basic. Compared with other approaches, Visual Basic makes database application programming as easy as falling off a log. As an aside, the database power of Visual Basic is something I do not advertise to my paying clients. When I bring in yet another project on-time and on-budget, I am perfectly content to let them continue to think of me as some sort of programming wizard guru type guy. They need never know that I am merely a competent Visual Basic programmer!

Much of Visual Basic’s database power comes in the form of data-aware controls. These controls have special capabilities that take much of the tedium out of database programming. They fall into two categories: Data sources provide a link between your program and the external data (the database file), while data consumers make use of the data in your program, displaying it to the user for example or using it to create a chart. I tend to prefer the term sink to consumer, partly because of my engineering background and partly because I think of a consumer as a person running amok with a credit card. Much as a heat sink absorbs the heat produced by a hot-running CPU, a data sink “absorbs” the data produced by a data source. (Remember, however, that consumer is Microsoft’s preferred term.)

If you have done even a little database programming with Visual Basic, you have seen data sources and sinks in action. Collectively, data sources and sinks are referred to as data-aware classes. The Data control, or its smarter younger brother, the ADO Data control, is probably the best-known data source. With a few keystrokes, a Data control can be connected to just about any database file. When your program runs, it has, through the Data control, access to all the information in the database file. You can view records, navigate from record to record, add new records, and delete or edit existing ones. All the hairy programming required to directly access a database file is taken care of for you. Alone, however, a data source is not enough.

To make use of the data provided by a data source, your program needs one or more data sinks. A data sink is an object that can be bound to external data that is made available by a data source. A Text Box control can act as a data sink. You bind the Text Box to a specific field in the external database—the LastName field in an address list database, for example—and the Text Box will automatically display the LastName data for whatever record is current in the data source. Likewise, if the user edits the data in the Text Box, the changes will automatically be stored in the database. Quite a few of Visual Basic’s controls can act as data sinks, including Check Box, Picture Box, List Box, Masked Edit, and Label. Most data sinks, including those just listed, can be bound to a single field in the source. A few others, including the DataList and DataGrid controls, can be bound to an entire record and display data from multiple fields at once.

So far so good, but what I have told you so far is stuff that most Visual Basic programmers already know. The data sources and sinks that are provided with the Visual Basic development environment are indeed great, but you can go beyond them and create your own data sources and sinks. To be more specific, you can program data-aware classes. Of course, the ability to program your own always brings with it the potential for greater flexibility and power.

This is an important feature of Visual Basic 6, even though a fairly small percentage of developers will ever actually use it. This is a fairly extensive topic. I will cover the background and show you how to create a simple-bound data sink. I’ll expand the project to include complex binding and show how to program a data source.

You may be thinking, “Well, of course I can program a data source class! There’s nothing new about putting code into a class that accesses external data.” You would be correct, but missing the point. The important feature of a data source, at least as the term is being used here, is not merely that that it can access external data but that it can be bound to other objects—specifically, to data sinks. A data source you create can be a lot more powerful than the Data control, and it does not need to have a visual representation. A data source can be an ActiveX control or an ActiveX DLL or EXE, depending on whether a visual interface is required. To permit a class to serve as a data source, you must change its DataSourceBehavior property from the default vbNone to vbDataSource.

As for data sinks, you have the same choice between an ActiveX control and a DLL or EXE. To serve as a data sink, a class’s DataBindingBehavior property must be set to one of two values. The vbSimpleBound value permits the class to be bound to a single field in the data table, while vbComplexBound permits binding to an entire record, or row, in the data table.

In the remainder of this column, I will assume that you have at least a fundamental familiarity with databases (tables, records, fields, etc.) and also with the process of creating an ActiveX control. Since we need a database to work with, I created a simple database table, using the Visual Data Manager add-in in Visual Basic. The table has only three fields: a type Text field (length = 50) called Name, type Single field called Percent, and a type Long field called Index with AutoIncrement set to True. Here’s what you need to do: Create a primary index based on the Index field. The table is called Contributors and the file itself is MYDATA.MDB. Add a few records to the table, using any names you like but making sure that the values you enter in the Percent field fall in the range 0-100. (You’ll see why later.)

Creating the Data Sink

For demonstration purposes I have kept this project simple, but it covers all of the important aspects of creating your own data sink. The goal is an ActiveX control that displays a bar graph indication of a percentage value, with the bar at half of its maximum height for 50%, and so on. We will create an ActiveX control that contains two Shape controls as its visual interface. The first Shape control will be sized to fill the entire ActiveX control to provide a white background and a thin black border. The second Shape control will be a different color, and its size will reflect the percentage value that the control is displaying. For example, if the percentage value is 33, the second shape control will fill the lower 1/3 of the control.

Start a new ActiveX control project and change the Name property of the UserControl to PC (for Percent Chart). Also change its DataBindingBehavopr property to 1 -vbSimpleBound. Place a Shape control on the UserControl and set its Backstyle property to 1 - Opaque, leaving all other properties at their default settings. Add a second Shape control on top of the first one. Change its Backstyle property to 1 - Opaque, its BackColor property to any bright color you like (I used red), and its BorderStyle property to 1 - Transparent. Do not worry about the precise sizes or positions of the Shape controls, as they will be set in code. In the Project Properties dialog box, change the Project Name to PercentChart and, on the debugging tab, select the Wait for Components to be Created option.

The control’s code is fairly simple. It will have a single property called PercentValue. We use the UserControl’s Resize event procedure to position the first Shape control to fill the UserControl. Also, there will be a procedure named Update that sizes the second Shape control to fill the appropriate percentage of the space. The code for the control is shown in Listing 1.

Listing 1. Code in the data consumer ActiveX control.

Dim pPercentValue 

Private Sub UserControl_Resize() 
' Fill control with Shape1. 

Shape1.Move 0, 0, ScaleWidth, ScaleHeight 

End Sub 
  
Public Property Get PercentValue() As Variant 

PercentValue = pPercentValue 

End Property 

Public Property Let PercentValue(ByVal vNewValue As Variant) 
' Restrict values to 0-100. 

If vNewValue <= 100 And vNewValue >= 0 Then 
    pPercentValue = vNewValue 
    ' Update the display. 
    Update 
End If 

End Property 

Private Sub Update() 
' Position Shape2 at the bottom of Shape1 and 
' with a height equal to the PercentValue property. 

Shape2.Move 0, _ 
  Shape1.Height * (1 - pPercentValue / 100), _ 
  Shape1.Width, _ 
  Shape1.Height * pPercentValue / 100 

End Sub

That’s it! The data sink is complete. You may be thinking that you missed something; other than setting the UserControl’s DataBindingBehavior property to VBSimpleBound, we have not done anything differently than for a regular ActiveX control that is not a data sink. That’s because most of the action takes place outside the control, and involves something called a BindingCollection.

The BindingCollection Object

The information that links, or binds, one or more data sinks to a data source is kept in a BC (BindingCollection) object. A BC is a collection, and has many things in common with other Visual Basic collections. There are some differences, however, and note also that the name BC departs from the usual Visual Basic practice of naming collections as the plural of the member object. Since it is a collection of Binding objects, it would normally be called Bindings, but because it has some extra properties and methods specific to ADO data binding, the name BC is used instead.

Each BC object can be associated with only a single data source at any one time. Within the collection, each Binding object defines a link between a field in the data source and a property in the data sink object. Table 1 describes the properties of the Binding object. The BC object has its own properties and methods. They are described in Tables 2 and 3.

Table 1. Properties of the Binding object.
Property Name	Description
DataChanged	Returns True if the data sink has changed the data.
DataField	The name of the field to which the sink is bound.
DataFormat	A stdDataFormat object that controls the formatting of the data. A default stdDataFormat object is created for each Binding, but you can modify it if special formatting is required. See the Visual Basic Help system for more information.
Key	An optional unique string identifying the Binding object
Object	A reference to the bound data sink object.
PropertyName	The name of the data sink object’s property that is bound to DataField.

Table 2. Properties of the BC object.
Property Name	Description
Count	The number of Binding objects in the collection.
DataMember	References a Command object of a DataEnvironment object that is the data source.
DataSource	References a DataSource of RecordSet object that is the data source.
Item	References a specific Binding object either by its index in the collection (1 to Count) or by the Binding object’s Key property.
UpdateMode	Specifies the conditions under which the data source is updated (see Visual Basic Help for more details).

Table 3. Methods of the BC object.
Method Syntax	Description
Add	Adds a Binding object to the collection (see below for details).
Clear	Removes all Binding objects from the collection.
Remove item	Removes a Binding object. Item can be a numeric value specifying the object’s position in the collection, or a string specifying its Key property.
UpdateControls	Re-fetches data from the data source and updates the bound data sinks.

The Add method has the following syntax which, as you can see, is very similar to the Add method for other Visual Basic collections:

Add(object, PropertyName, DataField, DataFormat, Key)

Here, object is the data sink that will be bound. PropertyName is the property of the data sink that will be bound. DataField is the field in the data source that will be bound to PropertyName. DataFormat is an optional reference to a stdDataFormat object that specifies data formatting. Key is an optional unique string identifier for the Binding object.

Let’s look at an example. Suppose you had a data source named MyDataSource that connected to a database table that included a field named CITY. Suppose also that you had a data sink named MyDataSink and it had a property named Text. Here’s how you would create a BindingCollection object and add a Binding to the collection to link these:

Dim BindColl As New BindingCollection Set BindColl.DataSource = MyDataSource BindColl.Add MyDataSink, "Text", "City"

While a BC is limited to a single data source, you can bind as many different data sinks to that source as you need—or, as is sometimes useful, multiple properties of the same data sink.

Testing the Data Sink

To see our data sink in action, we will need a regular Visual Basic project that contains a data source. You can use the usual procedures for testing an ActiveX control, namely creating a project group that includes both the ActiveX control project and a standard EXE project. To create the group, do the following: With the ActiveX control project still open, select Add Project from the File menu, and select Standard EXE. Now you’ll have two projects listed in the Project Explorer window. When you close the UserControl designer window by clicking on the X in its title bar, the ActiveX control becomes available for use in the other project, as indicated by the UserControl icon appearing in the Toolbox. Then you can place an instance of the ActiveX control on the form in the Standard EXE project and test it. You can also re-open the UserControl designer to modify the ActiveX control code as needed. To ensure that the Standard EXE project runs when you press F5 or click the Start button on the Visual Basic toolbar, you must set it as the startup project. You do this by right-clicking the project name in the Project Explorer window and selecting Set As Startup from the popup menu.

The test program’s form will contain only three controls: an instance of the ActiveX control we just created, plus one Text Box and one ADO Data control to serve as the data source. Remember, to use the ADO Data control, you must select it in the Components dialog box. After placing the ADO Data control, set its CommandString property to link to the database MYDATA.MDB you created earlier, and set its RecordSource property to “select * from Contributors”. Now, when the project runs, the ADO Data control will provide the application with a RecordSet containing all fields from all records in the table named Contributors.

The next step is to bind the Text Box control to the Name field. This is done in the usual way, by setting its DataSource property to Adodc1 (the name of the ADO Data control) and its DataField property to Name.

All that’s left is to bind the ActiveX control to the data source. Here is where the BindingCollection comes in. You must declare a variable to refer to the BindingCollection object as follows:

Private OBColl As BindingCollection

Then, in the Form_Load procedure, add the code shown in Listing 2. Let’s look at what the three lines of code in Listing 2 do:

Line 1 creates an instance of the BindingCollection object. Line 2 specifies that the BindingCollection object’s data source will be the ADO Data control on the form, named Adodc1. Line 3 specifies a binding between the field “Percent” in the data source and the property “PercentValue” in the object “PC1” (which is the default name assigned to the ActiveX control when you placed it on the form).

Listing 2. The test project's Form_Load procedure.

Private Sub Form_Load() 

Set OBColl = New BindingCollection 
Set OBColl.DataSource = Adodc1 
OBColl.Add PC1, "PercentValue", "Percent" 

End Sub

Static vs. Dynamic Data Binding
In Visual Basic versions 5 and earlier, data binding was static at run-time. This meant that the binding between a data sink and a data source had to be defined at design time. The inability to modify data bindings at run-time led to a lot of programming complexity. If you wanted a form to display data from two different data sources at different times, you had to create two complete sets of data sink controls, with each set bound to a different data source. Then one set of sinks was made visible, and the other hidden, as needed. With Visual Basic 6, the data binding of the TextBox, CheckBox, Label, FlexGrid, Hierarchical FlexGrid, DataGrid, DataCombo, DataList, Image, and PictureBox controls can be changed dynamically in code. This is permitted only with the new ADODB data sources, and not with the older Data and Remote Data controls.

The project is ready to run now. When you run it, you’ll see the name of the first record displayed in the Text Box, and a representation of the percent value displayed by the ActiveX control. When you use the ADO Data control to navigate between records, you can see the Text Box and the ActiveX control both update automatically, as shown in Figure 1.

Figure 1. The data-aware ActiveX control's display.

That was pretty easy, wasn’t it?

Data Sources

The data-aware controls provided with Visual Basic give you a great deal of database programming power, but many programmers want to take the technology much further for highly customized applications. Creating a custom data source has been a possibility for Visual Basic programmers for quite a while, and custom ActiveX controls can be created to “consume” the data. However, programming these could get rather complex because one part of the equation was missing: the ability to bind together custom data sources and data sinks, so that the transfer of data is handled automatically without the need to write code. Visual Basic 6 adds this missing ingredient. Actually, this capability is more flexible than you might think, because you are not limited to binding to controls on a form but can bind any ADO/OLE DB data source to any ADO/OLE DB data sink.

The BindingCollection object is used to define a binding between a data source and a data sink. Each BindingCollection object is associated with a single data source and defines bindings between that data source and one or more data sinks. A binding can be simple, which means that one field from the data source is bound to the data sink, or it can be complex, which means that an entire record, or row, from the data source is bound to the data sink. The binding behavior of a class is determined by its DataBindingBehavior property which can be set to vbNone, vbSimpleBoundvbComplexBound. The BindingCollection object is utilized only for simple bindings.

I already demonstrated a basic example of simple binding: an ActiveX control that had one property bound to a field in a data source, which was an ADO Data Control. I promised to explain creating a data sink that uses complex binding in this column, but that was before I started looking into the details. In the Visual Basic documentation, Microsoft talks about complex binding of custom data sinks but provides no information about doing it. All my efforts with other sources of information have turned up precious little, aside from some rather obscure C++ code, so I must (with some embarrassment) postpone an explanation of complex data binding of custom data sinks to some future column, after I have managed to figure it out for myself. Even so, there’s still a lot of interesting stuff to cover.

Semi-Complex Binding

I already showed you how to create an ActiveX control that served as a data sink, with a single property bound to a data source. You are not, however, limited to binding a single property to a single field; you can bind as many properties as you like. The properties are each bound individually, with each having a separate entry in the BindingCollection object, so this is not the same as true complex binding, in which an entire row is bound collectively. In some ways, however, it lets you accomplish the same things as complex binding, so I refer to it as semi-complex binding.

To demonstrate, I’ll use the same Access database table that we created earlier. It has a single table named Contributors which contains only three fields, a type Text field (length = 50) called Name, a type Single field called Percent, and a type Long field called Index with AutoIncrement set to True.

Next, start a new ActiveX control project. Set the UserControl’s DataBindingBehavior property to vbSinpleBound. Place three text boxes and three labels on the UserControl, and assign the following Name property values to the text boxes:

txtIndex txtName txtPercentage

When the visual design is complete, the control will look like in the Figure 2. It should be pretty clear which Text Box goes with which Label.

Figure 2. Visual design of MyControl

The ActiveX control has three properties, corresponding to the three fields in the database table that it will be bound to. They are called Idx, Named, and Percentage (I used the property names Idx and Named instead of Index and Name to avoid confusion with the control’s intrinsic properties named Index and Name.) The required property procedure code is shown in Listing 3.

Listing 3. Property procedures in the ActiveX data sink.

Public Property Get Idx() As Variant 
    Idx = txtIndex.Text  
End Property 

Public Property Let Idx(ByVal vNewValue As Variant) 
    txtIndex.Text = vNewValue  
End Property 

Public Property Get Named() As Variant 
    Named = txtName.Text  
End Property 

Public Property Let Named(ByVal vNewValue As Variant) 
    txtName.Text = vNewValue  
End Property 

Public Property Get Percentage() As Variant 
    Percentage = txtPercentage.Text  
End Property 

Public Property Let Percentage(ByVal vNewValue As Variant) 
    txtPercentage.Text = vNewValue  
End Property

That’s all that’s required for the ActiveX data sink. To test the control you need a standard EXE Visual Basic project as part of a project group, along with the ActiveX control. Procedures for doing this were explained above. The test project requires one ADO Data Control on its form, as well as one instance of the ActiveX control you just created. You must set the ADO Data Control’s ConnectionString property to link it to the database file, and set its RecordSource property to “select * from contributors” to select all fields and all records in the table.

The only code required in the test project is code that creates the data bindings. You need to create a BindingCollection object, associate it with the ADO Data Control, then add a binding for each of the field/property combinations. This code is shown in Listing 4, and assumes that the ADO Data Control is named ADODC1 and the instance of the ActiveX control is named MyControl1.

Listing 4. Binding the three properties of the ActiveX data sink to the fields of the database table.

Dim OBColl As BindingCollection  

Private Sub Form_Load()  

Set OBColl = New BindingCollection  
Set OBColl.DataSource = Adodc1  
OBColl.Add MyControl1, "Named", "Name",,"Name"  
OBColl.Add MyControl1, "Idx", "Index"  
OBColl.Add MyControl1, "Percentage", "Percent",,"Percent"  

End Sub

Note that we have assigned Key properties to two of the members of the collection, permitting them to be accessed using the Key value. The reason for this will be seen later. When you run the project, you’ll see that the three text boxes in the ActiveX control are bound to the three fields in the database table. Use the buttons on the ADO Data Control to move from record to record, and the corresponding data is displayed. In this simple demo, changes to the data made by the user are not written to the database table. Doing so would require a little extra programming, our next topic.

Saving Database Changes

The simplest kind of binding, as in the example just presented, moves data from the data source to the data sink, but not in the other direction. In other words, if the user edits any of the data in the bound control, those changes are not written to the database table. Saving of changes is controlled by the DataChanged property of the Binding object. Each binding that is defined in the BindingCollection is represented by a Binding object, and the default setting of the DataChanged property is False. If this property is True, however, then moving the data source to another record will automatically invoke the Edit and Update methods, and any changes that were made to the data in the bound data sink will be written back to the database table.

For a simple demonstration, change the code in the demo program’s Form_Load procedure as shown in Listing 5. The last two lines of the modified procedure set the DataChanged property to True for the Binding objects representing the Name and Percentage fields. (Now you can see why we set a Key property for these Binding objects earlier.)

Listing 5. Setting the DataChanged property to True so that data changes will be saved.

Private Sub Form_Load() 

Set OBColl = New BindingCollection  
Set OBColl.DataSource = Adodc1  
OBColl.Add MyControl1, "Named", "Name", , "Name"  
OBColl.Add MyControl1, "Percentage", "Percent", , "Percent"  
OBColl.Add MyControl1, "Idx", "Index", , "Idx"  
OBColl("Name").DataChanged = True  
OBColl("Percent").DataChanged = True 

End Sub

After these changes, when you run the program you’ll see that any changes to the Name or Percentage values in the control are saved to the database table and will be displayed the next time you open the file. Using the DataChanged property gives you complete control over which, if any, data changes are saved. You could, for example, set it to True for all Binding objects when the program starts, making saving of data changes the program default, but also provide a “Cancel” button that permits the user to abandon data changes by setting DataChanged to False for all or selected bindings.

Creating a Data Source

To quote from the Visual Basic Help system, “A data source is an object that binds other objects to data from an external source.” The external source is a database file, and the “other objects” are the data-aware data sinks that we have already covered. At its most basic, a data source is a class module that exposes one or more interfaces to the external data. While not strictly required, custom data sources are usually created as ActiveX components, because this greatly simplifies the task of sharing the class between multiple applications or using it as a software component that is shared by many programmers. An ActiveX data source can take the form of an ActiveX control, an ActiveX DLL, or an ActiveX EXE. As an ActiveX component, your custom data source can easily be shared. Of course, it can interface with data in a wide variety of sources, such as a local Access file, a private OLE DB data store, or a remote SQL server database.

As you might expect, the topic of custom data sources in Visual Basic is very complex, if only because it encompasses essentially the entire range of Visual Basic’s data access capabilities, and that’s a wide range indeed. It may help simplify our look at custom data sources by thinking of it as two related topics. First is the general topic of data access in Visual Basic, regardless of whether or not the access is part of a custom data source. Second is the question of encapsulating that data access within a custom data source. In the remainder of this column I will try to scratch the surface of the second topic, which taken on its own is not especially complicated.

One of the things that is possible with a custom data source is creating an emulation of the existing ADO Data Control. Now, why would you want to do this? One reason is to provide a different visual interface, a not-unreasonable idea since the ADO Data Control’s appearance leaves a lot to be desired. Another reason is to customize the behavior of the control in some way that better suits your needs. For demonstration purposes, I’ll combine these two rationales and show you how to create a modified clone of the ADO Data Control that has a better visual interface and is dedicated to a specific database file.

To begin, create a new ActiveX Control project. Open the Project Properties dialog box and enter “CustomDataSource” as the Project Name and “Data Source for MYDATA.MDB” as the Project Description. Change the UserControl’s Name property to CDS (for Custom Data Source) and change its DataSourceBehavior property to vbDataSource.

Next, place a control array of 4 command buttons on the UserControl. Do not worry about exact size and placement, as these will be set in code. Change the Caption property of the four command buttons as shown in Table 4.

Table 4. Command button Caption properties.
Index	Caption
0	First
1	Previous
2	Next
3	Last

Now add a label control, changing its Alignment property to 2 (that is, center). Again, the label’s size and position will be set in code, in the UserControl’s Resize event procedure, as shown in Listing 6.

Listing 6. Control sizes are set in the Resize event procedure.

Private Sub UserControl_Resize()  

Dim i As Integer  
Dim x As Integer, y As Integer  
Dim w As Integer, h As Integer  

For i = 0 To 3 
    x = i * ScaleWidth / 4 
    y = 0 
    w = ScaleWidth / 4 
    h = ScaleHeight * 0.8 
    Command1(i).Move x, y, w, h  
Next i 

Label1.Move 0, ScaleHeight * 0.8, ScaleWidth, ScaleHeight * 0.2   

End Sub

The next step is to define a Caption property, which will be displayed by the label control. This is accomplished by adding the two property procedures shown in Listing 7.

Listing 7. Property procedures for the Caption property.

Public Property Get Caption() As String 
   Caption = Label1.Caption  
End Property 

Public Property Let Caption(ByVal NewCaption As String) 
   Label1.Caption = NewCaption 
   PropertyChanged "Caption"  
End Property

We need two object variables to refer to the ADO connection and the recordset that the control will use. These are declared in the general declarations section of the code, as shown below:

Listing 8. Declaring global object variables to reference the connection and recordset objects.

Private cn As ADODB.Connection Private WithEvents rs As ADODB.Recordset

You must also open the References dialog box and select Microsoft ActiveX Data Objects 2.0 Library, so that the class definitions will be available for early binding.

The GetDataMember Event

At the center of a data source is the GetDataMember event. This event is not normally part of an ActiveX control, and is added only when the class’s DataSourceBehavior property is set to vbDataSource. GetDataMember is called whenever a bound data sink requests a new data source, or requests a data source for the first time. The event procedure looks like this:

Private Sub object_GetDataMember(DataMember As String, Data As Object) ... End Sub

The DataMember argument is a string containing the name of the data member to be bound as a data source—in other words, the DataMember property of the data sink that the control is bound to. The Data argument is a reference to the ADO recordset object (or the OLEDBSimple provider, but that’s a technology I will not cover here). The Data argument is used to return information from the procedure. What this usually means is that code in the GetDataMember event procedure creates a recordset containing the required data, then returns a reference to that recordset in the Data argument. Alternatively, the recordset objects can be created elsewhere, and code in GetDataMember simply needs to return a reference to the correct one.

Let’s look at an example. Suppose you have written a custom data source, and bind it to a text box control. This means that the text box’s DataSource property is set to the name of the data source control. Suppose also that the text box’s DataMember property is set to “Sales.” Then, when the program begins running, the data source’s GetDataMember event procedure will be called, and the value of the DataMember argument will be “Sales.” If, during program execution, code changes the text box’s DataMember property to “Agents,” then the GetDataMember event procedure will be called again, this time with the DataMember argument equal to “Agents.”

The code below demonstrates this. (Note: This code is not part of the demo project.) Assume that code elsewhere in the program has established the connection to the database file and created two recordsets, rsSales and rsAgents, that contain the appropriate data.

Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)

Select Case DataMember Case "Sales" Set Data = rsSales Case "Agents" Set Data = rsAgents Case Else Err.Raise "DataSource", "Invalid DataMember" End Select

End Sub

Returning to the demo project, we can now write the code for the GetDataMember event procedure. Because the project is relatively simple, and the program never requests a change in the data source, the GetDataMember event will fire only once, when the data binding is first established. Since we will not make use of the data sink’s DataMember property, this too can be ignored. (A blank string is passed to the DataMember argument of GetDataMember if the bound data sink’s DataMember property is blank.) Therefore we can use this event procedure to set up the ADO Connection and Recordset objects and establish the connection.

The code is shown in Listing 9. You’ll note that I have hard-coded the connection string required to connect to the MYDATA.MDB database file that we are using. You will likely need to edit the path information to indicate the location of your database file. Note also that the code sets the ActiveX control’s Caption property to display the name of the record source.

Listing 9. Code in the GetDataMember event procedure.

Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)  

Dim ConString  
Dim RecordSource  

RecordSource = "Contributors"  
Caption = RecordSource  
ConString = "Provider=Microsoft.Jet.OLEDB.3.51;"  
ConString = ConString & "Persist Security Info=False;Data Source="  
ConString = ConString & "C:\documents\VB Projects\binding\mydata.mdb" 

Set cn = New ADODB.Connection  
Set rs = New ADODB.Recordset  
cn.ConnectionString = ConString  
cn.Open  
rs.Open RecordSource, cn, adOpenKeyset, adLockPessimistic  
Set Data = rs  

End Sub

This completes the coding for the data source; now it’s time to take it for a spin.

Testing the Data Source

To test the data source, create a project group by adding a Standard EXE project to the Visual Basic workspace. Be sure that this new project is designated as the StartUp project. (Remember, the UserControl designer must be closed to make the ActiveX control available on the toolbar.) Then, working with the new Standard EXE project, place an instance of the ActiveX control on the form. Its default name will be CDS1. Next, place a text box on the form. With the text box selected, select the DataSource property in the properties window. You want CDS1 as the data source, and you’ll see that this is available on the drop-down list for the DataSource property.

Figure 3. Testing the ActiveX data source.

Next, set the text box’s DataField property to “Name.” The drop-down list for this property does not contain a list of available fields because the data source will not be connected to the database table until runtime.

You can now run the project. You’ll see that the binding works just the way it should: You can use the buttons on the custom control to move from record to record in the database table, and the Name field for each record is displayed in the text box.

But hold on, there’s more. A custom data source such as the one we just designed can also be complex-bound to a data sink. It’s remarkably simple, as the difficult details of complex binding are all taken care of behind the scenes. You can easily see this using the demo project that we just created. Add a data grid control to the test form, and set its DataSource property to CDS1. Run the project again, and the data grid control displays all of the records in the database table, as shown in Figure 4.

Figure 4. Using complex binding to display all data from the custom data source.

Summing Up

In this column, I’ve shown you the basics of creating custom data sources and data sinks (data consumers in Microsoft-speak) using Visual Basic 6. If there’s one single most important take-home message I learned from all this, it’s how simple it all it is! As is so often the case, Visual Basic takes care of most of the dirty work for you, so that you can devote your energies to the more important aspects of program design. Overall, database programming remains a fairly demanding task, made easier (but not easy!) by Visual Basic. As in so many other areas of development, Visual Basic provides you with two ways to approach a project: pre-programmed components that can be dropped in and used for many tasks, and the ability to create your own custom components as needed. v

During daylight hours, Peter is a research scientist at Duke University Medical Center in Durham, North Carolina. Comments and suggestions may be sent to him at paitken@acpub.duke.edu