Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Database Basics
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive 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 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } Search this book:  














Previous
Table of Contents
Next




For our musical recordings database, we will need the fields whose names and lengths are shown in Table 19.2. These are all Text fields. When you are finished, the Table Structure dialog box will resemble Figure 19.7.


Table 19.2 Text fields.



Field
Length

Composer/Group
40

Title
40

Media
4

Publisher
12

Location
6

Notes
50




Figure 19.7  The Table Structure dialog box after defining the database table’s fields.

The next step is to define an index for our table. This task is handled in the lower part of the Table Structure dialog box. A table does not have to have an index, but the advantages include faster queries and automatic sorting of records. Each index is linked to one field in the table, used for sorting the table (based on the data in that field) and for speeding queries on that field. While a table can have more than one index, we will create only one, linked to the Title field. Because we want the database sorted on this field, and because the database’s search capabilities will be limited to the Title field, this index will suit our needs perfectly.

To create the Title index, click on the Add Index button in the Table Structure dialog box to display the Add Index to Recordings dialog box, shown in Figure 19.8. Enter the index name—in this case, ByTitle—in the Name box, then click on the name of the field you want the index based on in the Available Fields list. This adds it to the Indexed Fields box. We’ll be using the Title field, of course. Turn the Primary and Unique options off, then click on OK. We could add another index at this point, but we are finished; click on Close to return to the Table Structure dialog box.
We could add more fields to the index definition. In the case of identical data in the first field, the data in these additional fields would be used to determine the index order. We need only the one field, however.
We are now finished defining the structure of the database table. Click on the Build the Table button, and VisData will generate the blank database based on the table specifications you entered. When it is complete, you will find a file named MUSIC.MDB located in the Visual Basic project directory. This is the database table that the database program will use. It contains no data yet, but it does have the table structure that we just defined.

Figure 19.8  Adding an index to the table.


TIP:  Modifying Database Tables
We can use the Visual Data Manager to open an existing database and change the field structure of a table. Most of these changes—modifying the data type of a field or changing its length—will result in data loss. This is one reason to plan carefully before designing the database tables and entering data. When changing the table structure becomes a necessity, you are almost always better off creating a brand-new table with the desired structure and then writing a Visual Basic program to transfer existing data from the old table to the new one. Doing this is easier than it might seem. Simply place two Data controls on a form, one linked to each table, and each with the required number of Text Box controls linked to it. Use the techniques covered later in this chapter to read each record from the old table into one set of Text Boxes, transfer it to the other set, and then save it in the new table.


We have only one more step before closing the Visual Data Manager. While we have defined the structure of our database table, it is empty—that is, it contains no data. This is okay, because you can place code in your Visual Basic program that will deal with an empty database table. We can skip adding this special code to our program, however, if we add at least a single record to the database table using Visual Data Manager. Real data is fine, although we can add a dummy record and instruct our users to delete it when they enter real data.
To add a record to the table we just defined, right-click on the Recordings table name in the Database window and select Open. The dialog box shown in Figure 19.9 will open, providing a Text Box for each of the fields in the table, plus a variety of Command Buttons. Click on the Add record button, then type the dummy record’s data into the fields in the dialog box. Enter anything you want, such as strings of Xs. Click on the Update button ton, followed by the Close button. The table now has one record in it, and you can close the Visual Data Manager (select File|Exit) and return to Visual Basic.

Figure 19.9  Entering a “dummy” record in the Recordings table.

Designing The Database Program’s Main Form
The program’s main form needs Text Box controls to display the database fields. It also requires labels to identify those Text Boxes; Command Buttons to carry out the various program actions; and a Data control to interface with the database. The form is shown in Figure 19.10, and its objects and properties are presented in Listing 19.14. Rather than guiding you through every step of creating this form, I’ll refer you to the figure and the listing, which provide all the necessary information. I will point out that the Command Buttons are in a control array, so it’s important to match the Caption and Index properties as indicated.



Previous
Table of Contents
Next