This chapter explains the basics of SQL statements as they apply to Visual Basic programming. All the examples in this chapter use an Access database. This chapter discusses both data manipulation language (DML) SQL statements and data definition language (DDL) SQL statements. Unless otherwise identified, SQL statements are of the DML type.
In this chapter, you learn how to do the following:
Structured Query Language (SQL) is a specialized set of programming commands that enable the developer (or end user) to do the following kinds of tasks:
SQL statements enable the developer to perform functions in one line or a few lines of code that would take 50 or 100 lines of standard BASIC code to perform.
As the name implies, Structured Query Language statements create a query that is processed by the database engine. The query defines the fields to be processed, the tables containing the fields, the range of records to be included, and, for record retrieval, the order in which the returned records are to be presented.
When retrieving records, a SQL statement usually returns the requested records in a dynaset. Recall that a dynaset is an updatable recordset that actually contains a collection of pointers to the base data. dynasets are temporary and are no longer accessible once they are closed. SQL does have a provision for the times when permanent storage of retrieved records is required.
The Microsoft SQL syntax used in this chapter is designed to work with the Jet database engine and is compatible with ANSI SQL (there are, however, some minor differences between Microsoft and ANSI SQL). In addition, if you use SQL commands to query an external database server such as SQL Server or Oracle, read the documentation that comes with the server to verify that the SQL features you want to use are supported and that the syntax of the statements is the same.
A SQL statement consists of three parts:
These parts are arranged as follows:
Most of this chapter uses only the manipulative statement and the options declarations. Using these two parts of the SQL statement, you can create queries to perform a wide variety of tasks. Table 6.1 lists the five manipulative clauses and their purposes.
Table 6.1 Parts of the Manipulative Statement
| Manipulative Statement | Function |
| DELETE FROM | Removes records from a table |
| INSERT INTO | Adds a group of records to a table |
| SELECT | Retrieves a group of records and places the records in a dynaset or table |
| TRANSFORM | Creates a summary table using the contents of a field as the column headers |
| UPDATE | Sets the values of fields in a table |
Although manipulative statements tell the database engine what to do, the options declarations tell it what fields and records to process. The discussion of the optional parameters makes up the bulk of this chapter. In this chapter, you first look at how the parameters are used with the SELECT statement and then apply them to the other manipulative statements. Many of the examples in this chapter are based on the sales transaction table of the sample Triton's Treasures database originally defined in Chapter 1.
The following discussions of the different SQL statements show just the SQL statement syntax. Be aware that these statements cannot be used alone in Visual Basic. The SQL statement is always used to create a QueryDef, to create a dynaset or snapshot, or as the RecordSource property of a data control. Following the explanation of the parts of the SQL statement is an explanation of how you can actually implement these statements in your programs. In addition, look back through Chapters 4 and 5 for other examples of using SQL statements.
The SELECT statement retrieves records (or specified fields from records) and places the information in a dynaset or table for further processing by a program. The SELECT statement follows this general form:
In demonstrating code statements, words in all caps are SQL keywords, and italicized words or phrases are used to indicate terms that a programmer would replace in an actual statement. For example, fieldlist would be replaced with Lastname, Firstname. Phrases or words inside the square brackets are optional terms.
The various components of this statement are explained in this chapter. Although a SQL statement can be greatly complex, it can also be fairly simple. The simplest form of the SELECT statement is shown here:
The fieldlist part of the SELECT statement is used to define the fields to be included in the output recordset. You can include all fields in a table, selected fields from the table, or even calculated fields based on other fields in the table. You can also choose the fields to be included from a single table or from multiple tables.
The field list portion of the SELECT statement takes the following form:
Selecting All Fields from a Table
The *, or wild-card, parameter is used to indicate that you want to select all the fields in the specified table. The wild card is used in the fieldlist portion of the statement. The statement SELECT * FROM Sales, when used with the sample database you are developing, produces the output recordset shown in figure 6.1.
Using * in the fieldlist parameter selects all fields from the source table.
Selecting Individual Fields from a Table
Frequently, you need only a few fields from a table. You can specify the desired fields by including a field list in the SELECT statement. Within the field list, the individual fields are separated by commas. In addition, if the desired field has a space in the name, such as Order Quantity, the field name must be enclosed within square brackets, [ ].The recordset that results from the following SELECT statement is shown in figure 6.2. A recordset created with fields specified is more efficient than one created with the wild card (*), both in terms of the size of the recordset and speed of creation. As a general rule, you should limit your queries to the smallest number of fields that can accomplish your purpose.
This recordset results from specifying individual fields in the SELECT statement.
Selecting Fields from Multiple Tables
As you remember from the discussions on database design in Chapter 1, you normalize data by placing it in different tables to eliminate data redundancy. When you retrieve this data for viewing or modification, you want to see all the information from the related tables. SQL allows you to combine information from various tables into a single recordset.
To select data from multiple tables, you specify three things:
Specify the table for each field by placing the table name and a period in front of the field name (for example, Sales.[Item Code] or Sales.Quantity). (Remember, square brackets must enclose a field name that has a space in it.) You also can use the wild-card identifier (*) after the table name to indicate that you want all the fields from that table.
To specify the tables you are using, place multiple table names (separated by commas) in the FROM clause of the SELECT statement.
The relationship between the tables is specified by either a WHERE clause or a JOIN condition. These elements are discussed later in this chapter.
The statement in listing 6.1 is used to retrieve all fields from the Sales table and the Item Description and Retail fields from the Retail Items table. These tables are related by the Item Code field. The results of the statement are shown in figure 6.3.
The listing shows an underscore character at the end of each of the first two lines. This is a line continuation character. It is used to tell Visual Basic that the next line is still part of the same statement.
Listing 6.1 Selecting Fields from Multiple Tables in a SQL Statement
Selecting fields from multiple tables produces a combined recordset.
You can leave out the table name when specifying fields as long as the requested field is present only in one table in the list. However, it is very good programming practice to include the table name, both for reducing the potential for errors and for readability of your code.
The example in listing 6.1 has customer order information consisting of the item ordered, quantity of the item, and the retail price. Suppose that you also want to access the total cost of the items. You can achieve this by using a calculated field in the SELECT statement. A calculated field can be the result of an arithmetic operation on numeric fields (for example, Price * Quantity) or the result of string operations on text fields (for example, Lastname & Firstname). For numeric fields, you can use any standard arithmetic operation (+, -, *, /, ^). For strings, you can use the concatenation operator (&). In addition, you can use Visual Basic functions to perform operations on the data in the fields (for example, you can use the MID$ function to extract a substring from a text field, the UCASE$ function to place text in uppercase letters, or the SQR function to calculate the square root of a number). Listing 6.2 shows how some of these functions can be used in the SELECT statement.
In the listing, no field name is specified for the calculated field. The Query engine will automatically assign a name such as Expr1001 for the first calculated field. The next section describes how you can specify a name for the field.
Listing 6.2 Creating a Variety of Calculated Fields with the SELECT Statement
Calculated fields are placed in the recordset as read-only fields: they cannot be updated. In addition, if you update the base data used to create the field, the changes are not reflected in the calculated field.
If you use a calculated field with a data control, it is best to use a label control to show the contents of the field. This prevents the user from attempting to update the field and causing an error. You could also use a text box with the locked property set to True. (You can learn more about the data control and bound controls in Chapter 5.)
Specifying Alternative Field Names
Listing 6.2 created calculated fields to include in a recordset. For many applications, you will want to use a name for the field other than the one automatically created by the Query engine.
You can change the syntax of the SELECT statement to give the calculated field a name. You assign a name by including the AS clause and the desired name after the definition of the field (refer to the second part of listing 6.3). You can also use this technique to assign a different name to a standard field if you want.
Listing 6.3 How to Access the Value of a Calculated Field and How to Name the Field
In addition to telling the database engine what information you want, you must tell it in which table to find the information. This is done with the FROM clause of the SELECT statement. Here is the general form of the FROM clause:
Various options of the FROM clause are discussed in the following sections.
The simplest form of the FROM clause is used to specify a single table. This is the form of the clause used in this statement:
The FROM clause can also be used to specify multiple tables (refer back to listing 6.1). When specifying multiple tables, separate the table names with commas. Also, if a table name has an embedded space, the table name must be enclosed in square brackets [ ] (refer back again to listing 6.1).
Using Tables in Other Databases
As you develop more applications, you may have to pull data together from tables in different databases. For example, you may have a ZIP code database that contains the City, State, and ZIP Code for every postal code in the United States. You do not want to duplicate this information in a table for each of your database applications that requires it. The SELECT statement allows you to store that information once in its own database and pull it in as needed. To retrieve the information from a database other than the current one, you use the IN portion of the FROM clause. The SELECT statement for retrieving the ZIP code information along with the customer data is shown in listing 6.4.
Listing 6.4 Retrieving Information from More Than One Database
Assigning an Alias Name to a Table
Notice the way the table name for each of the desired fields was listed in listing 6.4. Because these table names are long and there are a number of fields, the SELECT statement is fairly long. The statement gets much more complex with each field and table you add. In addition, typing long names each time increases the chances of making a typo. To alleviate this problem, you can assign the table an alias by using the AS portion of the FROM clause. Using AS, you can assign a unique shorter name to each table. This alias can be used in all the other clauses in which the table name is needed. Listing 6.5 is a rewrite of the code from listing 6.4 using the alias CS for the customers table and ZP for the ZIP code table.
Listing 6.5 Using a Table Alias to Cut Down on Typing
In most applications, you select all records that meet specified criteria. You can do this by specifying the ALL predicate in front of your field names or by leaving out any predicate specification (ALL is the default behavior). Therefore, the following two statements are equivalent:
However, there may be times when you want to determine the unique values of fields. For these times, use the DISTINCT or DISTINCTROW predicate. The DISTINCT predicate causes the database engine to retrieve only one record with a specific set of field values—no matter how many duplicates exist. For a record to be rejected by the DISTINCT predicate, its values for all the selected fields must match those of another record. For example, if you were selecting first and last names, you can retrieve several people with the last name Smith, but you cannot retrieve multiple occurrences of Adam Smith.
If you want to eliminate records that are completely duplicated, use the DISTINCTROW predicate. DISTINCTROW compares the values of all fields in the table, whether or not they are among the selected fields. For the pet store example, you can use DISTINCTROW to determine which products have been ordered at least once. DISTINCTROW has no effect if the query is only on a single table.
Listing 6.6 shows the uses of DISTINCT and DISTINCTROW.
Listing 6.6 Obtaining Unique Records with the DISTINCT or DISTINCTROW Predicates
When you design a database structure, you use key fields so that you can relate the tables in the database. For example, you use a salesperson ID in the Customers table to relate to the salesperson in the Salesperson table. You do this so that you don't have to include all the salesperson data with every customer record. You use these same key fields in the SELECT statement to set the table relationships so that you can display and manipulate the related data. That is, when you view customer information, you want to see the salesperson's name, not his or her ID.
There are two ways to specify the relationships between tables:
Using the WHERE clause to join tables creates a read-only recordset. To create a modifiable recordset, you must use the JOIN clause.
The basic format of the JOIN clause is as follows:
The query engine used by Visual Basic (also used by Access, Excel, and other Microsoft products) supports three types of JOIN clauses: INNER, LEFT, and RIGHT. Each of these clauses returns records that meet the JOIN condition, but each behaves differently in returning records that do not meet that condition. Table 6.1 shows the records returned from each table for the three JOIN conditions. For this discussion, table1 is the "left" table and table2 is the "right" table. In general, the left table is the first one specified (left side of the JOIN keyword) and the right table is the second table specified (right side of the JOIN keyword).
You can use any comparison operator (<, <=, =, >=, >, or <>) in the JOIN clause to relate the two tables.
Table 6.2 Records Returned Based on the Type of JOIN Used
| JOIN Type Table | Records from Left Table | Records from Right Table |
| INNER | Only records with corresponding record in right table | Only records with corresponding record in left table |
| LEFT | All records | Only records with corresponding record in left table |
| RIGHT | Only records with corresponding record in right table | All records |
To further explain these concepts, consider the sample database with its Customers and Salesperson tables. In that database, you created a small information set in the tables consisting of ten customers and four salespeople. Two of the customers have no salesperson listed, and one of the salespeople has no customers (he's a new guy!). You select the same fields with each JOIN but specify an INNER JOIN, LEFT JOIN, and RIGHT JOIN (refer to listing 6.7). Figure 6.4 shows the two base data tables from which this listing is working. Figure 6.5 shows the resulting recordsets for each of the JOIN operations.
Listing 6.7 Examples of the Three Different JOIN Types
The base tables used for the JOIN example.
Different records are returned with the different JOIN types.
Different records are returned with the different JOIN types.
Different records are returned with the different JOIN types.
Note that in addition to returning the salesperson with no customers, the RIGHT JOIN returned all customer records for each of the other salespeople, not just a single record. This is because a RIGHT JOIN is designed to return all the records from the RIGHT table even if they have no corresponding record in the LEFT table.
You can use the WHERE clause to relate two tables. The WHERE clause has the same effect as an INNER JOIN. Listing 6.8 shows the same INNER JOIN as listing 6.7 but using the WHERE clause instead of the INNER JOIN.
Listing 6.8 A WHERE Clause Performing the Same Function as an INNER JOIN
One of the most powerful features of SQL commands is that you can control the range of records to be processed by specifying a filter condition. You can use many types of filters such as Lastname = "Smith", Price < 1, or birthday between 5/1/94 and 5/31/94. Although the current discussion is specific to the use of filters in the SELECT command, the principals shown here also work with other SQL commands such as DELETE and UPDATE.
Filter conditions in a SQL command are specified using the WHERE clause. The general format of the WHERE clause is as follows:
WHERE logical-expression
There are four types of predicates (logical statements that define the condition) that you can use with the WHERE clause:
Using the Comparison Predicate
As its name suggests, the comparison predicate is used to compare the values of two expressions. There are six comparison operators (the symbols that describe the comparison type) you can use; the operators and their definitions are summarized in table 6.2.
Table 6.3 Comparison Operators Used in the WHERE Clause
| Operator | Definition |
| < | Less than |
| <= | Less than or equal to |
| = | Equal to |
| >= | Greater than or equal to |
| > | Greater than |
| <> | Not equal to |
Here is the generic format of the comparison predicate:
For all comparisons, both expressions must be of the same type (for example, both must be numbers or both must be text strings). Several comparisons of different types are shown in listing 6.9. The comparison values for strings and dates require special formatting. Any strings used in a comparison must be enclosed in single quotes (for example, 'Smith', 'AL'). Likewise, dates must be enclosed between pound signs (for example, #5/15/94#). The quotes and the pound signs tell the Query engine the type of data that is being passed. Note that numbers do not need to be enclosed within special characters.
Listing 6.9 Comparison Operators Used with Many Types of Data
With the LIKE predicate, you can compare an expression (that is, a field value) to a pattern. The LIKE predicate allows you to make comparisons such as Lastname starts with S, Titles containing SQL, or five-letter words starting with M and ending with H. You use the wild cards * and ? to create the patterns. The actual predicates for these comparisons would be Lastname LIKE 'S*', Titles LIKE '*SQL*', and Word LIKE 'M???H', respectively.
The LIKE predicate is used exclusively for string comparisons. The format of the LIKE predicate is as follows:
The patterns defined for the LIKE predicate make use of wild-card matching and character-range lists. When you create a pattern, you can combine some of the wild cards and character lists to allow greater flexibility in the pattern definition. When used, character lists must meet three criteria:
In addition to using a character list to match a character in the list, you can precede the list with an exclamation point to indicate that you want to exclude the characters in the list. Table 6.3 shows the type of pattern matching you can perform with the LIKE predicate. Listing 6.10 shows the use of the LIKE predicate in several SELECT statements.
Table 6.4 The LIKE Predicate Using a Variety of Pattern Matching
| Wild Card | Used to Match | Example Pattern | Example Results |
| * | Multiple characters | S* | Smith, Sims, sheep |
| ? | Single character | an? | and, ant, any |
| # | Single digit | 3524# | 35242, 35243 |
| [list] | Single character in list | [c-f] | d, e, f |
| [!list] | Single character not in list | [!c-f] | a, b, g, h |
| combination | Specific to pattern | a?t* | art, antique, artist |
Listing 6.10 The LIKE Predicate Provides Powerful Pattern Matching Capabilities
The IN predicate allows you to determine whether the expression is one of several values. Using the IN predicate, you can check state codes for customers to determine whether the customer's state matches a sales region. This example is shown in the following sample code:
The BETWEEN predicate lets you search for expressions with values within a range of values. You can use the BETWEEN predicate for string, numeric, or date expressions. The BETWEEN predicate performs an inclusive search, meaning that if the value is equal to one of the endpoints of the range, the record is included. You can also use the NOT operator to return records outside the range. The form of the BETWEEN predicate is as follows:
Listing 6.11 shows the use of the BETWEEN predicate in several scenarios.
Listing 6.11 Using the BETWEEN Predicate to Check an Expression against a Range of Values
The WHERE clause can also accept multiple conditions so that you can specify filtering criteria on more than one field. Each individual condition of the multiple conditions is in the form of the conditions described in the preceding sections. These individual conditions are then combined using the logical operators AND and OR. By using multiple condition statements, you can find all the Smiths in the southeast, or find anyone whose first or last name is Scott. Listing 6.12 shows the statements for these examples. Figure 6.6 shows the recordset resulting from a query search for Scott.
Listing 6.12 Combining Multiple WHERE Conditions with AND or OR
You can use multiple conditions to enhance a WHERE clause.
In addition to specifying the range of records to process, you can also use the SELECT statement to specify the order in which you want the records to appear in the output dynaset. The SELECT statement controls the order in which the records are processed or viewed. Sorting the records is done by using the ORDER BY clause of the SELECT statement.
You can specify the sort order with a single field or with multiple fields. If you use multiple fields, the individual fields must be separated by commas.
The default sort order for all fields is ascending (that is, A-Z, 0-9). You can change the sort order for any individual field by specifying the DESC keyword after the field name (the DESC keyword affects only the one field, not any other fields in the ORDER BY clause). Listing 6.13 shows several uses of the ORDER BY clause. Figure 6.7 shows the results of these SELECT statements.
When you are sorting records, the presence of an index for the sort field can significantly speed up the SQL query.
Listing 6.13 Specifying the Sort Order of the Output dynaset
The ORDER BY clause specifies the sort order of the dynaset.
You can use the SELECT statement to perform calculations on the information in your tables using the SQL aggregate functions. To perform the calculations, define them as a field in your SELECT statement using the following syntax:
The expression can be a single field or a calculation based on one or more fields such as Quantity * Price, or SQR(Datapoint). The Count function can also use the wild card * as the expression, because it only returns the number of records. There are 11 aggregate functions available in Microsoft SQL:
As with other SQL functions, these aggregate functions operate only on the records that meet the filter criteria specified in the WHERE clause. Aggregate functions are unaffected by sort order. Aggregate functions return a single value for the entire recordset unless the GROUP BY clause (described in the following section) is used. If GROUP BY is used, a value is returned for each record group. Listing 6.14 shows the SELECT statement used to calculate the minimum, maximum, average, and total sales amounts, and total item volume from the Sales table in the sample case. Figure 6.8 shows the output from this query.
Listing 6.14 Using Aggregate Functions to Provide Summary Information
A table showing the summary information from aggregate functions.
Creating record groups allows you to create a recordset that has only one record for each occurrence of the specified field. For example, if you group the Customers table by state, you have one output record for each state. This arrangement is very useful when combined with the calculation functions described in the preceding sections. When groups are used in conjunction with aggregate functions, you can easily obtain summary data by state, salesperson, item code, or any other desired field.
Most of the time, you want to create groups based on a single field. You can, however, specify multiple fields in the GROUP BY clause. If you do, a record is returned for each unique combination of field values. You can use this technique to get sales data by salesperson and item code. Separate multiple fields in a GROUP BY clause with commas. Listing 6.15 shows an update of listing 6.14 to add groups based on the salesperson ID. Figure 6.9 shows the results of the query.
Listing 6.15 Using the GROUP BY Clause to Obtain Summary Information for Record Groups
Using GROUP BY creates a summary record for each defined group.
The GROUP BY clause can also include an optional HAVING clause. The HAVING clause works similarly to a WHERE clause but examines only the field values of the returned records. The HAVING clause determines which of the selected records to display; the WHERE clause determines which records to select from the base tables. You can use the HAVING clause to display only those salespeople with total sales exceeding $3,000 for the month. This example is shown in listing 6.16; the output from this listing is shown in figure 6.10.
Listing 6.16 The HAVING Clause Filters the Display of the Selected Group Records
The HAVING clause limits the display of group records.
In all the examples of the SELECT statement used earlier in this chapter, the results of the query are output to a dynaset or a snapshot. Because these recordsets are only temporary, their contents exist only as long as the recordset is open. Once a close method is used or the application is terminated, the recordset disappears (although any changes made to the underlying tables are permanent).
Sometimes, however, you may want to store the information in the recordset permanently for later use. Do so with the INTO clause of the SELECT statement. With the INTO clause, you specify the name of an output table (and optionally the database for the table) in which to store the results. You may want to do this to generate a mailing list table from your customer list. This mailing list table can then be accessed by your word processor to perform a mail merge function or to print mailing labels. Listing 6.4, earlier in this chapter, generated such a list in a dynaset. Listing 6.17 shows the same basic SELECT statement as was used in listing 6.4, but uses the INTO clause to store the information in a table.
Listing 6.17 Using the INTO Clause to Save Information to a New Table
The table name you specify should be a new table. If you specify the name of a table that already exists, that table is overwritten with the output of the SELECT statement.
The DELETE statement is used to create an action query. Its purpose is to delete specific records from a table. An action query does not return a group of records into a dynaset like SELECT queries do. Instead, action queries work like program subroutines. That is, an action query performs its functions and returns to the next statement in the calling program.
The syntax of the DELETE statement is as follows:
DELETE FROM tablename [WHERE clause]
The WHERE clause is an optional parameter. If it is omitted, all the records in the target table are deleted. You can use the WHERE clause to limit the deletions to only those records that meet specified criteria. In the WHERE clause, you can use any of the comparison predicates defined in the section, "Using the Comparison Predicate," earlier in this chapter. Following is an example of the DELETE statement used to eliminate all customers who live in Florida:
DELETE FROM Customers WHERE State='FL'
Once the DELETE statement has been executed, the records are gone and cannot be recovered. The only exception is if transaction processing is used. If you are using transaction processing, you can use a ROLLBACK statement to recover any deletions made since the last BEGINTRANS statement was issued.
The INSERT statement is another action query, like the DELETE statement. The INSERT statement is used in conjunction with the SELECT statement to add a group of records to a table. The syntax of the statement is as follows:
You build the SELECT portion of the statement exactly as explained in the first part of this chapter. The purpose of the SELECT portion of the statement is to define the records to be added to the table. The INSERT statement defines the action of adding the records and specifies the table that is to receive the records.
One use of the INSERT statement is to update tables created with the SELECT INTO statement. Suppose that you are keeping a church directory. When you first create the directory, you create a mailing list for the current member list. Each month, as new members are added, you can either rerun the SELECT INTO query and re-create the table, or you can run the INSERT INTO query and add only the new members to the existing mailing list. Listing 6.18 shows the creation of the original mailing list and the use of the INSERT INTO query to update the list.
Listing 6.18 Using the INSERT INTO Statement to Add a Group of Records to a Table
The UPDATE statement is another action query. It is used to change the values of specific fields in a table. The syntax of the UPDATE statement is as follows:
You can update multiple fields in a table at one time by listing multiple field = newvalue clauses, separated by commas. The inclusion of the WHERE clause is optional. If it is excluded, all records in the table are changed.
Listing 6.19 shows two examples of the UPDATE statement. The first example changes the salesperson ID for a group of customers, as happens when a salesperson leaves the company and their accounts are transferred to someone else. The second example changes the retail price of all retail sales items, as can be necessary to cover increased operating costs.
Listing 6.19 Using the UPDATE Statement to Change Field Values for Many Records at Once
Data definition language (DDL) statements allow you to create, modify, and delete tables and indexes in a database with a single statement. For many situations, these statements take the place of the data access object methods described in Chapter 3, "Implementing the Database Design". However, there are some limitations to using the DDL statements. The main limitation is that these statements are supported only for Jet databases; remember that data access objects can be used for any database accessed with the Jet engine. The other limitation of DDL statements is that they support only a small subset of the properties of the table, field, and index objects. If you need to specify properties outside of this subset, you must use the methods described in Chapter 3.
Three DDL statements are used to define tables in a database:
To create a table with the DDL statements, you create a SQL statement containing the name of the table and the names, types, and sizes of each field in the table. The following code shows how to create the Orders table of the sample case.
Notice that when you specify the table name and field names, you do not have to enclose the names in quotation marks. However, if you want to specify a name with a space in it, you must enclose the name in square brackets, (for example, [Last name].)
When you create a table, you can specify only the field names, types, and sizes. You cannot specify optional parameters such as default values, validation rules, or validation error messages. Even with this limitation, the DDL CREATE TABLE statement is a powerful tool you can use to create many of the tables in a database.
By using the ALTER TABLE statement, you can add a field to an existing table or delete a field from the table. When adding a field, you must specify the name, type, and (if necessary) the size of the field. You add a field using the ADD COLUMN clause of the ALTER TABLE statement. To delete a field, you only need to specify the field name and use the DROP COLUMN clause of the statement. As with other database modification methods, you cannot delete a field used in an index or a relation. Listing 6.20 show how to add and then delete a field from the Orders table created in the preceding section.
Listing 6.20 Using the ALTER TABLE Statement to Add or Delete a Field from a Table
You can delete a table from a database using the DROP TABLE statement. The following simple piece of code shows how to get rid of the Orders table. Use caution when deleting a table; the table and all its data is gone forever once the command has been executed.
You can use two DDL statements with indexes:
You can create a single-field or multifield index with the CREATE INDEX statement. To create the index, you must give the name of the index, the name of the table for the index, and at least one field to be included in the index. You can specify ascending or descending order for each field. You can also specify that the index is a primary index for the table. Listing 6.21 shows how to create a primary index on customer number, and a two-field index with the sort orders specified. These indexes are set up for the Customers table of the sample case.
Listing 6.21 Create Several Types of Indexes with the CREATE INDEX Statement
Getting rid of an index is just as easy as creating one. To delete an index from a table, use the DROP INDEX statement as shown in the following example. These statements delete the two indexes created in listing 6.21. Notice that you must specify the table name for the index you want to delete.
As stated at the beginning of the chapter, you cannot place a SQL statement by itself in a program. It must be part of another function. This part of the chapter describes the various methods used to implement the SQL statements you can create.
The Jet engine provides an execute method as part of the database object. The execute method tells the engine to process the SQL query against the database. An action query can be executed by specifying the SQL statement as part of the execute method for a database. An action query can also be used to create a QueryDef. Then the query can be executed on its own. Listing 6.22 shows how both of these methods are used to execute the same SQL statement.
Listing 6.22 Execute a SQL Statement Using the Database Execute or Query Execute Method
Creating a QueryDef allows you to name your query and store it in the database with your tables. You can create either an action query or a retrieval query (one that uses the SELECT statement). Once the query is created, you can call it by name for execution (shown in the section "Executing an Action Query") or for creation of a dynaset (as described in the following section). Listing 6.22 shows how to create a QueryDef called Change Sales that is used to update the salesperson ID for a group of customers.
To use the SELECT statement to retrieve records and store them in a dynaset or snapshot, it must be used in conjunction with the OpenRecordset method. Using the OpenRecordset method, you specify the type of recordset with the options parameter. With this method, you can either use the SELECT statement directly or use the name of a retrieval query that you have previously defined. Listing 6.23 shows these two methods of retrieving records.
Listing 6.23 Using the Create Methods to Retrieve the Records Defined by a SELECT Statement
In addition to the use of SELECT statements to create dynasets and snapshots, the comparison WHERE and ORDER BY clauses are used in setting dynaset properties. The filter property of a dynaset is a WHERE statement without the WHERE keyword. When setting the filter property, you can use all the predicates described in the section, "Using the WHERE Clause," earlier in this chapter. In a like manner, the sort property of a dynaset is an ORDER BY clause without the ORDER BY keywords.
The data control uses the RecordSource property to create a recordset when the control is loaded. The RecordSource may be a table, a SELECT statement, or a predefined query. Therefore, the entire discussion on the SELECT statement (in the first part of this chapter) apply to the creation of the recordset used with a data control.
When you specify a table name for the RecordSource property, Visual Basic uses the name to create a SELECT statement such as this:
When you create and test your SQL statements, you can program them directly into your code and run the code to see whether they work. This process can be very time-consuming and frustrating, especially for complex statements. There are, however, three easier ways of developing SQL statements that may be available to you:
Users of Microsoft Excel or Office also have access to Microsoft Query, the tool in Access.
The VISDATA application, the Data Manager, and Access all have query builders that can help you create SQL queries. They provide dialog boxes for selecting the fields to include, and help you with the various clauses. When you have finished testing a query with either application, you can store the query as a QueryDef in the database. This query can then be executed by name from your program. As an alternative, you can copy the code from the query builder into your program using standard cut and paste operations.
The VISDATA application is more flexible in the types of SQL statements it can handle than the Data Manager. However, for simply creating recordsets, the Data Manager works quite well. Also, the Query Builder in the VISDATA application helps you create complex queries.
The VISDATA application allows you to open a database, construct and test SQL queries, and, if desired, create a QueryDef from the debugged SQL statement. To use the VISDATA application, you must run Visual Basic and load the VBP file. This file is the VISDATA.VBP file found in the sample applications subdirectory of Visual Basic. You can also compile the VISDATA application and add it to one of your Windows program groups. (The authors prefer the latter method.) Using the method you prefer, start the VISDATA application. Then open the File menu and select Open Database; when the dialog box appears, choose a database from the list of available databases. Once the database is opened, a list of tables and queries in the database appears in the left panel. The VISDATA application with the Triton database open is shown in figure 6.11.
You can use the VISDATA application to develop SQL queries.
To develop and test SQL statements, first enter the statement in the text box of the SQL dialog box (the one on the right of fig. 6.11). When you are ready to test the statement, click the Execute SQL button. If you are developing a retrieval query, a dynaset is created and the results are displayed in a data control (or a grid) if the statement has no errors. If you are developing an action query, a message box appears, telling you that the execution of the query is complete (again, assuming that the statement is correct). If you have an error in your statement, a message box appears informing you of the error.
The VISDATA application also includes a Query Builder that allows you to create a query by following these steps:
Once you have set the Query Builder parameters, you can run the query, display the SQL statement, or copy the query to the SQL statement window. The Query Builder provides an easy way to become familiar with constructing SELECT queries. Figure 6.12 shows the Query Builder screen.
You can use VISDATA's Query Builder to create SQL statements.
When you have developed the query to your satisfaction (either with the Query Builder or by typing the statement directly), you can save the query as a QueryDef in your database. In your Visual Basic code, you can then reference the name of the query you created. Alternatively, you can copy the query from VISDATA and paste it into your application code.
If you have a copy of Microsoft Access, you can use its query builder to graphically construct queries. You can then save the query as a QueryDef in the database and reference the query name in your Visual Basic code.
Developers always want to get the best possible performance from every aspect of their application. Wanting high performance out of SQL queries is no exception. Fortunately, there are several methods you can use to optimize the performance of your SQL queries.
The Microsoft Jet database engine uses an optimization technology called Rushmore. Under certain conditions, Rushmore uses available indexes to try to speed up queries. To take maximum advantage of this arrangement, you can create an index on each of the fields you typically use in a WHERE clause or a JOIN condition. This is particularly true of key fields used to relate tables (for example, the Custno and SalesID fields in the sample database). An index also works better with comparison operators than with the other types of WHERE conditions such as LIKE or IN.
Only certain types of queries are optimizable by Rushmore. For a query to use Rushmore optimization, the WHERE condition must use an indexed field. In addition, if you use the LIKE operator, the expression should begin with a character, not a wild card. Rushmore works with Jet databases and FoxPro and dBase tables. Rushmore does not work with ODBC databases.
Compiling a query refers to creating a QueryDef and storing it in the database. If the query already exists in the database, the command parser does not have to generate the query each time it is run, and this increases execution speed. If you have a query that is frequently used, create a QueryDef for it.
When you are working with a lot of data from a large number of tables, the SQL statements can become quite complex. Complex statements are much slower to execute than simple ones. Also, if you have a number of conditions in WHERE clauses, this increases complexity and slows execution time. Keep statements as simple as possible. If you have a complex statement, consider breaking it into multiple smaller operations. For example, if you have a complex JOIN of three tables, you may be able to use the SELECT INTO statement to create a temporary table from two of the three, then use a second SELECT statement to perform the final JOIN. There are no hard-and-fast rules for how many tables is too many or how many conditions make a statement too complex. If you are having performance problems, try some different ideas and find the one that works best.
Another way to keep things simple is to try to avoid pattern matching in a WHERE clause. Because pattern matching does not deal with discrete values, pattern matching is much harder to optimize. In addition, patterns that use wild cards for the first character are much slower than those that specifically define that character. For example, if you are looking for books about SQL, finding ones with SQL anywhere in the title (pattern = "*SQL*") requires looking at every title in the table. On the other hand, looking for titles that start with SQL (pattern = "SQL*") allows you to skip over most records. If you had a Title index, the search would go directly to the first book on SQL.
Visual Basic has the capability of passing a SQL statement through to an ODBC database server such as SQL Server. When you pass a statement through, the Jet engine does not try to do any processing of the query, but sends the query to the server to be processed. Remember, however, that the SQL statement must conform to the SQL syntax of the host database.
To use the pass through capability, set the option parameter in the OpenRecordset or Execute methods to the value of the constant dbSQLPassThrough.
The project file, CHAPTER6.VBP, on the companion disk contains many of the listings used in this chapter. Each listing is assigned to a command button. Choosing the command button creates a dynaset using the SQL statement in the listing and displays the results in a data bound grid. The form containing the grid also has a text box that shows the SQL statement.
To see how SQL statements are used in programs and with the data control, refer to the following chapters:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.
