Platinum Edition Using Visual Basic 5

Previous chapterNext chapterContents


- 28 -
Building Database Applications

Learn how to find out which information should be stored in the database.
Data in a database should be stored in a way that makes the information easy to retrieve and maintain.
The methods of the Data Access Objects allow you to write programs to create or modify a database.
The Visual Data Manager provides an easy way to create and modify databases for your programs.
If you have Access, you have the best tool for working with database structures.
You can create, delete, and modify tables in a database with SQL queries.

It's probably fair to say that most business-oriented computer applications work with data in one form or another. This data often is stored in one or more databases. Visual Basic can create powerful data-management programs with a little planning and effort. The most fundamental part of that planning is in how the data is structured. A poorly designed database can doom even the most well-intentioned program from the start. On the other hand, a well-designed database can make a programmer's life much easier.

Creating an organized data structure requires you to learn about two separate tasks. First, you must learn about how to design a database. In the design, you decide what data goes in the database and how it will be organized. Second, you must learn how to translate the design into the actual database. You can do so in a variety of ways. In this chapter, I walk you through the process of designing and creating a sample database that contains information about parents and their children. The database you create here will mirror a portion of the database used in one of my commercial applications that tracks the members of youth groups.

Designing a Database

Like most tasks, building a database starts with a design. After all, you wouldn't try to build a house without a blueprint, and most people wouldn't attempt to prepare a new dish without a recipe. Like these other tasks, having a good design for your database is a major first step to a successful project.

In designing a database application, you must set up not only the program's routines for maximum performance, but you must pay attention also to the physical and logical layout of the data storage. A good database design does the following:

Design Objectives

When you're creating the design for your database, you must keep several objectives in mind. Although meeting all these design objectives is desirable, sometimes they are mutually exclusive. The primary design objectives are:

Key Activities in Designing Your Database

Creating a good database design involves the following seven key activities:

Now, look briefly at the initial two activities in the list. First, take a look at modeling the application. When you model an application, you first should determine the tasks that the application is to perform. For example, if you're maintaining a membership list, you know that you want to create phone directories and mailing lists of the members. As you're determining the tasks to be performed by the application, you are creating what is called the functional specification. For a project that you are creating, you probably know all the tasks that you want to perform, but writing down these tasks in a specification document is a good idea. This document can help you keep focused on what you want your program to do. If you're creating the program for another person, a functional specification becomes an agreement of what the application will contain. This specification also can show milestones that need to be achieved on a set schedule.

When you're creating the program for other people, the best way to learn what task must be performed is to talk to the people requesting the work. As a first step, you can determine if they already have a system that they are looking to replace, or if they have reports that they want to produce. Then, ask a lot of questions until you understand the users' objectives for the program.

After you determine the functional specifications for the program, you can start determining what data the program needs. In the case of a membership application, knowing that you have to produce directories and mailing lists tells you that the database needs to contain the address and phone number of each of the members. Taking this situation a little further, you know that, by presorting mail by ZIP Code, you can take advantage of reduced rate postage. Therefore, you need an index or query that places the mailing list information in ZIP code order. So, you can see that the model not only tells you the data needed but also defines other components of the database, as well.

Organizing the Data

One of the key aspects of good database design is determining how the data will be organized in the database. To have a good design, you should organize the data in a way that makes the information easy to retrieve and makes maintenance of the database easy. Within a database, data is stored in one or more tables. For many database applications, you can accomplish efficient data management by storing data in multiple tables and by establishing relationships between these tables. In the following sections, you learn how to determine what data belongs in each table of your database.

Tables as Topics A table is a collection of information related to a particular topic. By thinking of a key topic for the table, you can determine whether a particular piece of data fits into the table. For example, if a country club wants to track information about members and employees, the club management might be tempted to put both in the same table (because both groups refer to people). However, look at the data required for each group. Although both groups require information about a person's name, address, and phone number, the employee group also requires information about the person's Social Security number, job category, payroll, and tax status. If you were to create just one table, many of the entries would be blank for the members. You also would have to add a field to distinguish between a member and an employee. Clearly, this technique would result in a lot of wasted space. It also could result in slower processing of employee transactions or member transactions because the program would have to skip a number of records in the table. Figure 28.1 shows a database table with the two groups combined. Figure 28.2 shows the reduction in the number of fields in a member-only database table.

FIG. 28.1
Combining the employee and member tables wastes a lot of space.

FIG. 28.2
A separate database table for members has only the relevant fields and is more efficient.

By thinking of the topic to which a table relates, you can determine more easily whether a particular piece of information belongs in the table. If the information results in wasted space for many records, the data belongs in a different table.

Data Normalization Data normalization is the process of eliminating redundant data within a database. Taking data normalization to its fullest extent results in each piece of information in a database appearing only once, although that's not always practical. Consider the example of order processing. For each item a person orders, you need the item's number, description, price, order number, and order date, as well as the customer's name, address, and phone number. If you place all this information in one table, the result looks like the table shown in Figure 28.3.

FIG. 28.3
Non-normalized data produces a large, inefficient data table.

As you can see, much of the data in the table is repeated. This repetition introduces two problems. The first problem is wasted space, because you repeat information. The second problem is one of data accuracy or currency. If, for example, a customer changes his or her phone number, you have to change it for all the records that apply to that customer--with the possibility that you will miss one of the entries. In the table in Figure 28.3, notice that Martha Smith's phone number was changed in the latest entry but not in the two earlier entries. If an employee looks up Martha Smith and uses an earlier entry, that employee would not find Martha's updated phone number.

