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.
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.
| 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.
| 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.
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
Download this project's codeYou 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.
FIGURE 30.4 Use the Data control to access a database and to bind controls on a form to the fields of a database.
The DatabaseName and Name propertiesThe 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 DatabasesCreating 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
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 DataFieldBe 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.
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.
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).
| 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.
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
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
Download this project's codeThe 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.
FIGURE 30.13 You select the layout that you want the data form to take by choosing an option from the Form Layout list.
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.
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.
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}