Binding Data to Controls with MS Access and ADO

Introduction

The ubiquitous combo boxes, list boxes and text boxes provide the visual and interactive links to decision support systems because they are extensively used in GUI front ends. These controls maintain a connection to the underlying data by their ControlSource property. Connections to the underlying table create read locks, and if modification is involved then there will also be write locks. Since decision support systems work mostly with archived data, most of the activity will be centered around data that is read-only. In a multi-user scenario, these locks impose a limit preventing data access. Methods and practices to design these controls can help mitigate these locking-related bottlenecks. Since some of these controls do not need data that can be traversed in both directions (navigable recordsets), a connection that furnishes a fast "fill" of these controls is all that is needed.

Also, decisions in decision support systems are made by non-programmers who are more at home fine tuning their data-lookup with a number of constraints (fine tuning of queries against the database) they can apply and immediately see the value of interest. In these cases, to void any kind of data input error, a combo box (list box) presentation of choices to impose constraints for fine-tuning is more meaningful. Please review this article on an MS Excel application looking up data in a OLAP server where such a user interface is built in. This article builds on the earlier ADO related articles on this site to show how you may bind these controls to the recordsets provided by ADO, and how to populate them with data.

In this tutorial the Shippers table in the Northwind.mdb database will be used. Using ADO, the database will be accessed. The Northwind database is not resident on the Access application. The connection to the database on the local machine is achieved by means of ADO. The query against the database will return a recordset which will be the control source for the form. The controls on the form are unbound because there is no database locally present. The binding is achieved at run time.

This way of displaying data has been around since the days of DAO. A number of text boxes bound to the various columns of the table, or fields returned by a query, are displayed in the text boxes as the recordset is moved in either direction. The minimum that is required for this is a CursorType that will support this bi-directional data movement. For this tutorial the text boxes will display the three columns returned by the select statement shown here.

Place three text boxes on the form that will serve as the canvas for the different controls that will be placed on them. The picture shows the three text boxes, and the picture that follows it shows the properties of the form which can be accessed by right clicking on the black square dot at the top, left end of the form in the design view shown in this picture.

The record source property of the form is empty, and the three text boxes are unbound.

Code to retrieve the recordset

The following code is placed in a module that is added to the application. The AdoRst() procedure will provide a publicly visible recordset, rst. Since the CursorType is adOpenKeySet, the recordset may be moved up and down its range. The sample database is from Access 2003.

Option Compare Database
Public strsql As String
Public cnn As New ADODB.Connection
Public rst As New ADODB.Recordset
Public Sub AdoRst()
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select * from Shippers"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
End Sub

In an earlier tutorial it was mentioned that the Access form has a new property called the Recordset property. This is not a design time property, and therefore you do not see it in the picture shown above. You may, however, look for it in the object browser, where you will find it. Now all that is needed is to assign the form's control source property to the rst recordset. Then for each of the text boxes, the control source is the name of the Recordset's field items (0 based). The commented out message box would return true, since the recordset can be moved both ways.

Private Sub Form_Load()
Call AdoRst
Set Me.Recordset = rst
'MsgBox ("Can you move to a previous record: " & rst.Supports 

(adMovePrevious))
Me.Text0.ControlSource = rst.Fields.Item(0).Name
Me.Text2.ControlSource = rst.Fields.Item(1).Name
Me.Text4.ControlSource = rst.Fields.Item(2).Name
Set rst = Nothing
Set cnn = Nothing
End Sub

A header is added to the form and appropriate labels are inserted for the text boxes, as shown in the displayed form in the next picture.

Populating a combo box

As mentioned previously, the combo box will be populated in order to use it as a constraint to filter the data from the database for displaying to the user. Typically this may be the column "last name" in a database containing contact information, or it could be the telephone number in a video store database example. In the present example it will be the shipper's name as shown in the picture. Again the combo box is unbound in the design.

In a combo box you want to dump all of the data, and you do not require it to be scrollable. In order to get this functionality with reduced record locks, you need to use the read-only, forward-only type of cursor and close it immediately. The code for achieving this is shown here. The CursorType adOpenForwardOnly achieves a fast read of the data, and the recordset is closed immediately after retrieval. Although the code has been written to the click event of a button, it could have been a procedure called by the from load event itself.

In order to populate the combo box, row after row of data is collected in a string with each row separated by a semi-colon and the aggregated string is assigned  to the combo box's row source property. The syntax for the value list is  < value1;value2;value3;...>.  At design time, make sure that the row source type of the combo box is set to value list, as shown in this picture. The other option for row source type is Table/Query, which is not applicable in this case.

Private Sub Command6_Click()
'-----fast data retrieval block----
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select * from Shippers"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strsql
End With
'--------Aggregating the semi-colon delimited string from recordset--
Me.Combo9.RowSource = ""
Dim strCombo
strCombo = ""

While Not rst.EOF
'------generate [value list for row source] here----
strCombo = strCombo + rst.Fields.Item(1) & ";"
rst.MoveNext
Wend
'------Assigning aggregate string to row source---
Me!Combo9.RowSource = strCombo
Set rst = Nothing
Set cnn = Nothing

End Sub 

When the form is displayed the combo box will be empty, but when the "Populate Combobox" button is clicked, the combo box will be filled with the names of the shippers in the database as shown. For the purposes of this display, the form's record navigator, separator and record related properties have been set to false.

Filling a list box

For read-only decision support data display purposes, the list box is ideal. It is very efficient in terms of resources, and it is easy to add functionality to arrange for drill-down of data -- more refined filtering of the data. Similar to the combo box, in this demo tutorial, add a list box and a button. For the click event of the button, type in this code.

Private Sub Command11_Click()
Me.List12.RowSource = ""
'-----fast data retrieval block----
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select * from Shippers"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strsql
End With
'-----------------------
Dim strlist
strlist = ""
rst.MoveFirst
While Not rst.EOF
'---------create value list
strlist = strlist & rst.Fields.Item(1) + "--" + rst.Fields.Item(2) & ";"
rst.MoveNext
Wend
MsgBox strlist
Me!List12.RowSource = strlist
Set rst = Nothing
Set cnn = Nothing
End Sub

The design view of the list box control is shown. The list will be filled when the "Populate the List Box" button is clicked. It is necessary that the Row source type property be set to value list at design time for the list box.

Displaying a list box

When the form is displayed, the list box will be empty, but when the "Populate the List Box" button is clicked, the list box will be filled with the names of the shippers with their telephone numbers in the database, as shown. Before the list box is filled, the message box will appear with the value list. The value list produced by the above program is shown in the message box in this picture.

For the purposes of this display, the form's record navigator, separator and record related properties have been set to false.

Summary

This tutorial is about binding data from the underlying database to the otherwise unbound controls from the toolbox in Microsoft Access. The Recordset property of the MS Access form provides a solid link.  What is covered in this tutorial is only part of the story. It has provided only the basic building blocks for code based application development. For application development related questions, please pose your questions to the article blogs.