A better solution for handling the data is to put the customer information in one table and the sales order information in another table. You can assign each customer a unique ID and include that ID in the sales order table to identify the customer. This arrangement yields two tables with the data structure shown in Figure 28.4.

FIG. 28.4
Normalized Customer and Order tables eliminate data redundancy.

With this type of arrangement, the customer information appears in only one place. Now, if a customer changes his or her phone number, you have to change only one record.

You can do the same thing to the items sold and order information. This leads to the development of four tables, but the organization of the tables is much more efficient. You can be sure that when information must be changed, it will change in only one place. This arrangement is shown in Figure 28.5. With the four-table arrangement, the Orders table and the Items Ordered table provide the links between the customers and the retail items they purchased. The Items Ordered table contains one record for each item of a given order. The Orders table relates the items to the date of purchase and the customer making the purchase.

FIG. 28.5
Complete normalization of the tables provides the greatest efficiency.

When information is moved out of one table and into another, you must have a way of keeping track of the relationships between the tables. You can do so through the use of data keys. For example, your Customers table has a field called CustNo. The Orders table also has a field called CustNo. These tables are said to be linked through that field. If a program needs to obtain information about the customer who made a particular order, that customer's record can be located quickly in the Customers table via the common CustNo field.

Child and Lookup Tables Another way to handle data normalization is to create what is known as a child table. A child table is a table in which all the entries share common information that is stored in another table. A simple example is a membership directory; the family shares a common last name, address, and phone number, but each family member has a different first name. The table containing the common information is called the parent table, and the table containing the member's first names is the child table. You use this data structure in the Youth system that is created later in the chapter. Figure 28.6 shows a parent table and its related child table. A lookup table is another way to store information to prevent data redundancy and to increase the accuracy of data entry functions. Typically, a lookup table is used to store valid data entries (for example, a state abbreviations table). When a person enters the state code in an application, the program looks in the abbreviations table to make sure that the code exists.

You also can use a lookup table in data ormalization. If you have a large mailing list, many of the entries use the same city and state information. In this case, you can use a ZIP Code table as a related table to store the city and state by ZIP Code (remember that each ZIP Code corresponds to a single city and state combination). Using the ZIP Code table requires that the mailing list use only the ZIP Code of the address, and not the city and state. During data entry, you can have the program check an entered ZIP code against the valid entries.

Rules for Organizing Tables Although no absolute rules exist for defining what data goes into which tables, here are some general guidelines to follow for efficient database design:


NOTE: As stated previously, the guidelines for defining tables are not hard-and-fast rules. Sometimes it makes sense for you to deviate from the guidelines.


Performance Considerations
One of the most frequent reasons for deviating from the guidelines just given is to improve performance. If obtaining a total sales figure for a given salesperson requires summing several thousand records, for example, you might find it worthwhile to include a Total Sales field in the Salesperson table that is updated each time a sale is made. This way, when reports are generated, the application doesn't have to do large numbers of calculations and the report process is dramatically faster.

Another reason to deviate from the guidelines is to avoid opening a large number of tables at the same time. Because each open table uses precious resources and takes up memory, having too many open tables can slow down your application.

Deviating from the guidelines results in two major consequences. The first is increasing the size of the database because of redundant data. The second is the possibility of having incorrect data in some of the records because a piece of data was changed and not all the affected records were updated.

There are tradeoffs between application performance and data storage efficiency. For each design, you must look at the tradeoffs and decide on the optimum design.


Using Indexes

When information is entered into a table, records usually are stored in the order in which they are added. This is the physical order of the data. However, you usually want to view or process data in an order different from the order of entry; that is, you want to define a logical order. You also frequently need to find a specific record in a table. Doing so by scanning the table in its physical order can be quite time-consuming.

An index provides a method of showing a table in a specific order. An index is a special table that contains a key value (usually derived from the values of one or more fields) for each record in the data table; the index itself is stored in a specific logical order. The index also contains pointers that tell the database engine where the actual record is located. This type of index is similar to the index in the back of this book. By using the book's index, you easily can look up key words or topics, because it contains pointers (page numbers) to tell you where to find the information.

Why Use an Index? The structure of an index allows for rapid data search and retrieval. If you have a table of names indexed alphabetically, you rapidly can retrieve the record for a specific name by searching the index. To get an idea of the value of such an index, imagine a phone book that lists the customer names in the order in which they signed up for phone service. If you live in a large city, finding a person's number could take forever, because you have to look at each line until you find the one you want. A table can have a number of different indexes associated with it to provide different organizations of the data. For example, an Employee table can have indexes on last name, date of birth, date of hire, and pay scale. Each index shows the same data in a different order, for a different purpose.


CAUTION: Although having many different views of the data may be desirable, keeping multiple indexes can take a toll on performance, since all indexes must be updated each time data changes. Once again, you must consider the tradeoffs in the database design.


NOTE: You also can create different views of the information in a table by sorting the records or by specifying an order using the ORDER BY clause of a Structured Query Language (SQL) statement. Even though indexes aren't used directly by the SQL engine, their presence speeds up the sorting process when an ORDER BY clause is present. You learn about this topic in detail in "SQL Primer," which is on the companion CD-ROM.

See "Setting the Sort Conditions," found in "SQL Primer" on this book's CD-ROM.

Single-Key Expressions The most common type of index is the single-key index, which is based on the value of a single field in a table. Examples of this type of index are Social Security number, ZIP Code, employee ID, and last name. If multiple records exist with the same index key, those records are presented in physical order within the sort order imposed by the single-key index. Figure 28.7 shows the physical order of a Names table and how the table appears after being indexed on the last name field.

