Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Coding for the Data Control

By placing a SELECT statement in a data control’s RecordSource property, an application can select a specific group of records. The application can display the list sorted in a useful order, and it can allow the user to edit records without including a single line of Visual Basic source code. With just a little code, an application can change a data control’s RecordSource property and produce different recordsets at run time.

For example, an application could use a set of option buttons to allow the user to select one of several SQL statements. The first statement might order the records by last name while the second might order them by first name. The SQL statements could even be placed in each option button’s Tag property so the option buttons’ Click event handler would not need to know anything about the SQL statements involved. If it was necessary to change the statements, the changes would be made in the buttons’ Tag properties. The following code shows how the option buttons’ click event handler code could select the different SQL statements:

Private Sub SQLOption_Click(Index As Integer)
    PeopleData.RecordSource = SQLOption(Index).Tag
    PeopleData.Refresh
End Sub

With this little bit of code, an application can allow users to choose different data orderings or completely different select statements. With an additional text box and command button, an application can search for data values entered by the user. For example, if the user enters a value in the SearchText text box, the application could find the records with matching last names using the following code:

Private Sub CmdSearch_Click()
    PeopleData.RecordSource = _
        “SELECT * FROM People WHERE LastName=‘” & _
        SearchText.Text & “’ ORDER BY LastName, FirstName”
    PeopleData.Refresh
End Sub

Using Data Access Objects

Earlier this chapter mentioned that data controls work closely with data access objects. This is made obvious by the fact that a data control’s Recordset property is actually a reference to a Recordset data access object. An application can use the Recordset object to manipulate the records in a data control’s recordset.

Recordset objects provide methods for moving through the recordset, examining the recordset’s selected fields, and adding, updating, and deleting records. The data control attached to a recordset automatically updates any bound controls to reflect any actions taken by the Recordset object. You can search the Visual Basic online help for “recordsets” to obtain more information on Recordset objects and their methods.

The following sections describe the Recordset and other data access objects. If you already know how to create and manipulate data access objects, you may want to skim this section or skip directly to the section, “Accessing Records with the Outline Control.”

Creating Recordsets

The Visual Basic Professional Edition allows applications to create Recordsets and other data access objects directly. This means an application can create Recordsets that are not attached to a data control. Unattached Recordsets are ideal for working with data that should not be displayed directly by bound controls.

For example, a program might need to perform some sort of statistical analysis on a large group of records. Displaying each of the records in rapid succession within bound text boxes would look strange to the user. Using an unattached Recordset object, the program can select the records and examine them internally before displaying only the summary results.

Before a program can create a Recordset object, it must give Visual Basic some contextual information by creating a couple of other data access objects. Data controls generate this information automatically; the program needs to follow these steps only if it is creating an unattached Recordset.

The data access object at the highest level of abstraction is DBEngine. The DBEngine object represents an instance of the Jet database engine, the software that runs the database. The main reason the program needs to use the DBEngine object is to access Workspaces.

A Workspace object defines a database session for a particular user. Different Workspaces can be attached to different databases, and they can perform different operations without interfering with each other.

Workspaces also manage user privileges and security if the database has security features enabled. Even though a Visual Basic program can manage user security using data access objects, it cannot actually create a secure database. A secure database must be created using Microsoft Access. Then a program can open it in Visual Basic.

The DBEngine contains a collection named Workspaces that contains references to Workspace objects. The entry Workspaces(0) is created by default. A program can use the DBEngine’s CreateWorkspace method to create additional Workspace objects if they are needed.

A Workspace object’s OpenDatabase method opens a particular database file and associates it with a Database object. The following code uses the default Workspace to open the file Employees.MDB. It saves the returned Database object instance for later use in the variable TheDB.

Dim TheDB As Database
    Set TheDB = DBEngine.Workspaces(0).OpenDatabase(“Employees.MDB”)

Once a program has opened a database, it can finally create a Recordset object. The following code uses the Database object’s OpenRecordset method to create a Recordset that lists the names in the Employees table:

Dim TheRS As Recordset
Dim query As String

    query = “SELECT * FROM Employees ORDER BY LastName, FirstName”
    Set TheRS = TheDB.OpenRecordset(query, dbOpenDynaset)

The second parameter to the OpenRecordset method is a constant that determines the type of Recordset created. A Visual Basic application can create several different kinds of Recordset objects, as described in the next section. The sections after that explain how to use Recordset objects to manipulate data. They tell how a Recordset object can create, edit, copy, and delete records. The next section explains how a data control can validate changes made by a Recordset object. Finally, the last section before the discussion of PeopleWatcher begins describes the relationship between Recordset objects and bookmarks.

Types of Recordset Objects The second parameter to the Database object’s OpenRecordset method indicates the type of Recordset to open. This parameter can be dbOpenTable, dbDynaset, dbOpenDynamic, dbOpenSnapshot, and dbOpenForwardOnly.

The value dbOpenTable makes OpenRecordset create a table type Recordset. A table type Recordset represents a table in the database. Some operations, such as locating records using the Seek method, are allowed only on table type Recordsets.

The value dbOpenDynaset makes OpenRecordset create a dynaset type Recordset. Dynasets can select records using an SQL statement and can select fields from more than one database table. For example, the following statement selects employee names from the Employees table. It also selects each employee’s salary from the Salaries table. This query uses the EmployeeID fields in the two tables to decide which salary value goes with each employee name.

SELECT LastName, FirstName, Salary FROM Employees, Salaries _
    WHERE Employees.EmployeeID = Salaries.EmployeeID