Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

Flat And Relational Databases

So far, we’ve defined a certain type of database: a flat or flat-file database. There is a second type of database as well: relational databases. Relational databases are called relational because they are set up to relate the data in multiple tables together. To make a table relational, you choose certain fields to be primary keys and foreign keys.

The primary key in a table is usually the most important one—the one you might use to sort on, for instance. The foreign key usually represents the primary key in another table, giving you access to that table in an organized way. For example, we might add a field for student IDs to our student grade table. That same field, student ID, may be the primary key in the school registrar’s database table, which lists all students. In our table, then, the student ID field is a foreign key, allowing us to specify individual records in the registrar’s table.

We’ve gotten an overview of databases at this point; the next step is to look into the three different ways of working with them in Visual Basic, and we’ll do that now.

DAO

When Visual Basic first started working with databases, it used the Microsoft Jet database engine, which is what Microsoft Access uses. Using the Jet engine represented a considerable advance for Visual Basic, because now you could work with all kinds of data formats in the fields of a database: text, numbers, integers, longs, singles, doubles, dates, binary values, OLE objects, currency values, Boolean values, and even memo objects (up to 1.2GB of text). The Jet engine also supports SQL, which database programmers found attractive.

To support the Jet database engine, Microsoft added the data control to Visual Basic, and you can use that control to open Jet database (.mdb) files. Microsoft also added a set of Data Access Objects (DAO) to Visual Basic:

•  DBEngine—The Jet database engine

•  Workspace—An area can hold one or more databases

•  Database—A collection of tables

•  TableDef—The definition of a table

•  QueryDef—The definition of a query

•  Recordset—The set of records that make up the result of a query

•  Field—A column in a table

•  Index—An ordered list of records

•  Relation—Stored information about the specific relationship between tables

We’ll work with these Data Access Objects in the next chapter; in this chapter, we’ll work with the data control.

The Data Control

The data control enables you to move around in a database from record to record and to display and manipulate data from the records in bound controls. This control displays a set of arrow buttons the user can manipulate to move through a database, and the records from that database are displayed in bound controls. You can see a data control operating with bound controls in Figure 24.2, where we’ve placed our students table into a database and opened it with a data control.

Figure 24.2  Using a data control.

In fact, you can perform most data access operations using the data control—without writing any code. Data-bound controls automatically display data from one or more fields for the current record, and the data control performs all operations on the current record. If the data control is made to move to a different record, all bound controls automatically pass any changes to the data control to be saved in the database. The data control then moves to the requested record and passes back data from the current record to the bound controls where it’s displayed.

When an application begins, Visual Basic uses data control properties to open a selected database, create a DAO Database object, and create a Recordset object. The data control’s Database and Recordset properties refer to those Database and Recordset objects, and you can manipulate the data using those properties. For example, if you have an SQL statement to execute, you place that statement in the data control’s RecordSource property, and the result appears in the Recordset property.

RDO

Remote Data Objects (RDO) connect to databases using ODBC. You set up ODBC connections to databases using the ODBC item in the Windows Control Panel, and then use one of those connections with the RDO objects. The Remote Data Objects are designed in parallel with the Data Access Objects; for example, the database engine is rdoEngine instead of DBEngine, Recordsets have become rdoResultsets, TableDefs became rdoTables, Workspaces became rdoEnvironments, Field objects became rdoColumn objects, and so on. Although the names have changed, the command set is very similar to DAO.

Although Microsoft intends ADO to supercede RDO, many programmers will use RDO for some time to come. In this chapter, we’ll take a look at RDO with the remote data control.

The Remote Data Control

Like the data control, the remote data control gives you access to a database and displays data in bound controls. Unlike the data control, however, you use the remote data control to access ODBC data sources (which can include databases built with all the popular commercial database programs).

As with the data control, if the remote data control is instructed to move to a different row, all bound controls automatically pass any changes to the remote data control to be saved to the ODBC data source. The remote data control then moves to the requested row and passes back data from the current row to the bound controls where it’s displayed.

In fact, the remote data control behaves like the data control in most respects, with some differences; for example, you can treat the remote data control’s SQL property like the data control’s RecordSource property, but it cannot accept the name of a table by itself unless you populate the rdoTables collection first.