FIG. 28.6
The physical and logical order of a table can be different. Logical order depends on an index.

Multiple-Key Expressions Although single-key expressions are valuable in presenting data in a specific order, imposing an even more detailed order on the table is often necessary. You can do so by using multiple-key indexes. As you can infer from the name, a multiple-key index is based on the values of two or more fields in a table. A prime example is to use last name and first name when indexing a membership list. Figure 28.8 updates the view of the table shown in Figure 28.7 to show how using the first name field to help sort the records changes the order of the table. As with single-key indexes, if the key values of several records are the same, the records are presented in physical order within the index order.

FIG. 28.7
Multiple-key indexes further refine the logical order of a table.


CAUTION: Although this point might be obvious, I must stress that the order of the fields in the index expression has a dramatic impact on the order of the records in the table. Indexing on first name and then last name produces different results than indexing on last name and then first name. Figure 28.9 shows the undesirable results of using a first name/last name index on the table used in Figure 28.7.

FIG. 28.8
An improper index field order yields undesirable results.

Using Queries

When you normalize data, you typically are placing related information in multiple tables. However, when you need to access the data, you want to see the information from all the tables in one place. To do so, you need to create recordsets that consolidate the related information from the multiple tables. You create a recordset from multiple tables by using a SQL statement that specifies the desired fields, the location of the fields, and the relation between the tables. One way of using a SQL statement is to place it in the OpenRecordset method, which you use to create the recordset. However, you also can store the SQL statement as a query in the database.

Using stored queries presents several advantages:

Implementing Your Design

The first step in implementing the database design is to create the database itself. There are three main methods of creating an Access database for use with Visual Basic. You can use the following:

Creating the Database

You can use Visual Basic's database commands to write a program that creates a database for use in your design work or to write a program that creates a new database while the program is running. Using the database creation commands is the only way you can make a new database at runtime. Using the program to create the database is particularly useful in creating and distributing commercial applications, because you don't have to worry about including and installing the database files with the application. If the code is included in your program, the database can be created the first time the user runs your application. Also, if future releases of the program require modifications of the database structure, you can use program commands to update the database on-the-fly, without requiring the user to handle conversions from the old format to the new.

Creating files at runtime also is useful if the user is expected to create different database files with the same structure but different user-defined names. Each time the user wants to create a new file, the program asks for the file name and then creates the database accordingly. As an example, a user might create a different database to hold data for each calendar year.


NOTE: In order to do any work with databases in your program, you must add a reference to the appropriate DAO object library (typically the Microsoft DAO 3.5 Object Library). This is done in the References dialog box, which is accessible by choosing Project, References.

To create a new database, follow these eight steps:

1. Create a new database object variable with the Dim statement.

2. Use the CreateDatabase method to create the new database.

3. Create TableDef objects with the Dim statement and CreateTableDef method.

4. Set the properties for the new tables.

5. Create Field and Index objects with the Dim statement and CreateField and CreateIndex methods.

6. Set the properties for the fields and indexes.

7. Use the Append method to add fields and indexes to the tables.

8. Use the Append method to add the table to the database.

The heart of Visual Basic's data access object structure is the DBEngine object, which represents the database engine that Visual Basic programs use to interface with physical databases. All data access objects are contained within the DBEngine object. Programs interface with the DBEngine object through Workspace objects, which represent sessions with the DBEngine object and are members of the DBEngine's Workspaces collection. Visual Basic creates a default Workspace object, Workspaces(0), that is used unless the program explicitly creates another one. When a program references the various data access objects, Visual Basic assumes that the objects are contained in this default Workspace object unless another one is named.

You will be using the CreateDatabase method, which is a method of a Workspace object, to create a database. Listing 28.1 shows the statements you will use in this example to define Database and Workspace objects, and then actually create a database using the Workspace object's CreateDatabase method.

Listing 28.1 Defining a Database Object and Creating a Database

