Binding Data to Controls with MS Access and ADO

(Page 1 of 6 )

This basic tutorial is targeted to those who want to develop Microsoft Access front end applications using ADO, or for those who are using DAO/RDO and want to move into ADO. This tutorial shows you how you may wire up the text boxes, combo boxes and list boxes to the underlying database using Microsoft's ActiveX Data Objects (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.

Data access using code

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.

Next: Retrieving and displaying data using text boxes >>
 

More ASP.NET Articles
More By Jayaram Krishnaswamy

Comments On: Binding Data to Controls with MS Access and ADO

· The predominance of Microsoft products, especially its Office is primarily because...    · Very good explanation.I couldnt get mine to work for a while, but then I took...   >>> Post your comment now!