Make your own free website on Tripod.com
'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Using Visual Basic 6 -- Ch 30 -- Database Basics and the Data Control


Using Visual Basic 6

Previous chapterNext chapterContents


- 30 -
Database Basics and the Data Control


Understanding Databases

In simplest terms, a database is a collection of information. The most common example of a database is a phone book, which is a collection of names, addresses, and phone numbers. Each line in a phone book is a record that contains the information for a single person or family. The entire set of records--that is, all the listings in the book--is a table. Another important characteristic a phone book has in common with most databases is that information is presented in a specific order--in the case of the phone book, alphabetically by last name.

Computer databases are similar in concept to phone books in that they provide a way to store and retrieve information easily and quickly. Computers actually can use two basic types of databases--flat-file and relational. A phone book is an example of a flat-file database. This means that a single record stores all the fields, or discrete data, for each entry, and a single table contains all the records for the database (see Figure 30.1).

FIGURE 30.1 Each unique instance of a collection of fields is called a record.

By contrast, a relational database stores information in a number of tables related by a common field known as the primary key. For instance, you might have a Customer Information table that contains specific information about your customers, and you might have another table called Loans Outstanding that contains information about outstanding loans. Both tables contain a common field--Social Security Number. In a relational database, by keying on the Social Security Number field, you could produce a third table, Average Days to Pay, that's made of data from each of the other tables (see Figure 30.2).

FIGURE 30.2 Relational databases are commonly used for large-scale applications.

Understanding Database Terminology

Thus far, you've seen a few terms, such as record and field, used to refer to different parts of a database. It's important that you understand a few more terms before learning about databases. Table 30.1 defines the key parts of a database.

TABLE 30.1  The Basic Elements of a Database

Element Description
Database A group of data tables that contain related information.
Table A group of data records, each containing the same type of information. In the phone book example, the book itself is a table.
Record A single entry in a table, consisting of a number of data fields. In a phone book, a record is one of the single-line entries.
Field A specific piece of data contained in a record. In a phone book, at least four fields can be identified: last name, first name, address, and phone number.
Index A special type of table that contains the values of a key field or fields and contains pointers to the location of the actual record. These values and pointers are stored in a specific order and can be used to present data in that order. For the phone book example, one index might be used to sort the information by last and first name; another index might be used to sort the information by street address; and a third might be used to sort the information by phone number.
Element Description
Query A command, based on a specific set of conditions or criteria, designed to retrieve a certain group of records from one or more tables or to perform an operation on a table. For example, you would write a query that could show all the students in a class whose last name begins with S and who have a grade point average of more than 3.0.
Recordset A group of records, created by a query, from one or more tables in a database. The records in a recordset are typically a subset of all the records in a table. When the recordset is created, the number of records and the order in which they're presented can be controlled by the query that creates the recordset.

The Microsoft Jet database engine provides the means by which Visual Basic interacts with databases. You use it with Visual Basic to access databases and database functionality. The Jet engine is shared by Visual Basic, Microsoft Access, and other Microsoft products, and it lets you work with a wide variety of data types, including several types of text and numeric fields. These different data types give you a great deal of flexibility in designing database applications. Table 30.2 lists the data types available.

TABLE 30.2  Many Data Types Are Available with the Jet Database Engine

Type Description Size/Range
Text Character strings 255 characters maximum
Memo Long character strings Up to 1.2GB
Byte Integer (numeric data) 0 to 255
Integer Integer (numeric data) -32,768 to 32,767
Long Integer (numeric data) -2,147,483,648 to 2,147,483,647
Counter Long integer, automatically incremented
Single Real (numeric data) -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values
Type Description Size/Range
Double Real (numeric data) -1.79769313486232E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308
Currency Real (numeric data) -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Yes/No Logical/Boolean
Date Date and time values
Binary Binary data Up to 1.2GB
OLE OLE objects Up to 1.2GB

As your database programming skills develop, you will be interacting with the Jet database engine on an abstract level. For now, the Jet engine will be relatively transparent to you because you will use the Data control to do your database work. This control works with the Jet database engine, which in turn works with the database. Whether a database is flat or relational isn't important for the time being because using the Data control during design time hides most of the inner workings of the database from you.

Working with the Intrinsic Data Control

The Data control is a link between the information in your database and the Visual Basic control that you use to display the information. As you set the Data control's properties, you tell it which database and what part of that database to access. By default, the Data control creates a dynaset-type recordset from one or more of the tables in your database. This means that the recordset is dynamic so that records within it are updated when the data from the original table is modified.

The Data control also provides the navigation functions that your application will need to switch between records. By using these buttons, users can move to the first or last record in the recordset or to the next or previous record (see Figure 30.3). The design of the buttons is somewhat intuitive in that they're similar to the buttons you might find on a VCR or CD player.