`************************************
`Full syntax of CreateDatabase method
`************************************
Dim NewDb As Database, NewWs As Workspace
Set NewWs = DBEngine.Workspaces(0)
Set NewDb = NewWs.CreateDatabase("C:\YOUTH\YOUTHTRK.MDB",dbLangGeneral)

You can define any valid variable name as a database object by using the Dim statement. Although a literal file name ("C:\YOUTH\YOUTHTRK.MDB") is specified explicitly in the argument of the CreateDatabase method, you can use a string variable to hold the name of the database to be created. This arrangement gives the user the flexibility of specifying a database name meaningful to him or her, or it enables you to create multiple databases with the same structure.


TIP: If your program allows the user to create a database, you might want to use the CommonDialog control's File Open dialog box to retrieve the file name and path for the new database.

The constant dbLangGeneral represents the required Locale argument of the CreateDatabase method. It specifies the database's collating order (how strings are sorted and compared), which may be different depending upon the language and culture in which the program will be utilized. The constant dbLangGeneral that you used specifies that the database engine should use English sorting rules. The Visual Basic help screen "Data Access Objects (DAO) Constants" lists the constants that you can use for different languages in the CollatingOrder section.

Another (optional) argument, the options argument, is available for the CreateDatabase method that enables you to specify which version of the Jet database engine to use to create the database, and to specify whether the database should be encrypted. The default Jet engine for both Windows 95 and Windows NT is version 3.5.

To specify the options argument, supply the sum of the Visual Basic constants that define the options you want. The following lines show how you can change the code in Listing 28.1 to create a Jet 2.5 database and encrypt it (if, for instance, your program needed to share data with 16-bit applications):


Dim NewDb As Database, NewWs As Workspace
Dim DbOpts As Long, DbName As String
Set NewWs = DBEngine.Workspaces(0)
DbName = "C:\YOUTH\YOUTHTRK.MDB"
DbOpts = dbVersion25 + dbEncrypt
Set NewDb = NewWs.CreateDatabase(DbName, dbLangGeneral, DbOpts)


CAUTION: When you use the CreateDatabase method, a trappable error occurs if the file name to be created already exists. Include a trap for this error in your error-handling routine or, better yet, check for the existence of the file name before invoking the function, while you're still got a chance to specify a different name.

Creating a Table

Creating a database using the code in Listing 28.1 creates only a file on a disk. You can't do anything with that file until you create the tables and add them to the database (refer to Steps 3 through 8 in "Creating the Database," earlier in this chapter). You can think of the CreateDatabase method as simply building the shell of a warehouse. To store items, you still have to lay out the aisles and build the shelves. You do just that when you create the tables.

Defining the TableDef Object The first step in creating a new table is to create a new TableDef object. TableDef is short for "Table Definition." When you create a TableDef, you define what type of information will be stored in a table and some optional properties of that table. Using the TableDef object, you can set the properties for the new table. The following lines of code show how to create a TableDef object and give the table a name:

Dim NewTbl As TableDef
Set NewTbl = NewDb.CreateTableDef("Youth")

The Name property of the table is only one of several properties for the TableDef object, but it is typically the only one required for the creation of a Jet table. You can use some of the other properties (Attributes, Connect, and SourceTableName) when attaching an external table to the database; these can be set in successive arguments of the CreateTableDef method. You also can specify other properties by setting them equal to a value after the TableDef object has been created, as you do if you want to set the validation rule and validation error message for a table (as shown in the following section). These statements follow the CreateTableDef method:

NewTbl.ValidationRule = "Age > 0"
NewTbl.ValidationText = _
     "You cannot enter an age of 0 or less."

Defining the Fields After defining the TableDef object for the new table, you must define one or more Field objects. For each field, you must define its name and type. Depending on the type of field, you might be required to define other properties, or you might want to set some optional properties. For text fields, you must set the Size property to specify how long a string the field can contain. The valid entries for the Size property of the text field are 1 to 255. If you want to allow longer strings, you can set the field type to Memo, which allows over a gigabyte of text.

Listing 28.2 shows how field objects are created and field properties set for the Youth table of the sample application. You can specify the field name, type, and size as optional arguments of the CreateField method, or you could use the CreateField method without any arguments and then set all the field properties with assignment statements. Listing 28.2 shows both techniques. You must use an assignment statement to set any other properties. As an example of an assignment statement, the listing sets a validation rule for the age field.

Listing 28.2 Creating Field Objects and Setting Properties

Dim F1 As Field, F2 As Field, F3 As Field, F4 As Field
Dim F5 As Field, F6 As Field, F7 As Field
`***********************************************************
`Specify field name, type, and size as CreateField arguments
`***********************************************************
Set F1 = NewTbl.CreateField("LastName", dbText, 20)
Set F2 = NewTbl.CreateField("FirstName", dbText, 20)
Set F3 = NewTbl.CreateField()
`*******************************
`Explicitly set field properties
`*******************************
F3.Name = "Address"
F3.Type = dbText
F3.Size = 30
Set F4 = NewTbl.CreateField("Age", dbInteger)
`*************************************
`Set validation properties for a field
`*************************************
F4.ValidationRule = "Age > 0"
F4.ValidationText = "A person's age must be greater than 0."
Set F5 = NewTbl.CreateField("City", dbText, 20)
Set F6 = NewTbl.CreateField("State", dbText, 2)
Set F7 = NewTbl.CreateField()
F7.Name = "Birthdate"
F7.Type = dbDate

After you define each of the fields to include in the table, use the Append method of the TableDef object to add the fields to the table definition, as shown in Listing 28.3.

Listing 28.3 Adding Fields to the Table Definition

NewTbl.Fields.Append F1
NewTbl.Fields.Append F2
NewTbl.Fields.Append F3
NewTbl.Fields.Append F4
NewTbl.Fields.Append F5
NewTbl.Fields.Append F6
NewTbl.Fields.Append F7


NOTE: If you have a large number of fields, or if you want to create a generic routine for adding fields to a table, consider using an array to define your fields. By using arrays, you can write a simple FOR loop to add all the fields to the table (as shown in the following code statements). Depending on the structure of the table, you might be able to use a loop to set the type properties of several fields, although you must still define each field you intend to add to the table:
ReDim Fld(1 To 7) As Field
`***********************************************
`Field definition statements go here for each
`array element.
`***********************************************
FOR I = 1 To 7
 ewTbl.Fields.Append Fld(I)
NEXT I 

Setting Optional Field Properties In the preceding section, you learned how to specify the name of a field, the type of data it can store, and, for some fields, the size of the field. These elements are the minimum requirements for defining a field. However, you can set several other properties of a field to further define its behavior. The first of these properties is the Attributes property. Two key settings of this property are applicable to creating fields in a table. The first is the auto-increment setting, which tells the database to increment the value of a numeric field each time a new record is added. This setting can provide a record counter; you can use it to ensure a unique value in that field for each record. You then can use the auto-increment field as a primary key field. The auto-increment setting is valid only for fields with the Long data type. Another optional setting is the updatable setting, which enables you to specify whether a field can be changed. This setting is not typically used in initial table creation but can be useful in limiting access to information, particularly in a multi-user environment.

