Chapter 2

Looking at the Capabilities of the Jet Engine

The Microsoft Jet (Joint Engine Technology) database engine that comes with Visual Basic (and is shared with Microsoft Access) provides the developer with a feature-rich database environment for creating any type of data access application. This chapter covers the following topics regarding the Jet database engine:

• How a database management system works
• Database management features supported by the Jet engine
• How the database management features are accessed from Visual Basic

What's New in This Release?

Bundled with Visual Basic 4 is the latest version of the Microsoft Jet engine. If you worked with data access applications in Visual Basic 3, you worked with version 1.1 of the Jet engine (or possibly version 2 with the compatibility layer). The following list provides a quick summary of the enhancements made to version 1.1 to result in the latest version of the Jet engine:

• Engine-level enforcement of referential integrity
• Support for cascading updates and deletes
• Engine-level field and table validation
• Data definition language queries for creating and modifying database objects
• Enhanced security model that allows permission assignments by database object and user

These features are discussed in greater detail later in this chapter. The implementation of many of the features is covered in other chapters in this book.

How a Database Management System Works

Most programs created by developers must be able to retrieve, analyze, and store information. Before the advent of database management systems (DBMS), the developer not only had to handle the user interface (input and output) and analytical portions of the program, but also had to develop the methods of storing and retrieving the data in either sequential or random access files. The developer also had to handle any forms of searches or indexing that the program required. This type of data handling is shown in figure 2.1.

Fig. 2.1

Data retrieval and storage without a DBMS was a major effort.

With a DBMS, the developer initially defines the type of data to be stored. When data must be retrieved or stored, the program issues a request to the DBMS, and all the gory details of data management are handled automatically. The DBMS also handles searches and creates indexes as instructed by the developer. Data handling using a DBMS is shown schematically in figure 2.2.

Fig. 2.2

Data handling is much easier with a database management system.

Advantages of a DBMS

As indicated in the preceding section, a DBMS frees developers from the tasks of defining the data storage and retrieval mechanisms. This provides several advantages.

First, the initial design of the program is easier because you don't have to program search algorithms or read-and-write statements to work with the current record. This can speed the initial development of a program.

Second, a DBMS makes it much easier to change the format of some of the data if it becomes necessary to do so (as it often does). With a DBMS, you change only the data definition stored in the database. The DBMS takes care of the rest. The routines in the programs that access an individual piece of data remain unchanged. Without a DBMS, you have to write a routine to port data files to the new format and then change the input and output statements in each affected program to reflect the change to the data format.

Finally, it is easier for users and other developers to create additional programs to access the data in the database. This is because they don't have to know the format in which the data was stored, only the names of the data fields and the types of data they contain. This information is easily found because most DBMSes contain methods to report the structure (field names and types) of the database.

Parts of a DBMS

A DBMS consists of two major parts, the programming interface (which consists of the user interface and the data retrieval requests) and the database engine. These two parts are shown in the DBMS block of figure 2.2.

Functions of the Programming Interface

The programming interface provides the commands that allow a program to tell the database engine what to do. The programming interface usually includes the following elements:

• A data definition language (DDL) that tells the database engine the format of the data objects (tables, records, fields, and indexes). The DDL also defines the data validation and data integrity rules for the database.
• A data manipulation language (DML) that tells the database engine the functions to perform on the data (retrieve, change, add, delete, store).
• A data control language (DCL) that tells the database engine what type of access is allowed to the data by various users.

Functions of the Database Engine

The simplest form of database engine provides mechanisms for the physical storage of the data, retrieval and updating of data, and data search and index capabilities. A database engine may also provide methods for ensuring data validity, data integrity, and data security.

The design of the database engine also determines what data manipulation features are supported. For example, if you need to increase the price of every item in a retail sales table, one database engine may support the use of "action queries" that allow a single program line to perform the function. With a different database engine, you may have to use a program loop to retrieve each record, change the price, and store the changes.

The Jet Database Engine

The Microsoft Jet database engine supports most of the functions described in the preceding section. The data definition features of Jet support the creation, modification, and deletion of tables, indexes, and queries. Jet also supports field level and record level data validation. Data integrity is supported in the form of primary keys and referential integrity between tables.

