home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

Bookmark It

Search this book:
 
Previous Table of Contents Next


Defining Indexes

Along with setting field properties, the Visual Data Manager also defines indexes in a database. To review briefly, an index is a logical sorting, or ordering, of the records in a table, based on the data contained in one or more fields. To define an index for a table, start the Visual Data Manager and open the database file (if necessary). Then:

1.  In the Database Window, right-click on the table name and select Design from the pop-up menu. This opens the Table Structure dialog box.
2.  Click on the Add Index button to open the Add Index dialog box, shown in Figure 22.3.
3.  Type a descriptive index name in the Name box.
4.  In the Available Fields list, click on the name of the field or fields upon which this index will be based.
5.  Turn the Primary, Unique, and IgnoreNulls options on or off, as needed.
6.  Click on OK to define the index; click on Close after you have defined all needed indexes for this table.

The indexes to define for the GRAPEVINE database are shown in Table 22.1. For the database you are developing, use the techniques just described to define the indexes in Table 22.1.


TIP:  Tradeoffs In Using Indexes

Selection and design of indexes is a subjective process to some extent. Defining an index on a field is not necessary for sorting the records based on that field or searching for data in that field. However, having an index certainly speeds up these operations. Of course, overhead is involved in every index. Each time the user adds a new record or edits an existing record, all the table’s indexes are automatically updated by the database engine. This updating takes time. While the time may not be noticeable initially when the database is small, time can become a problem later when the database has grown to thousands of records.



Figure 22.3  The Add Index dialog box.

Table 22.1 GRAPEVINE database indexes and attributes.

Table Index Name Field Primary Unique
CUSTOMERS CustID CustID y y
Company Company n n
WINES StockNo StockNo y y
Type Type n n
RetailPrice RetailPrice n n
INVOICES InvNo InvNo y y
CustID CustID n y
ITEMS ItemNo ItemNo y y
StockNo StockNo n n
InvNo InvNo n n


TIP:  Maintaining Record Integrity

What is referential integrity? To answer this question, think for a moment about the purpose of a relationship between a dependent and a primary table. In a record in the dependent table, the value of the foreign key indicates or “points” to a record in the primary table that contains related information. For example, in the Invoices table of the GRAPEVINE) database, the value in the CustID field matches a single record in the Customers table. As a result, each Invoice record is linked to the required customer information. When referential integrity is enforced, the database engine checks to see whether the primary table contains a matching record whenever a record in the dependent table is entered or edited. If referential integrity is enforced for the Customers:Invoices relationship, the database engine checks for a matching record in the Customers table each time the user enters or edits a record in the Invoices table.

For the current demonstration, you don’t need to enforce referential integrity. As you’ll see later, the way you enter data into the foreign key fields ensures the existence of a matching primary key.


The Validate Event

A vital part of any well designed database program is validating the data being entered. By preventing the entry of invalid data, you can save a lot of headaches down the road. Certain kinds of validation can be accomplished by using the field object’s validation properties, as discussed earlier in the chapter. Considering the limitations of these properties, I have found the Validate event to be much more useful. This event applies to a wide variety of controls—anything that you would possibly use for data entry/editing in a database project. The Validate event works in tandem with the CausesValidation property. If the focus is moved from one control to another, the first control’s Validate event is fired if the second control’s CausesValidation property is True.

Here’s an example. You have a data entry form with a Text Box that is bound to an ADO Data control. The form also has an OK Command Button to confirm data entry, and a Help Command Button to display help information. Set the CausesValidation property to True for the OK button and to False for the Help button. If the user enters or edits data in the Text Box and then clicks on the OK button, the Text Box’s Validate event is fired, and code in the event procedure can be used to validate the data. In contrast, if the user clicks on the Help button, the Validate event is not fired.

The Validate event procedure has the following structure:

Private Sub object_Validate(KeepFocus As Boolean)
...
End Sub


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.