You set the Attributes property by assigning it a value in a code statement. For example, the following code segment creates a field and then specifies that it be used as a counter field by setting the Attributes property to auto-increment:

Set F1 = NewTbl.CreateField("YouthID", dbLong)
F1.Attributes = dbAutoIncrField

Note that dbAutoIncrField is a predefined constant that can be used to specify an auto- increment field. The other constants that you can use in the Attributes property are listed in Table 28.1. You can apply multiple settings to the Attributes property by combining the values of the constants and then setting the property to the sum of the values.

Table 28.1 The Attributes Settings to Control the Behavior of a Field

Constant Function
dbFixedField The length of the field is fixed.
dbVariableField The field size can change. (Text fields only.)
dbAutoIncrField The value of the field is incremented automatically by the database engine.
dbUpdatableField The value of the field can be changed.

In addition to the Attributes property, you can set several other optional properties for individual fields. As with the Attributes property, these optional properties are set using assignment statements; they cannot be set as part of the CreateField method. Table 28.2 lists optional field properties, their functions, and their default settings, if applicable.

Table 28.2 Optional Properties that Provide You Further Control over the Behavior of a Field

Property Function
AllowZeroLength Specifies whether the value of a Text or Memo field can be a zero-length (empty) string. The default is False; setting this option to True allows zero-length strings.
DefaultValue Allows you to specify a default value for the field.
Required Determines whether a value for the field must be entered. The default value is False.
ValidationRule Specifies criteria that must be met for the field before the record can be updated. The default value is no rule (an empty string).
ValidationText Specifies the error message that is displayed when the validation rule is not met. The default value is an empty string.


CAUTION: As mentioned, the default setting for the AllowZeroLength property is False. A zero-length string, therefore, cannot be used in the field. You might want to change this value for many of the fields you create, as your program might not need values for these fields. For example, you might have a field for a work phone but need to allow a zero-length string for people who don't work or don't provide the information.

Adding the Table to the Database The final step in creating a database is adding the table or tables to the database. Use the Append method of the Database object to accomplish this by appending the TableDef object you just created to the database's TableDefs collection (see the following code). The TableDefs collection contains one TableDef object for each table in the database. The code also shows the Close method, which closes the database file and releases the system resources associated with the Database object:

NewDb.TableDefs.Append NewTbl
NewDb.Close

Using a Query to Create a Table In the preceding sections, you learned how to use data access objects to create a table in a database. However, you also could use the SQL CREATE TABLE statement in a query to accomplish the same goal with less effort. In this query, you specify the table name, followed by the names, types, and, optionally, sizes of the fields to include in the table. The list of fields is enclosed in parentheses. The query is run using the Database object's Execute method. The following code shows how to create the Youth table containing the LastName, FirstName, Age, and Birthdate fields using a query:

Dim SQLSel As String
SQLSel = "Create Table Youth (LastName TEXT(20),FirstName TEXT(20),"
SQLSel = SQLSel & "Age INTEGER,Birthdate DATETIME);"
NewDb.Execute SQLSel

The main drawback of using a CREATE TABLE query is that you cannot use it to set optional properties of the fields. You can set these properties only with the data access objects and the CreateField method.

Creating Indexes

Defining indexes for a table is another key aspect of developing your database. The method for creating an index is closely related to the method for creating the table itself. For each index, you must assign a name, define the fields to include in the index, and determine whether the index is a primary index and whether duplicate values are allowed in the fields that comprise the index key.

To create an index, follow these six steps:

1. Use the CreateIndex method of the TableDef object to create the Index object.

2. Set any optional properties of the index (such as primary or unique).

3. Use the CreateField method of the Index object to create the Field objects.

4. Set any optional properties of the Field objects.

5. Append the Field object(s) to the Fields collection of the Index object.

6. Append the Index object to the Indexes collection of the TableDef object.

Two commonly used optional properties of the Index object are the Primary property and the Unique property. A primary index is one that is typically used for finding a specific record in a table. To make an index primary, set the Primary property to True. Making an index primary ensures that the value of the index key for each record is unique and that no null values exist.


CAUTION: If you create a primary index, you must include logic in your program to ensure that any records added have unique, non-null values for the fields in the primary index. If you attempt to add a record with a non-unique or null value, an error is generated.

Use the Unique property on a non-primary index to make sure that the values of fields other than the primary index field are unique (for example, to make sure that you enter a unique Social Security number for each employee in a table).


NOTE: You can specify only one primary index per table.

For the Field objects that will be part of the Fields collection of an Index object, the only property of concern for creating indexes is the Attributes property. This property determines whether the sort order of the field is ascending (from A to Z) or descending (from Z to A). The default value is ascending. If you want to sort the field in descending order, set the Attributes property to the constant dbDescending.

You can create a multiple-field index (for example, an index on the first and last names of a customer). To create such an index, simply set up multiple fields using the CreateField method, and then append these fields one-by-one to the Index object's Fields collection. Remember that the order of the fields can have a dramatic impact on the order of your records. The order of the fields in an index is determined by the order in which the fields are appended to the index, not the order in which the field objects are created.

As I described in the preceding section, after you create the fields and set the properties of the fields and index, use the Append method to add the fields to the index and the index to the table definition.


NOTE: You can create a maximum of 32 indexes per table.

For the sample case, create a primary index on the YouthID field and an index on the LastName and FirstName fields. You also might want to create an index on the Birthdate field in descending order. Listing 28.4 shows how you accomplish this task.

Listing 28.4 Creating Index Objects, Assigning Properties, and Adding Indexes to the Table

