by Peter G. Aitken (c)1999
Originally published in Visual Developer magazine
Basic has a lot of strong points, but I think that most programmers would agree
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. OK, it's not quite that easy but you get the picture! The
database power of Visual Basic is, by the way, 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!
of Visual Basic's database power comes in the form of data aware controls. These
controls have special capabilities built in to 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.
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 of 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 made 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.
far so good, but what I have told you so far is stuff that most Visual Basic
programmers already know about. 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, and 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 use it. This is a fairly extensive topic so I will be
devoting two columns to it. This issue I will cover the background and show you
how to create a simple-bound data sink. In the next column I'll expand the
project to include complex binding and also show how to program a data source.
of 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 -
namely, 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
or not 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
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 must be set to one of
two values. vbSimpleBound 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.
the remainder of this column I assume 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, a type Single field called Percent, and a type Long field called Index
with AutoIncrement set to True. Create a primary index based on the Index field.
The table is called Contributors and the file itself is MYDATA.MDB (sorry,
that's the best I could come up with!). 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).
demonstration purposes I have purposely devised a simple demonstration, but one
that covers all of the important aspects of creating your own data sink. The
goal is an ActiveX control that displays a bar graph-style indication of a
percentage value, with the bar at half-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
then the second shape control will fill the lower 1/3 of the control.
a new ActiveX control project and change the Name property of the UserControl to
PC (short for Percent Chart). Also change its DataBindingBehavior 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
control's code is fairly simple. It will have a single property called
PercentValue. We will 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.
1. Code in the data consumer ActiveX control.
Private Sub UserControl_Resize()
' Fill control with Shape1.
Shape1.Move 0, 0, ScaleWidth,
Public Property Get PercentValue()
PercentValue = pPercentValue
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.
Private Sub Update()
' Position Shape2 at the bottom of
' with a height equal to the
Shape2.Move 0, _
Shape1.Height * (1 - pPercentValue / 100), _
Shape1.Height * pPercentValue / 100
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
information that links, or binds, one or more data sinks to a data source is
kept in a Binding Collection object. A Binding Collection 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 Binding Collection 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 Binding Collection is used instead.
Binding Collection 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. The following
table describes the properties of the Binding object.
1. Properties of the Binding object.
True if the data sink has changed the data.
name of the field to which the sink is bound.
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
optional unique string identifying the Binding object
reference to the bound data sink object.
name of the data sink object's property that is bound to DataField.
Binding Collection object has its own properties and methods. They are described
in Tables 2 and 3.
2. Properties of the Binding Collection object.
number of Binding objects in the collection.
a Command object of a DataEnvironment object that is the data source.
a DataSource or RecordSet object that is the data source.
a specific Binding object either by its index in the collection (1 to
Count) or by the Binding object's Key property.
the conditions under which the data source is updated (see Visual Basic
Help for more details).
3. Methods of the Binding Collection object.
a Binding object to the collection (see below for details).
all Binding objects from the collection.
a Binding object. Item can be a
numeric value specifying the object's position in the collection, or a
string specifying its Key property.
data from the data source and updates the bound data sinks.
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)
is the data sink that will be bound.
is the property of the data sink that will be bound.
Is the field in the data source that will be bound to PropertyName.
is an optional reference to a stdDataFormat object that specifies data
is an optional unique string identifier for the Binding object.
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
Set BindColl.DataSource =
While a Binding Collection 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.
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, 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
test program's form will contain only three controls - an instance of the
ActiveX control we just created, of course, plus one Text Box and one ADO Data
control to serve as the data source. Remember, in order 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 Contributors.
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.
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:
OBColl As BindingCollection
in the Form_Load procedure, add the code shown in Listing 2. Let's look at what
these three lines of code do:
1: Creates an instance of the BindingCollection object.
2: Specifies that the BindingCollection object's data source will be the ADO
Data control on the form, named Adodc1.
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
2. The test project's Form_Load procedure.
Private Sub Form_Load()
Set OBColl = New BindingCollection
Set OBColl.DataSource = Adodc1
OBColl.Add PC1, "PercentValue",
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
was pretty easy, wasn't it? You can create even more impressive data-aware
controls when you use complex binding. That's a topic that I'll leave for my
next column, when I'll also cover creating a custom data source.