For data manipulation, Jet supports the use of Structured Query Language (SQL). SQL provides the means for a single statement to retrieve, add, delete, or update groups of records based on user-defined criteria. (The SQL supported by the Jet engine is close to but not fully compliant with ANSI SQL-89.) In addition, the Visual Basic-Jet engine combination supports the use of data access objects. These objects allow the developer to manipulate information in the database by setting the properties of the objects and executing the methods attached to the objects. Table 2.1 lists these objects and gives a brief description of their functions. How these data access objects are used to manipulate data is covered in Chapters 4 and 5.

Table 2.1 Visual Basic Data Access Objects

The data access objects and SQL statements are also used to provide data definition language support. The data definition language is used to create or modify the actual structure of the database. The use of some of the forms of data definition language is covered in Chapter 3, "Implementing the Database Design."

Jet also provides support for security features. These features allow the developer to assign a user ID and password that must be given before the user can access the database. Jet also supports the use of permissions, or access levels (for example, read-only or read/write accessibility), for individual tables and queries. This allows the database administrator to assign each user or group of users specific access to different parts of the database. For example, you can set up security so that everyone can look at the address and phone number information in an employee database but so that only the department manager can view salary and performance information.

Data Types Supported by the Jet Engine

The Jet engine supports a wide variety of data types, including several types of text and numeric fields. These different data types allow the developer a great deal of flexibility in designing a database application. Table 2.2 shows all the different data types available.

Table 2.2 The Data Types Available with the Jet Engine

Data Integrity and Validation

One of the key functions of a database application is ensuring, as much as possible, the accuracy of the data in the tables. Data accuracy refers not only to making sure that the individual data items are correct, but also to making sure that relationships between data tables are properly maintained. These two functions are referred to, respectively, as data validation and data integrity.

The Jet engine supports two main types of data integrity monitoring—primary-key integrity and referential integrity. It also supports two key forms of data validation—field-level validation and record-level validation. These items are discussed later in this section.

In Visual Basic 4, the developer can invoke all these integrity and validation features using the data access objects. The features are determined by setting the properties of the various objects at design time when the database and tables are created. (See Chapter 3 for more information on creating data tables and fields and setting the object properties.) In Visual Basic 3, these features (with the exception of primary-key integrity) could only be set using Microsoft Access.

Primary-Key Integrity

Primary-key integrity ensures that each record in a table is uniquely identified by a field or combination of fields. Unique keys are essential for properly relating tables. For example, if you make a sale to a customer named John Smith, but have no identifier other than his name, how do you determine which John Smith to send the bill to?