`*********************************
`Dimension the data access objects
`*********************************
Dim Idx1, Idx2, Idx3 As Index
Dim Fld1, Fld2, Fld3 As Field
`********************************
`Create the primary YouthID index
`********************************
Set Idx1 = NewTbl.CreateIndex("YouthID")
Idx1.Primary = True
Set Fld1 = Idx1.CreateField("YouthID")
Idx1.Fields.Append Fld1
NewTbl.Indexes.Append Idx1
`*********************
`Create the name index
`*********************
Set Idx2 = NewTbl.CreateIndex("Name")
Idx2.Unique = False
Set Fld1 = Idx2.CreateField("LastName")
Set Fld2 = Idx2.CreateField("FirstName")
Idx2.Fields.Append Fld1
Idx2.Fields.Append Fld2
NewTbl.Indexes.Append Idx2
`**********************************************
`Create the birthdate index in descending order
`**********************************************
Set Idx3 = NewTbl.CreateIndex("Born")
Set Fld1 = Idx2.CreateField("Birthdate")
Fld1.Attributes = dbDescending
Idx3.Fields.Append Fld1
NewTbl.Indexes.Append Idx3

Creating Relations

Earlier in this chapter, I described ormalizing data and the need to relate normalized tables. The Jet engine relates tables through the use of a Relation object stored in the database. The Relation object tells the database which two tables are related, which table is the parent, which is the child, and the key fields used to specify the relationship.

Follow these seven steps to create a relationship between two tables:

1. Use the Dim statement to define a Relation object variable.

2. Create the Relation object by using the CreateRelation method of the Database object.

3. Set the primary table and the foreign table properties of the relationship.

4. Create the relation field for the primary table by using the CreateField method of the Relation object.

5. Set the foreign field property of the Field object.

6. Append the field to the Relation object.

7. Append the Relation object to the database.

Listing 28.5 demonstrates the creation of a relationship, showing how to create a relation between the Family (primary) table and the Youth (foreign) table of the sample database.

Listing 28.5 Specifying a Relationship Between Two Tables Using the Relation Object

Dim NewRel As Relation
Dim Fld1 As Field
`**************************
`Create the Relation object
`**************************
Set NewRel = NewDb.CreateRelation("Parents")
`**********************************
`Set the properties of the relation
`**********************************
NewRel.Table = "Family"
NewRel.ForeignTable = "Youth"
`************************************************
`Create the relating field and set the properties
`************************************************
Set Fld1 = NewRel.CreateField("ParentID")
Fld1.ForeignName = "ParentID"
`*****************************************************************
`Append the field to the relation and the relation to the database
`*****************************************************************
NewRel.Fields.Append Fld1
NewDb.Relations.Append NewRel


TROUBLESHOOTING: When I try to create a relation in the database, I get the error message Parents is not an index in this table. You get this message if you do not have a primary index on the key field in the primary table. In the preceding case, the primary index must be on the ParentID field in the Family table. Although the documentation does make this point, you must have a primary key field in your primary table. This field identifies the records to the relationship.

Creating Queries

Using queries is a powerful way of gathering information from more than one table or of selecting information from a table that matches specific criteria (for example, customer records for people who live in Alabama). As you learn in Chapter 31, "Improving Data Access with Data Access Objects (DAO)," an object called a recordset can store this type of information for use in your programs. In fact, using a query is one method of creating a dynaset- or snapshot-type recordset. The advantage of creating a query is that the information about it is saved in the database itself, making it convenient to test and store information needed to create recordsets that are used often. You learn a lot more about SQL in "SQL Primer" found on the CD-ROM.

Setting Up the Query To create a query, you define a QueryDef object, and then use the CreateQueryDef method of the database. When calling the function, you must specify the name of the query. You can specify the SQL syntax of the query, or you can define the SQL statement in a separate program line. The following code shows two methods of creating a query:

Dim OldDb As Database, NewQry As QueryDef
Set OldDb = OldWs.OpenDatabase("C:\YOUTH\YOUTHTRK.MDB")
Set NewQry = OldDb.CreateQueryDef("Local")
NewQry.SQL = "SELECT * FROM Youth Where State = `AL';"
`*************************************************
`Alternative form of query creation statement.
`*************************************************
Set NewQry = OldDb.CreateQueryDef("Local", "SELECT * FROM _
    Youth Where State = `AL';")

The heart of defining queries is the SQL statement. This statement defines the fields to be included, the source of the fields, record filters, and the sort order of the resulting recordset.


NOTE: The Jet engine can store queries only for Access (Jet) databases. It can, however, use queries to retrieve the data in many database types, such as FoxPro, Paradox, dBase, SQL Server, and others.

Deleting a Query As with most other objects in the database, if you create a query, you might, at some time, need to delete it. If you have a query that you no longer need in your database, you can remove it by using the following command:

OldDb.DeleteQueryDef "Local"


CAUTION: When you use a query, you open the query by creating a data access object. Therefore, before deleting a query that has been used, you should close the associated query object. This way, you can ensure that the query is not in use and that no error occurs during deletion. The syntax for closing a query is NewQry.Close.

Creating a Database with Other Tools

Although the data access objects provide you with a way to create a database by using program code, this approach is not the only way to create a Jet database. Several other methods are available to you:

Using Visual Data Manager

The Visual Data Manager application that comes with Visual Basic provides you with an interactive way of creating and modifying databases. You can run this application by selecting the Visual Data Manager item from Visual Basic's Add-Ins menu.


NOTE: Visual Data Manager can work with Access (Jet), dBase, FoxPro, Paradox, and ODBC databases, as well as text files. Typically, in Visual Basic applications, you will use it to manipulate Access databases.