FIGURE 30.3 The Data control displays the value of its Caption property between the navigation buttons.

Use the Data control

1. Create a new project and name it DataProj. Name the default form frmMain.


Download this project's code

You can find the code for all the examples in this chapter at http://www.mcp.com/info. When prompted, enter 078971633x for the ISBN and click the Search button.


2. Select the Data control from the Toolbox and draw a Data control on the form.

3. Retain the default name of the Data control, Data1.

4. Add two TextBox controls to the form. Name one txtFirst and the other txtLast. Position the Data and TextBox controls as shown in Figure 30.4.

5. Save the project as DataProj.vbp.

FIGURE 30.4 Use the Data control to access a database and to bind controls on a form to the fields of a database.

Connecting the Intrinsic Data Control to a Database


The DatabaseName and Name properties

The DatabaseName property isn't the same as the Name property. The Name property specifies the name of the data control object and is used to reference the object in code. The DatabaseName property, on the other hand, specifies the name of the database file that the data control is accessing.


After the Data control is on your form, you need to make the connection between it and the information in your database. This is done by setting the properties of the Data control.

Although several properties can affect the way a Data control interacts with the database, only two properties are required to establish the link to a Jet database: DatabaseName and RecordSource. Specifying DatabaseName "connects" a Data control to a specific database, whereas RecordSource specifies a table within that database. After you set these two properties, the Data control is ready to retrieve, create, and edit information.


Creating Databases

Creating a database from scratch in Visual Basic is an advanced skill. For now, you're going to work with pre-existing database files made in the Microsoft Access environment. The demonstration items in this chapter will reference the Access database file Composer.mdb, available for download from http://www.mcp.com/ info. You can also use Biblio.mdb or Nwind.mdb, both included with Visual Basic, with minor modifications to the steps in this chapter.


Attach a Data control to a database and table

1. Select the Data control on the form frmMain in the project DataProj.vbp created earlier.

2. Select the DatabaseName property in the Properties window (see Figure 30.5).

3. Select the database Composer.mdb from the DatabaseName dialog.

4. Select table tblComposers from the RecordSource property drop-down list (see Figure 30.6).

FIGURE 30.5 When you select the DatabaseName property in the Properties window, an ellipsis appears to the right of the value area. Click the ellipsis to open the DatabaseName dialog.

FIGURE 30.6 Assigning a database file to the Data control's DatabaseName property populates the RecordSource property's drop-down list with all the tables and queries in that database.


Assign a RecordSource before a DataField

Be sure to have a table assigned to the Data control's RecordSource property before you select a value for another control's DataField. If you don't have a table assigned to the RecordSource property, you will get an error.


5. Select the txtFirst TextBox on the form.

6. For the TextBox's DataSource property, select Data1 in the Properties window (see Figure 30.7).

7. For the TextBox's DataField property, select FirstName (see Figure 30.8).

FIGURE 30.7 The DataSource property lists all the Data controls on a form.

FIGURE 30.8 All fields from the table assigned to the Data control's RecordSource property are displayed in the DataField drop-down list.

8. Assign the Data control Data1 to the DataSource property of the txtLastName TextBox, just as you did for the txtFirstName TextBox.

9. In the Properties window, select LastName for the DataField property of the TextBox txtLastName (see Figure 30.9).

10. Save and run the code (see Figure 30.10).

FIGURE 30.9 When you assign a field from a table to the DataField property of a TextBox, the contents of that field will appear in that TextBox on a record-by-record basis.

FIGURE 30.10 You can move through the database's table by using the Data control's navigation buttons.