You can implement a primary key in either of two ways. You can define a unique field or combination of fields that is meaningful to you, or you can create a counter field. If you create a counter field, Jet automatically creates a new value for the field for each record you add, ensuring the uniqueness of the key. If you define your own field (for example, the first three letters of a person's first and last names, such as JOHSMI), you are responsible for making sure that the values are unique and for resolving any conflicts if a new value is not unique. That is, you must provide a program function that generates the key value (or gets an input value from the user) and verifies that the value is unique. If the value is not unique, you must provide a way to change the value and reverify its uniqueness.

With either method, Jet enforces primary integrity by verifying that the value of the primary key is unique before it allows the addition or updating of a record. If the value is not unique, a trappable error is returned. Your program must be able to handle this error.

Referential Integrity

To relate one table to another, the same value must appear in both tables. In a one-to-many relationship, the table on the one side of the relationship contains the primary key for the table (as described in the preceding section). The table on the many side of the relationship contains a reference to this primary key in a field. This field is known as the foreign key. Figure 2.3 shows the relationship between the Retail Items and Sales tables of the sample case, with the primary and foreign keys labeled.

Fig. 2.3

The foreign key in one table is related to the primary key in another table.

In this relationship, each record in the Retail Items table can be linked to many items in the Sales table. However, each item in the Sales table can be linked to only one item in the Retail Items table. A one-to-many relationship is often referred to as a parent-child relationship, with the primary-key table being the parent table and the foreign-key table being the child table.

Referential integrity is responsible for making sure that the relationship between two tables is maintained. Jet supports the following functions of referential integrity:

• When a foreign key is added or changed in a child table, the existence of the key value is verified in the parent table.
• When a new record is added to a child table, a foreign key must be specified.
• When a parent record is deleted, or the primary key is changed, Jet can either cascade the update or deletion through the child tables or reject the operation if there are child records. The choice of behavior is made by the developer.

With Visual Basic 4, you can define the relationships between tables using the data access objects available for relations. With previous versions of Visual Basic and versions of the Jet engine before 2.0, referential integrity was either not available or could be set only using Microsoft Access. If you have a copy of Access, however, it provides a nice graphical method of setting table relationships (see fig. 2.4).

Fig. 2.4

Relations between tables are made graphically in Microsoft Access.

Cascading Updates and Deletions

Allowing the cascading of updates and deletions from the parent table to the child tables is a new feature of the Jet engine, initially implemented in Jet 2.0. Cascading is an optional property of a relationship. You may choose to use cascading updates, cascading deletes, or both. If you choose these options, changes to the parent table are automatically propagated through the child table. For updates, if you change the primary key of the parent table, the related foreign keys in the child table are changed to reflect the new value. For deletions, if you delete a record in the parent table, all related child records are also deleted.

Without cascading, the programmer is responsible for handling conflicts that arise from making changes to a parent record when there are dependent records in a child table. The programmer must either verify that no child records exist before making the change or deletion, or let the program make the change when a record is found or an error occurs. If you do not have the cascading options turned on, you get an error when you try to change or delete a record with dependent records in a child table.

Cascading options are set when a Relation is created. The options are propeties of the Relation object. The tasks of creating a Relation and setting its properties are covered in Chapter 3, "Implementing the Database Design."

Although cascading is a very useful and powerful method for preserving referential integrity, it should be used with caution—especially cascading deletions. Accidentally deleting a parent record can wipe out quite a bit of data.

Handling Rejected Changes

For any type of data integrity rule defined in the database, the Jet engine issues an error message if the rule is violated. When this happens, the program must have routines to trap the error and either handle the problem or inform the user of decisions to be made about the erroneous data. If you do not include an error handling routine, the user sees the error message box, the attempted operation is aborted, and, in extreme cases, the program locks up.

You handle database errors the same as other errors in your code: by using the On Error GoTo statement and an error-handling routine. Visual Basic’s manuals and online Help cover basic error handling fairly well. Chapter 7, “Creating Multiuser Programs,” covers some of the specific database errors that you might encounter.

Data Validation

Data validation is the process of ensuring that the data input or changed by a user meets certain criteria. Data validation can take several forms, all of which are described in the following sections:

• Field level validation
• Record level validation
• Required field validation

Field Level Validation

Field level validation ensures that the information in the field is within a certain range of values. Jet supports the use of simple expressions in field level validation. A simple expression can compare the value of the field to a constant. Additional types of field level validation include the use of user-defined functions or checking for valid entries in another table. Although Jet does not support these types of validation at the engine level, they can be programmed into your Visual Basic code. Alternatively, if you are accessing an external database server, check to see whether these types of validation are supported by your host system.

Database applications created with Visual Basic can provide data validation functions using either engine-level validation or program validation. Engine-level validation uses rules about data fields and tables that are stored in the database itself. When data is changed, the Jet engine checks the data against the rule prior to writing the update to the database. If the new data does not conform to the rules, an error message is returned. Program validation is made up of data rules embedded in the actual program code. These rules check the value of the data against the defined criteria when particular program events occur. A program validation rule might be placed in the data-changed event of an object or the click event code of a command button.

In addition to setting a validation rule, you can also specify a custom message that displays if the validation rule is violated. This message is displayed in the standard message box when needed. In version 4, field level validation can be set by setting the optional ValidationRule and ValidationText properties of the data access objects. You can also use Microsoft Access or the Data Manager application that comes with Visual Basic. Figure 2.5 shows how the validation rule and text are set using the Data Manager. In Visual Basic, field level validation expressions are checked when an update method is called.

Fig. 2.5

You can set validation clauses for any field in a table.

Record Level Validation

Record level validation checks the information in a field against data in other fields of the record, or checks the results of a combination of fields against a criterion. For example, you can verify that the ratio of the retail price to the wholesale price is greater than 25 percent or that the length of the combined first and last name fields is less than 40 characters (for mailing labels). As with field level validation, Jet only supports the use of simple expressions for record level validation. In addition, only one validation criterion can be set for each table. As with field level validation, you can enter validation text that provides a custom error message if the validation rule is violated.

Record level validation can also be set using the data access objects or Microsoft Access. When your program violates the record level validation, a message is shown indicating the error. Any text you entered as validation text is included in the message that appears.

Required Field Validation

The Jet engine also allows you to specify any field as a required field. If you specify a field as required, a value must exist for it when a record is added or changed. Jet checks each required field for a null value whenever a record is updated. If you attempt to update a record without a value in a required field, an error is generated.

As with field validation, required field validation can be set using the data access objects, the Data Manager, or Microsoft Access.

If you specify required fields, make sure that you include the field on any data-entry form that adds new records to the table. Otherwise, you provide no means to input a value for the required field, and no new records can be created.

Support for Queries

The Jet engine supports the use of Structured Query Language (SQL) statements for defining and manipulating data. There are two main groupings of SQL statements, Data Manipulation Language statements and Data Definition Language statements.

Data Manipulation Language (DML)

The Data Manipulation Language statements provide the means to insert, delete, update, and retrieve groups of records in a database. Two basic types of queries are defined: action queries and retrieval queries.

Action Queries

Action queries operate on groups of records. These types of queries let you delete records, insert new records, update fields in records, or create new tables from existing records. Jet supports all of these types of action queries. The queries are based on SQL syntax. You can run action queries with either a database execute method or a query execute method. The syntax of these queries and how to execute them are discussed in detail in Chapter 6, "Understanding Structured Query Language (SQL)." Table 2.3 summarizes the action queries supported by the Jet engine.

The Execute method is one of the methods associated with the Database and QueryDef objects. By using this method, you can run a SQL query in your code.

Table 2.3 Action Queries Supported by the Jet Engine

The selection criteria for deleting records is defined by a logical expression such as Price > 1.00. Any record that meets this criteria is deleted; all other records are left alone. The syntax of this statement is explained further in Chapter 6, "Understanding Structured Query Language (SQL)."

Retrieval Queries

Retrieval queries tell the database engine to return a group of records in a dynaset or snapshot for viewing or processing. These queries are SQL SELECT statements that define the fields to be retrieved; the tables in which the fields are located; and the filter criteria for the fields. Jet supports standard SQL clauses such as WHERE, ORDER BY, GROUP BY, and JOIN. In addition, Jet supports the new clauses UNION, TOP n, and TOP n%. Jet also supports the use of subqueries, in which the results of one SELECT statement can be used as part of the WHERE clause of another statement. These capabilities of the SELECT statement provide the developer with a lot of flexibility in grouping and manipulating data. Table 2.4 summarizes the clauses of the SELECT statement supported by the Jet engine.

Table 2.4 Types of Retrieval Queries and Conditional Clauses Supported by the Jet Engine

Data Definition Language (DDL)

Data definition language (DDL) queries are a new feature of the Jet engine (versions 2.0 and later). In previous versions of the Jet engine, you created tables in a database by defining field and index objects and then adding them to the table definition. With the new DDL queries, you can issue a single command to create, change, or delete a table, and create or delete an index. Table 2.5 summarizes the various DDL queries.

Table 2.5 DDL Queries Used To Modify a Database Structure

Security

The security features of the Jet engine include the assignment of user IDs, passwords, and permissions for objects in the database. In Visual Basic 4, you control these features through data access objects. In earlier versions, security features could be set only using Microsoft Access.

The security features allow you to assign different permissions to each user or group of users for each object in the database. Table 2.6 summarizes the permission levels available for database objects.

Table 2.6 Security Features Available with the Jet Engine

From the application developer's standpoint, the only links to the security system are to include the user ID and password when opening a database that has the security features enabled.

Access to Other Databases

The Jet engine provides direct access to several external database formats: FoxPro, dBase, Paradox, and Btrieve. In addition, the Jet engine can link to any Open Database Connectivity (ODBC) database through the ODBC drivers.

From Here...

The capabilities of the Jet engine are more fully explained in other chapters of this book:

• Chapter 3, "Implementing the Database Design," explains more about creating and maintaining a database.
• Chapter 6, "Understanding Structured Query Language (SQL)," explains how to build SQL statements.
• Chapter 8, "Accessing Other Databases with the Jet Engine," explains how to access external databases.
• Chapter 9, "Accessing Other Databases through ODBC," explains how to access ODBC databases.