Visual Data Manager is also one of the sample applications that can be found in the Visual Basic folder when you installed Visual Basic. Examining this project can provide a tremendous education into creating database applications in Visual Basic.


The first step in creating a new database is to create the database file itself. This provides a physical location for the rest of your work. To do this in Visual Data Manager, first choose File, New. This brings up a submenu that allows you to specify the type of database to create. For the purpose of this discussion, you will create an Access (Jet) database by choosing the Microsoft Access item. This brings up another submenu from which you can choose the version of Access database to create. If you will be sharing data with users on a Windows 3.1 system, you should choose the 2.0 version; otherwise, choose the 7.0 version. Figure 28.10 shows the different menu levels for creating a database. After you have chosen the type of database, you are presented with the Select Microsoft Access Database to Create dialog box. This dialog box allows you to choose a name and folder for your database. After entering a name, click the Save button on the dialog box. This takes you to the design mode shown in Figure 28.11.

FIG. 28.9
The menus allow you to choose the type and version of database to create.

FIG. 28.10
The Visual Data Manager Database window provides access to the design functions for tables, fields, and indexes.

The Visual Data Manager presents the database information in a tree-like view. This type of view allows you quickly to see the tables and queries in the database. It also allows you to open the view further to see the fields and indexes of a table as well as its properties. Finally, you can open the view all the way up to see the properties of the individual fields.

Adding a New Table After creating the database, the next thing you will want to do is create tables. To create a new table, right-click anywhere in Database Window. Select the New Table item. This brings up the Table Structure dialog box, as shown in Figure 28.12. This dialog box shows you information about the table itself, as well as a list of fields and indexes in the table. There are also buttons in the dialog box to add and remove fields and indexes. To add fields to the table, click the Add Field button to bring up the Add Field dialog box, as shown in Figure 28.13.

FIG. 28.11
In the Table Structure dialog box, you can specify a table name.

FIG. 28.12
In the Add Field dialog box, you can specify the properties of the fields for a table.

After you are in the Add Field dialog box, you need to follow these steps for each field you want to add:

1. Enter the name of the field.

2. Select the field type from the Type drop-down list.

3. Enter the size of the field (if necessary).

4. Enter any optional parameters, such as validation rules.

5. Click the OK button to add the field to the table.

After you have entered all the fields for your table, click the Close button in the Add Field dialog box. This returns you to the Table Structure dialog box.

If you want to remove a field from the table, select the field name in the dialog box's field list, and then click the Remove Field button. When you are satisfied with the fields in the table, click the Build the Table button to create the table.

Making Changes to the Fields in Your Table After you have created the fields in the table, you can set or change a number of the field properties from the Table Structure dialog box. To modify the properties, select the field name in the Field List. The properties of the field that can be modified appear in the dialog box as enabled text or check boxes. All other properties appear as disabled controls.


TIP: You also can edit the properties of a field from the Database window of the Visual Data Manager. Simply expand the database view to show field properties and right-click the property to be edited. You then can select Edit from the pop-up menu to change the property.


NOTE: In Visual Basic, you cannot edit or delete any field that is part of an index expression or a relation. If you need to delete such a field, you must delete the index or relation containing the field and then make the changes to the field.

Adding an Index to the Table The Table Structure dialog box also allows you to add, modify, or remove indexes in the table. Any indexes currently in the table appear in the Index List at the bottom of the dialog box, as shown in Figure 28.14.

FIG. 28.13
You can add, edit, or delete indexes for a table from the Table Structure dialog box.

To add a new index, click the Add Index button; the Add Index dialog box then appears, as shown in Figure 28.15. In this dialog box, first enter an index name. Next, select the fields to be included in the index by clicking the fields in the Available Fields list. As you select each field, it is added to the Indexed Fields list in the order in which it was selected. By default, all fields are indexed in ascending order. To change the order to descending, precede the field name in the Indexed Fields list with a minus sign (-).

FIG. 28.14
The Add Index dialog box provides a visual means of creating the indexes for a table.