In the preceding steps, you "connected" a database to a Data control and then selected a RecordSource for the control. Then you assigned that Data control to be a DataSource for two TextBoxes. You bound each TextBox to a field in the database (the Data control's RecordSource) by selecting a field for the TextBox's DataField property.

If you want to add and bind more TextBoxes to the Data control, or if you want to add and bind a Label control, you follow the process enumerated earlier. Controls such as CheckBoxes and ListBoxes can also be bound to a Data control; however, the fields to which a CheckBox or ListBox is bound must contain data of type Boolean (see Table 30.3).

TABLE 30.3  Some Controls That Can Be Bound Only to Specific Data Types

Control Available Data Types
TextBox Any data types. Editing is allowed.
Label Any data types. Editing isn't allowed.
Image Displays graphics stored in the database but doesn't allow editing of the image.
Control Available Data Types
Picture Displays graphics stored in the database and allows editing of the image.
CheckBox Boolean data type only. This allows updating of the record.
ListBox Text data types. Editing is allowed on the selected record.
ComboBox Text data types. Editing is allowed on the selected record.

Bound controls such as the TextBox can also be used to edit a record. To do this, the user simply edits the contents of the control while your program is running. When a different record is selected with the Data control's navigation buttons (or the form is closed), the information in the database is automatically updated to reflect the user's changes.

Creating Database-Bound Forms with the Data Form Wizard

Visual Basic provides a tool that lets you easily make forms that have controls bound to a database. Called the VB Data Form Wizard, it can be accessed from the Add-Ins menu. The Data Form Wizard creates a form that lets you browse a database, complete with text boxes, labels, and the ADO Data control. The ADO Data control is slightly different from the intrinsic Data control you used in the preceding section but will appear to work the same to users.

When you installed the Visual Basic programming environment on your computer, the VB Data Form Wizard wasn't installed. You must attach it to the Add-Ins menu.


Don't need the wizard anymore?

To remove the wizard from the Add-Ins menu, select VB 6 Data Form Wizard in the Add-In Manager dialog and deselect the Loaded/Unloaded and Load on Startup check boxes.


Install the Data Form Wizard

1. Choose Add-In Manager from the Add-Ins menu.

2. In the Add-In Manager dialog, select VB 6 Data Form Wizard from the list and select the Loaded/Unloaded check box (see Figure 30.11). Click OK.

FIGURE 30.11 If you select Load on Startup, the VB Data Form Wizard will be added to the Add-Ins menu after the installation process.

Now that you've attached the VB 6 Data Form Wizard to the Visual Basic IDE, you can use it in all your projects. You can now use the VB Data Form Wizard to create a form for the table tblWorks in the database file Composer.mdb.

Create a bound form for a database table

1. Choose Data Form Wizard from the Add-Ins menu.

2. Click Next in the Introduction dialog (see Figure 30.12).


Download this project's code

The form that these steps create is frmWorks, which is part of the project DataProj.vbp. It's located at http://www. mcp.com/info.


FIGURE 30.12 The Introduction dialog lets you load a profile of your common Data Form Wizard settings.

3. In the Database Type dialog, select Access and click Next.

4. In the Database dialog, click the Browse button to see the Access Database dialog. You can now select the database for which you want to make a form. Locate Composer.mdb and click Next.

5. Name the form frmWorks. Select Single Record from the Form Layout list, select ADO Data Control from the Binding Type options, and click Next (see Figure 30.13).

FIGURE 30.13 You select the layout that you want the data form to take by choosing an option from the Form Layout list.

6. Select tblWorks from the Record Source drop-down list (see Figure 30.14).

FIGURE 30.14 All tables and queries of the selected database are listed in the Record Source drop-down list. After you select the record source, the fields of the record source will appear in the Available Fields list.

7. Click the >> button and click Next (see Figure 30.15).

8. Don't deselect any of the check boxes so that all data manipulation controls are made available on your form (see Figure 30.16). Click Next.

9. Save the settings you've just made by clicking the ellipsis button (see Figure 30.17).

FIGURE 30.15 Any fields you select will appear on the data form. If you want only some fields, select them one at a time and click the > button. To remove fields from the data form, click the < and << buttons.

FIGURE 30.16 You can select a limited number of buttons to be on the data form by selecting or clearing the appropriate check boxes.

FIGURE 30.17 Saving your settings in a profile can save you time during database program development.

10. Click Finish. A final Data Form Created dialog will appear; click the Don't Show This Dialog in the Future check box if you don't want to see this confirmation message in the future. Click OK.

11. Save the code, remembering to change the name of the form to frmWorks.frm to follow the Visual Basic naming convention for forms.

12. Open the Project Properties dialog by selecting DataProj Properties from the Project menu.

13. Select the frmWorks form from the Startup Object drop-down list (see Figure 30.18).

14. Save and run the code (see Figure 30.19).

FIGURE 30.18 If you want a newly added form to be the startup form for your application, you must reset the Startup object.

FIGURE 30.19 The data form created by the wizard allows you to add, update, delete, and refresh data from the table assigned to the value of the Data control's RecordSource property.

You can use multiple Data controls on a form. Each Data control can be assigned a RecordSource from the same database or from different databases. You can also change a Data control's properties at runtime, but be advised that you will probably also have to change properties of the controls bound to the Data control.

The Data control makes working with databases simple, but this is partly because it's rather limited. Database programming is a complete discipline itself. As data structures and business requirements become more complex, so will the programming. After a time, you might outgrow the Data control in favor of a more advanced technology known as ActiveX Data Objects (ADO). Regardless of your future plans, the Data control will serve you well in most aspects of your database programming activity, especially for simpler applications.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.