After you define the fields for the index, you can choose to require the index to be unique or to be the primary index (assuming there's not already a primary index) by selecting the appropriate check box in the window. When the index is completed to your liking, save it by clicking OK. The index you have just created is added to the index list on the Table Structure dialog box. To delete an index, simply select it in the list box and click Remove Index.

Returning to the Visual Basic Design Window Closing the Visual Data Manager window or selecting Exit from the File menu takes you back to Visual Basic's main design window. (You also can switch back and forth between the Data Manager and the Visual Basic design environment.) To manipulate databases without having to start Visual Basic every time, make the Data Manager application a program item in your Visual Basic group.

Using Microsoft Access

Another option for creating a Jet database for use with a Visual Basic application is to use Microsoft Access. Access has a good visual design interface for setting up tables, indexes, queries, and table relationships. Obviously, this option is available only if you own a copy of Access. Note that Visual Basic can work with databases created with any version of Access; however, in order to exploit the power of 32-bit databases, Access 95 or later must be used.

Third-Party Database Designers

In addition to Visual Data Manager and Access, a number of third-party programs enable you to create and maintain Jet databases. Some of them provide you with advanced data modeling capabilities. These modeling capabilities make it easy for you to determine what information goes in which table and to set up the relations easily between the tables. Then, after your data model is complete, the program automatically can generate the database for you.

Modifying the Database Structure

Even if you create the perfect database for an application, sooner or later someone will come along and say, "Well, I really need this program to handle other data, too." At this point, you must modify the structure of your database and tables. Modifications can take the form of new tables, new fields or indexes in tables, or changes in the properties of tables, fields, or indexes. On occasion, you also might have to delete a table, field, or index.

In the following sections, you learn about the modification of a database through the use of Visual Basic code. As with the creation of a database, you also can use Visual Data Manager application or Microsoft Access to perform the modifications.

Adding and Deleting Tables

To add a table, follow the same steps that you took to create tables in a new database:

1. Define the table, field, and index objects by using the Dim statement and appropriate create methods.

2. Define the properties of the table, fields, and indexes.

3. Append the fields and indexes to the table.

4. Append the table to the database.

To delete a table from a database, you need to delete its TableDef object from the database's TableDefs collection, as shown in this statement:

OldDb.TableDefs.Delete "Members"


CAUTION: Use the Delete method with extreme caution. When you delete a table, all fields, indexes, and--most importantly--data are deleted along with it. And when it's gone, it's gone. The only way to get the data back is to create the table again from scratch and reload all your data.

Adding, Deleting, and Editing Indexes

Adding a new index involves the same steps as creating an index for a new table. You must define an Index object, set its properties, and append it to the Indexes collection of the appropriate TableDef object. An example of these steps was shown earlier in Listing 28.4.

To delete an index, simply use the Delete method as shown in the following line of code. This code deletes the Born index from the Youth table:

OldDb.TableDefs("Youth").Indexes.Delete "Born"

You cannot modify the properties of an existing Index object. Therefore, if a change to an index is required, you must delete the old index from the table and create a new index with the new properties. You do so by using the methods shown in the section "Creating Indexes," earlier in this chapter.


NOTE: You cannot delete an index that is required by a relation. To delete such an index, you first must delete the relation.

Adding, Deleting, and Editing Fields

As you learned when creating a new database, you add a field to a table by defining the field object, setting its properties, and appending it to the table. These commands were presented earlier in Listings 28.2 and 28.3.

To delete a field, use the Delete method to remove the Field object from the appropriate TableDef object's TableDefs collection, as shown here. This example deletes the Address field from the Youth table:

NewDb.TableDefs("Youth").Fields.Delete "Address"

Unfortunately, you cannot change a field's properties directly. You can, however, accomplish this task in two indirect ways. If the table contains no data, or if you don't care about losing the data in the field, you can delete the field from the table and then re-create it with the new properties. If you have a table that contains data, and you want to preserve the data, you must create a whole new table (making the appropriate changes to the Field object), copy the data to the new table, and then delete the old table. The difficulty of this process of making changes to fields dramatically underscores the importance of a good initial design.

To move data from one table to another existing table, follow these steps:

1. Open both tables.

2. Set up a loop to process each record in the table currently containing the data.

3. Then, for each record in the old table, follow these steps:
a. Retrieve the value of each field to be transferred from the old table.

b. Add a record to the new table.

c. Set the values of the field in the new table.

d. Update the new table.


NOTE: If you have Microsoft Access, you can change the properties of a table's fields while preserving the fields' contents.

Remember that you cannot delete a field that is part of an index or relation.

Deleting a Relation

If you need to delete a relation, you can use the Delete method of the Database object to remove the Relation object from the database's Relations collection. The following statement shows how to delete the relation created earlier in Listing 28.5:

NewDb.Relations.Delete "Parents"

Using SQL to Modify the Database

Just as you can create a table with SQL statements, you also can modify or even delete a table by using SQL. To modify a table, you can use an ALTER TABLE query. By using this type of query, you can add a new field to the table or delete a field from the table. The following code segment shows how you can use an ALTER TABLE query to add an Address field and delete the Birthdate field from the Youth table created earlier:

`*********************************
`Add an address field to the table
`*********************************
NewDb.Execute "ALTER TABLE Youth ADD COLUMN Address TEXT(30);"
`*****************************************
`Delete the birthdate field from the table
`*****************************************
NewDb.Execute "ALTER TABLE Youth DROP COLUMN Birthdate;"

If you want, you also can delete an entire table by using a DROP TABLE query. The following statement deletes the entire Youth table:

NewDb.Execute "DROP TABLE Youth;"

Why Use a Program Instead of Visual Data Manager?

In this chapter, you have learned that the Visual Data Manager application and Microsoft Access can create, modify, and load data into a database. So the question you might be asking is: "Why do I ever need to bother with the Visual Basic program commands for these functions?" The answer is that, in many cases, you don't. If you have direct control over the database (that is, you are the only user or you can access the database at any time), you may never need to use program commands to create or change a database.

If, however, you have an application with many users--either throughout your company or across the country--using a program for data management offers several benefits. One benefit is in initial installation. If the database creation routines are in the program itself, you don't have to include empty database files on your setup disks. This can reduce the number of disks required, and it certainly reduces the possibility that a key file is left out. Along the same lines, a user accidentally may delete a database file, leading to the necessity to create a new one.

Another benefit occurs when you distribute updates to the program. With changes embedded in a program, your user merely can run the update program to change the file structure. He or she doesn't need to reload data into a new, blank file. Also, by modifying the file in place, you can preserve most structure changes in the database made by the end user.

Another reason for putting database creation and maintenance commands in a program is for performance considerations. Sometimes it is desirable, from a performance standpoint, to create a temporary table to speed up a program or to store intermediate results, and then delete the table at the completion of the program. You also might want to create a temporary index that creates a specific order or speeds up a search.

From Here...

In this chapter, you learned how to design and create a database for use in an application. To use the database, however, you must write a database access application. This topic is covered in other chapters of the book. For further information, refer to the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.