Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 3 -
Creating Queries with SQL


The Structured Query Language (SQL) is a standard language for defining and manipulating relational databases. Virtually all relational database products on the market today support SQL. The Jet database engine, the heart of Visual Basic and Microsoft Access, uses SQL as its primary definition and manipulation method.

For truly flexible, powerful database programming, SQL is a vital element. You can work with Visual Basic databases without SQL, of course; the Jet database engine provides a robust set of objects and capabilities to enable you to accomplish in Visual Basic almost anything you could accomplish in SQL.

Because of the versatility of the Jet database engine, the benefits of SQL are not readily apparent. To help you visualize the power that SQL can add to your database applications, examine the following example. Assume that you want to delete all the records with a ShipmentDate earlier than January 1, 1993, from the Orders table in your ACCOUNTS.MDB database. Your program could delete the records this way by using Jet Data Access Objects (commonly known as DAO objects):

Dim dbfAccounts as Database
Dim recOrders as Recordset
Set dbfAccounts = DBEngine.Workspaces(0).OpenDatabase("ACCOUNTS.MDB")
Set recOrders = dbfAccounts.OpenRecordset("Orders", dbOpenTable)
If recOrders.RecordCount > 0 Then
    recOrders.MoveFirst
    Do Until recOrders.EOF
        If recOrders("ShipmentDate") < #1/1/1993# Then recOrders.Delete
        recOrders.MoveNext
    Loop
End If
recOrders.Close
dbfAccounts.Close

Now, examine the following SQL example. (For an explanation of the dbfAccounts.Execute statement in this example, see How-To 3.13.)

Dim dbfAccounts as Database
Set dbfAccounts = DBEngine.Workspaces(0).OpenDatabase("ACCOUNTS.MDB")
dbfAccounts.Execute("DELETE Orders.* FROM Orders " & _
                    "WHERE Orders.ShipmentDate < #1/1/1993#")
dbfAccounts.Close

Both examples achieve the same result. However, the SQL result not only uses less code but also, in most cases, provides faster results than the Jet database example. The first example, employing Data Access Object (DAO) techniques, is forced to retrieve the entire table and then check each record one by one, deleting it if necessary. The second example, however, selects, checks, and deletes in one step, allowing the database, rather than Visual Basic, to manage the deletion.

Although the prospect of learning another language might be disquieting, you'll find that you can accomplish most SQL programming tasks with a very basic level of SQL training. The How-To's in this chapter illustrate the most important SQL statements and techniques. Furthermore, if you have a copy of Microsoft Access, you don't need to learn SQL to use SQL in Visual Basic. Access can write even the most difficult SQL for you by way of the various query design tools and wizards packaged with the application.

3.1 Create Recordsets by Selecting Records from Single Tables

The SELECT statement is the basic building block for retrieving records from a database with SQL. In this How-To, you'll learn how to create a SELECT statement that specifies fields, records, and a sort order.

3.2 Select Unique Field Values in a SQL Query

The SELECT statement normally returns one record for every record in the source table that meets the designated criteria. This How-To shows you how to modify the basic SELECT statement to ensure that the resulting recordset contains no duplicated records.

3.3 Use Variables and Visual Basic Functions in a SQL Query

The choice between SQL and regular Visual Basic code is not an either-or proposition. You can combine Visual Basic variables with SQL statements to create a powerful data management environment. In this How-To, you'll extend the basic SELECT statement with variables.

3.4 Use Wildcards and Ranges of Values in a SQL Query

Much of the power of SQL comes from the many ways you can specify recordsets. This How-To demonstrates the use of wildcards and the Between operator in SQL SELECT statements.

3.5 Define and Use a Parameter Query

The optimization process Microsoft Access uses on queries is more effective if the query is stored in the database and used multiple times. That can be difficult, however, when the query's parameters change often. A parameter query can bridge that difficult gap to give you both speed and flexibility, with remarkable ease of programming. This How-To demonstrates how to create and use a parameter query involving a single parameter.

3.6 Create Recordsets by Joining Fields from Multiple Tables

A properly designed relational database splits data into multiple tables and then relates those tables through key fields. Visual Basic SQL can "cement" fields from multiple tables together into dynaset- and snapshot-type recordsets. This How-To shows the technique for joining fields from multiple tables into unified recordsets through SQL INNER JOIN operations.

3.7 Find Records in a Table Without Corresponding Entries in a Related Table

You might need to identify records that have no corresponding entries in a related table--perhaps you're looking for Customer table records with no records in the Orders table, for example. This How-To shows how to use a SQL OUTER JOIN statement to locate these orphans.

3.8 Retrieve Information such as Counts, Averages, and Sums and Display It by Binding It to a Data Control

Sometimes you don't need the records themselves, just some statistics on records that meet certain criteria. This How-To shows you how to use SQL aggregate functions in SELECT statements to retrieve record counts, averages, sums, and other statistics.

3.9 Create a Recordset Consisting of Records with Duplicate Values

It's often useful to find duplicated values in a table--for example, you might want to find all cases in which the same customer was invoiced more than once on the same day. This How-To shows how to find the duplicate values in a table.

3.10 Use Visual Basic Functions Within a SQL Statement

Although SQL is a flexible and powerful method of database manipulation, sometimes you need extra power. Access and Visual Basic can make use of Visual Basic functions directly in a SQL query, ensuring your complete control over your data. This How-To illustrates how to employ Visual Basic functions in a SELECT query.

3.11 Make Bulk Updates to Database Records

In addition to SELECT statements, which retrieve recordsets from a database, SQL also provides a rich set of action statements. Action statements let you modify the contents of database tables. In this How-To, you'll see how to change values in existing records through SQL.

3.12 Create and Delete Tables

You can use SQL to create empty tables with a list of fields you specify. You can also use SQL to delete tables from your database. This How-To shows you how to accomplish both of these operations.

3.13 Append and Delete Records

Another type of action statement enables you to create a recordset and then append the records in that recordset to an existing table. In this How-To, you'll see how to accomplish this useful task, as well as how to delete records from a table.

3.14 Create a New Table with Data from Existing Tables

SQL action statements can also create new tables from records in existing tables. In this How-To, you'll create a new table from a recordset that you specify through SQL.

3.15 Modify a Table's Structure

SQL's capabilities don't stop at data manipulation. The data definition capabilities of SQL allow a great deal of control over a database's structure, as well as its data. This How-To demonstrates several ways a table can easily be modified with SQL statements.

3.16 Create a Crosstab Query

A crosstab report allows data to be cross-indexed in a compact, spreadsheet-like format. Once a difficult report to design, crosstab reports are now quick and easy. In this How-To, you'll receive an introduction in the ways of crosstab query design.

3.1 How do I...

Create recordsets by selecting records from single tables?

Problem

I want to select a subset of the records in a table, based on criteria I specify. I don't need to see all the fields for each record, but I do want to specify the order in which the records appear. How can I accomplish this task in Visual Basic by using SQL?

Technique

You create recordsets from data in tables through the SQL SELECT statement. You can embed the SQL statement in your Visual Basic code, or you can use it as the RecordSource for a Data control.

THE SQL SELECT STATEMENT

A basic single-table SQL SELECT statement has four basic parts, as shown in Table 3.1. Parts 1 and 2 are required in every SELECT statement. Parts 3 and 4 are optional. If you omit Part 3, the record-selection criteria, your recordset will consist of all the records in the table. If you omit Part 4, the sort order, the records will be ordered as they are in the table.

Table 3.1. The four parts of a basic SQL SELECT statement.

PURPOSE EXAMPLE
1. Specify which fields you want to see SELECT [Name], [Telephone]
2. Specify the table FROM [Publishers]
3. Specify the record-selection criteria WHERE [State] = "NY"
4. Specify the sort order ORDER BY [Name]

Combining the four example lines in the table produces this complete SQL SELECT statement:

SELECT [Name], [Telephone] FROM [Publishers] WHERE [State] = "NY" ORDER BY [Name]

This example is from the BIBLIO.MDB database supplied with Visual Basic. This database has a table named Publishers. Among the fields in the Publishers table are Name, Telephone, and State.

In the example, the field names and the table name are surrounded by square brackets, and the text NY is enclosed within quotation marks. These syntax requirements help the Jet database engine interpret the SQL statement. Table 3.2 lists the enclosure syntax requirements for SQL statements.

Table 3.2. The enclosure syntax requirements for SQL statements.

ELEMENT ENCLOSURES EXAMPLES WHEN REQUIRED
Numeric data None
Text data Single or double "NY" or `NY' Always quotation marks
Date data Pound signs #6/11/1996# Always
Field names Square brackets [Name], [Zip Code] When name has spaces or punctuation
Table names Square brackets [Publisher Comments] When name has spaces or punctuation


CAPITALIZATION DOESN'T MATTER

In the Table 3.1 example, the SQL keywords (SELECT, FROM, WHERE, and ORDER BY) appear in capital letters. This is a convention, and it is completely optional. Neither Visual Basic nor the Jet database engine cares about the capitalization of SQL keywords or about the capitalization of table names and field names.


Note that in the example from Table 3.1, the field and table names do not require brackets because all the names consist of a single word with no spaces or punctuation characters. The brackets are optional when there are no spaces or punctuation.

Multiple Fields and Multiple Criteria

If you need more than one field in the returned recordset, specify the fields by separating the field names with commas. Do the same to designate multiple-field sorts. The following example returns three fields sorted first by the State field and then by the City field:

SELECT [Name], [City], [State], FROM [Publishers]
ORDER BY [State], [City]

Specify multiple criteria through the AND and OR keywords. Assume that you have a table consisting of invoices with the fields shown in Table 3.3.

Table 3.3. Fields for a hypothetical Invoices table.

FIELD TYPE
Invoice Number Numeric
Issue Date Date
Amount Currency
Customer Number Numeric

You want to create a recordset consisting of invoices to customer number 3267 that were dated on or after August 1, 1995. Your SQL SELECT statement might look like this:

SELECT [Invoice Number], [Issue Date], [Amount] FROM [Invoices]
WHERE [Customer Number] = 3267 AND [Issue Date] >= #8/1/95#
ORDER BY [Issue Date]

Notice the use of the greater-than-or-equal-to operator (>=) in that statement. SQL comparison operators mimic those available in Visual Basic. Also notice that because the customer number is a numeric field, not a text field, the customer number is not enclosed in quotation marks.

In another situation, perhaps you want to find all invoices issued to customers 3267 and 3396. Your statement might be this:

SELECT [Invoice Number], [Issue Date], [Amount] FROM [Invoices]
WHERE [Customer Number] = 3267 OR [Customer Number] = 3396
ORDER BY [Issue Date]

You can combine AND and OR to select the invoices to customers 3267 and 3396 that were issued on or after August 1, 1995:

SELECT [Invoice Number], [Issue Date], [Amount] FROM [Invoices]
WHERE ([Customer Number] = 3267 OR [Customer Number] = 3396)
AND [Issue Date] >= #8/1/95# ORDER BY [Issue Date]

In the last example, the OR'd criteria are enclosed in parentheses. You do this to specify to the Jet engine the order in which it should evaluate the criteria. In this situation, "the ORs go together." You want to select the invoices that were sent to both customers after the specified date.

Using SQL Statements with the Data Control

When you use the Data control, you set the control's RecordSource property to specify the records that the control will display. The RecordSource property can be set to a table, to a stored query, or to a SQL SELECT statement. When you use a SQL SELECT statement as the RecordSource, you can specify records within a table by criteria you specify, and you can specify the order in which the records are presented. Because you cannot use indexes with the Data control, the ability to define the sort order is an important one.

Using SQL Statements with OpenRecordset

The Database object includes a method called OpenRecordset, with which you can create a dynaset or snapshot by using a SQL statement as the first argument. The SQL statement must be in the form of a string (that is, enclosed in quotation marks).

Assume that you have declared dbfTemp to be a Database object and recTemp to be a Recordset object and that you have set dbfTemp to point to a database. You can then create the recordset with the following statement (the statement must be all on one line, of course):

Set recTemp = dbfTemp.OpenRecordset("SELECT [Name], [Telephone] FROM [Publishers]
ÂWHERE [State] = `NY' ORDER BY [Name]")

After this statement has executed, recTemp represents a set of records that meet the criteria specified in the SQL statement. You can use any of the Recordset object's methods to work with these records.

Notice in the previous example that because the entire SQL statement is enclosed in double quotation marks, the text data within the SQL statement requires single quotation marks. If the text being enclosed contains single quotation marks, you should use a pair of double quotation marks. For example, assume that you are looking for records in which the company name is Joe's Beanery. The WHERE clause in your SQL statement would be this:

WHERE [Company Name] = "Joe's Beanery"

You can also assign the SELECT statement to a string variable and then use the string variable as the argument to the OpenRecordset method. Assume that sqlStmt has been declared as a string and that the following assignment statement appears all on one line:

sqlStmt = "SELECT [Name], [Telephone] FROM [Publishers] WHERE [State] = `NY'
ÂORDER BY [Name]" Set recTemp = dbfTemp.OpenRecordset(sqlStmt)

Because SQL statements can get very long--and you can't use Visual Basic's line-continuation feature in the middle of a quoted string--assigning SQL statements to strings often produces more readable code. You can build the string with the concatenation operator:

sqlStmt = "SELECT [Invoice Number], [Issue Date], [Amount] FROM [Invoices]"
sqlStmt = sqlStmt & " WHERE ([Customer Number] = 3267 OR [Customer Number] = 3396)"
sqlStmt = sqlStmt & " AND [Issue Date] >= #8/1/95# ORDER BY [Issue Date]"
Set recTemp = dbfTemp.OpenRecordset(sqlStmt) 

Steps

Open the project SELECT1.VBP. Change the DatabaseName property of the Data control Data1 to point to the copy of BIBLIO.MDB installed on your system (probably in the directory where VB6.EXE is installed). Then run the project. The form shown in Figure 3.1 appears. Scroll the top list to see the publishers in the database. Scroll the bottom list to see the titles in the database.

Figure 3.1. The SQL Select Form.

1. Create a new project called SELECT1.VBP. Use Form1 to create the objects and properties listed in Table 3.4, and save the form as SELECT1.FRM.

Table 3.4. Objects and properties for the Simple SELECTer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.1 Example
CommandButton Name cmdClose
Caption Close
Default True
ListBox Name lstTitles
Data Name dtaData
Caption dtaData
RecordSource SELECT [Company Name] FROM [Publishers] WHERE STATE = `NY' ORDER BY [Company Name]
Visible False
DBList Name dlstPublishers
RowSource dtaData
ListField Company Name

2. Add the following code to the declarations section of Form1. Ensure that your BIBLIO_PATH constant points to the location of your copy of BIBLIO.MDB, shipped with Visual Basic.

Option Explicit
`Change the following to point to your copy of BIBLIO.MDB.
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
3. Add the following code to the Load event of Form1. The Form_Load event will set the dtaData Data control's DatabaseName property, allowing it to retrieve data. Then, a snapshot-type Recordset object containing records from the Titles table where the [Years Published] field is equal to 1993 or 1994 is created, and the titles are added to the lstTitles list box. More detail about types of recordsets is provided in Chapter 2, "Accessing a Database with Data Access Objects."

PPrivate Sub Form_Load()
    Dim dbfBiblio As Database, recSelect As Recordset
    Dim strSQL As String
    `Set up the error handler.
    On Error GoTo FormLoadError
        `Get the database name and open the database.
        dtaData.DatabaseName = BIBLIO_PATH
        dtaData.Refresh
        Set dbfBiblio = _
DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        `Open a snapshot-type recordset on the [Titles] table,
        ` selecting only those titles published in 1993 or 1994,
        ` sorting by the ISBN number. Note the use of the line
        ` continuation character (_), used throughout the
        ` examples, to make code more readable. " & _
        strSQL = "SELECT [Title], [ISBN] FROM [Titles] " & _
            "WHERE [Year Published] = 1993"
 Or [Year Published] = 1994 " & _
            "ORDER BY [ISBN]"
        `Create the recordset.
        Set recSelect = dbfBiblio.OpenRecordset(strSQL, _
            dbOpenSnapshot)
        `Iterate through the recordset until the end of the file
        `(EOF) is reached.  Display each record in the unbound
        `list box lstTitles.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstTitles.AddItem recSelect![ISBN] & ":  " & _
                recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    Exit Sub
FormLoadError:
    `If an error occurs, display it with a MsgBox command.
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub


4. Add the following code to the Click event of cmdClose. The code in the cmdClose_Click event will end the application.

Private Sub cmdClose_Click()
    End
End Sub

How It Works

This How-To displays two lists, each showing the records in a recordset generated by a SQL SELECT statement. The top list is a DBList control bound to the Data control dtaData. The SQL statement that generates the recordset is supplied as the RecordSource property of the Data control. The bottom list is an unbound ListBox. Its recordset is generated by the OpenRecordset method called from the Form_Load event.

Comments

As basic as the code for this example might seem, it becomes the foundation for data access in Visual Basic using SQL. No matter which method you use (Data controls, Data Access Objects, or some of the other methods this book explores), SQL is the foundation on which these methods stand.

3.2 How do I...

Select unique field values in a SQL query?

Problem

I know that records in my table contain duplicate values in a particular field. How can I create a list of the unique values in the field?

Technique

By default, a SQL statement returns one row for each row in the table that meets the criteria specified in the statement's WHERE clause. If this action results in duplicate rows, these duplications are reproduced in the output recordset. For example, assume that your company provides products in several colors but uses the same product number for all colors of an otherwise identical product. You might have a [Products] table with this structure:

Product Number       Color
AGD44523             Green
AGD44523             Red
AGD44527             Red

You have two records with the [Product Number] field equal to AGD44523, each with a different entry in [Color]. Query the table with this SQL statement:

SELECT [Product Number] FROM [Products]

Included in your resulting recordset will be two identical rows, each with the value AGD44523.

Perhaps you want a list of unique product numbers, with no duplication. You can tell the Jet engine to filter duplicates out of the resulting recordset by inserting the keyword DISTINCT immediately after the word SELECT. You would rewrite your SQL statement like this:

SELECT DISTINCT [Product Number] FROM [Products]

That statement would result in a recordset with just one occurrence of the value AGD44523.


WHICH IS FASTER: SQL OR PROCEDURAL?

The question "Is SQL or the procedural approach faster when interfacing with the database?" is a complex one. In most cases, SQL methods will be faster than traditional procedural approaches. If an operation is time critical, it will probably be profitable to benchmark both the SQL approach and the procedural approach before making a decision on which to use in your production code.

An important class of exceptions to the generalization that "SQL is usually faster" is random access into an indexed table using the Seek method when each Seek operation is followed by operations on a small number of records. This code fragment provides an example:

Set recSelect = dbfTest.OpenRecordset("MyTable", dbOpenTable)
recSelect.Index = "MyIndex"
recSelect.Seek "=", intSomeValue
If Not recSelect.NoMatch Then
    ` Perform an action on the record sought
End If
` If needed, perform another Seek, or close the recordset and 
` move on.

In such cases, the traditional procedural methods are usually faster than SQL statements.


Steps

The BIBLIO.MDB database (supplied with Visual Basic) contains multiple publishers from the same state. Open the project SELECT2.VBP and run it. Initially, the list box on the form is blank. Click the Show All button, and the form then appears as shown in Figure 3.2; notice the occurrence of multiple CA entries. Click the Show Unique button, and the form appears as shown in Figure 3.3, with only one entry for each state.

Figure 3.2. The SQL Select Unique form after the Show All button is clicked.

Figure 3.3. The SQL Select Unique form after the Show Unique button is clicked.

1. Create a new project called SELECT2.VBP. Use Form1 to create the objects and properties listed in Table 3.5, and save the form as SELECT2.FRM.

Table 3.5. Objects and properties for the Distinct SELECTer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.2 Example
CommandButton Name cmdClose
Caption &Close Form
CommandButton Name cmdShowUnique
Caption Show &Unique
CommandButton CommandButton cmdShowAll
Caption Show &All
Data Name dtaData
DBList Name dlstData
DataSource State
RowSource dtaData

2. Add the following code to the declarations section of Form1:

Option Explicit
`Ensure that the following points to your copy of BIBLIO.MDB.
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"

3. Add the following code to the Click event of cmdShowAll. This code builds a SQL statement that creates a recordset with one record for every [Publishers] table row with a non-NULL [State] field and passes the SQL statement to the RefreshControls subroutine.




Private Sub cmdShowAll_Click()
    Dim strSQL As String
    `Perform the simple SELECT query.  Note the lack of the
    `DISTINCT keyword (see the cmdShowUnique_Click event
    `for more info.)
    strSQL = "SELECT [State] FROM [Publishers] " & _
        "WHERE [State] IS NOT NULL " & _
        "ORDER BY [State]"
    `Set the RecordSource and refresh the Data control and 
    `DBList control
    RefreshControls strSQL
End Sub
4. Add the following code to the Click event of cmdShowUnique. This code builds a SQL statement that creates a recordset with one record for every unique value in the [State] field of the [Publishers] table and passes the SQL statement to the RefreshControls subroutine.

Private Sub cmdShowUnique_Click()
Dim strSQL As String
    `Perform the SELECT DISTINCT query.  
    `Since the DISTINCT keyword is present, only 
    `the first instance of a given [State] value is represented 
    `in the result set.
    strSQL = "SELECT DISTINCT [State] FROM [Publishers] " & _
        "WHERE [State] IS NOT NULL " & _
        "ORDER BY [State]"
    `Set the RecordSource and refresh the Data control and 
    `DBList control
    RefreshControls strSQL
End Sub
5. Create the RefreshControls subroutine by entering the following code into Form1. This routine assigns the SQL statement received as the argument to the RecordSource property of the Data control. It then refreshes the Data control and the bound list box.




Private Sub RefreshControls(strSQL as string)
    dtaData.RecordSource = strSQL
    dtaData.Refresh
    dlstData.Refresh
End Sub
6. Add the following code to the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

The RecordSource property of the Data control dtaData is set to an empty string in the Properties window. At form load, therefore, the Recordset object of dtaData will be empty, and it will remain empty until the RecordSource property is set to something that will return a valid recordset and the Data control is refreshed. Because the DBList control is bound to the Data control, it will be empty while the Data control remains empty.

The Click routines of cmdShowAll and cmdShowUnique both perform the same basic function: They build a SQL statement to select the [State] field from the [Publishers] table and then pass the SQL statement to the RefreshControls subroutine. The difference in the Click routines is that cmdShowUnique includes the DISTINCT keyword in its SELECT statement and, therefore, returns only one record for each unique [State] value in the table.

Comments

In addition to the DISTINCT keyword described in this How-To, the Jet database engine also supports DISTINCTROW operations. When you use DISTINCTROW rather than DISTINCT, the database engine looks not only at the fields you've specified in your query but at entire rows in the table specified by the query. It returns one record in the dynaset for each unique row in the table, whether or not the output recordset row is unique.

Here's a simple example to illustrate the difference. Assume that the table [Cities] consists of these records:

CITY           STATE
Chicago        IL
Rockford       IL
Madison        WI
Madison        WI
Dubuque        IA

Here are two SQL queries:

SELECT DISTINCT [State] FROM [Cities]
SELECT DISTINCTROW [State] FROM [Cities]

The first SQL statement would return the following recordset. The DISTINCT statement ensures that each row in the output recordset is unique.

IL
WI
IA

The second SQL statement would return the following recordset. The IL entry appears twice because there are two unique records in the underlying table with a value of IL in the [State] field. The WI entry, on the other hand, appears only once because there is only one unique record in the underlying table.

IL
IL
WI
IA

In a well-designed database, a table will have no duplicate records because each record will have a primary key, and primary keys are by definition unique. If you have primary keys on all your tables, therefore, you have no need for DISTINCTROW.

3.3 How do I...

Use variables and Visual Basic functions in a SQL query?

Problem

SQL SELECT statements are useful tools. But if I have to hard-code the criteria into the statement, it limits my flexibility because I can't change the criteria at runtime. I'd like to be able to use variables in the criteria clauses of my SELECT statements or--even better--use Visual Basic functions that return values. How can I accomplish this?

Technique

The SQL statement that you pass to the OpenRecordset method of the Database object or that you assign as the RecordSource property of a Data control is a string. Because it is a string, you can insert the values of variables into it using Visual Basic's concatenation operator. You can use the same technique to insert the value returned by a call to a function (a built-in Visual Basic function or one you write yourself) into the string.

Steps

Open the project SELECT3.VBP and run the project. The form shown in Figure 3.4 appears. Scroll the top list to see the publishers in the database. Click on a publisher, and the titles for that publisher appear in the bottom list. Enter a year in the Year Published box, and click on a publisher to restrict the display to titles published by a specific publisher in a specific year.

Figure 3.4. The Variable SELECTer form at startup.

1. Create a new project called SELECT3.VBP. Use Form1 to create the objects and properties listed in Table 3.6, and save the form as SELECT3.FRM.

Table 3.6. Options and properties for the Variable SELECTer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.3 Example
CommandButton Name cmdClose
Caption Close
Default True
ListBox Name lstTitles
Data Name dtaData
Caption dtaData
RecordsetType Snapshot
RecordSource SELECT [Company Name] FROM [Publishers] ORDER BY [Company Name]
Visible False
DBList Name dlstPublishers
RowSource dtaData
ListField Company Name
Label Name lblYearPublished
AutoSize True
Caption Year Published:
TextBox Name txtYearPublished
Text ""

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Dim dbfBiblio As Database
3. Add the following code to the Load event of Form1:




Private Sub Form_Load()
On Error GoTo FormLoadError
        `Set the Data control and load the Database object 
        `dbfBiblio.
        dtaData.DatabaseName = BIBLIO_PATH
        dtaData.Refresh
        Set dbfBiblio = DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    On Error GoTo 0
Exit Sub
FormLoadError:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub
4. Add the following code to the Click event of dlstPublishers:

Private Sub dlstPublishers_Click()
Dim recSelect As Recordset
    Dim strSQL As String
    Dim intYearPublished As Integer
    On Error GoTo PublishersClickError
        `Clear the list box
        lstTitles.Clear
        `Confirm that the year is numeric; if so, set the
        `intYearPublished variable to its numeric value.
        If IsNumeric(txtYearPublished) Then intYearPublished = _
            Val(txtYearPublished)
        `Build the SQL statement
        strSQL = "SELECT [Title], [ISBN] FROM [Titles] " & _
            "WHERE [PubID] = " & GetPubID()
        `If the year published selection is greater than zero, 
        `modify the SQL to search for it.
        If intYearPublished > 0 Then
            strSQL = strSQL & " AND [Year Published] = " & _
                     intYearPublished
        End If
        `Sort the results by the ISBN number.
        strSQL = strSQL & " ORDER BY [ISBN]"
        `Get the recordset from our SQL statement.
        Set recSelect = dbfBiblio.OpenRecordset(strSQL, _
                        dbOpenSnapshot)
        `If we have obtained results, add the ISBN 
        `and Title fields to the list box.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstTitles.AddItem recSelect![ISBN] & ": " _
                & recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
PublishersClickError:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub
When the user clicks on a publisher name, this subroutine opens a snapshot-type Recordset object created from the Titles table, selecting only those titles published by the selected publishing company and, if the user has entered a publication year in txtYearPublished, in the designated year. It sorts the records in the snapshot by the ISBN number.

The WHERE clause in the SQL statement includes the value returned from the function GetPubID. GetPubID returns a numeric value corresponding to the Publishers table's PubID field for the currently selected publisher in dlstPublishers. Its value can be inserted into the SQL string by concatenating the function call to the string.

If the user has entered a publication year into txtYearPublished, its value is assigned to the numeric variable yrPublished and then inserted into the SQL string by concatenating the variable yrPublished to the string. Note that both values added to the string (the return value of GetPubID and the value of yrPublished) represent numeric fields in the database. Therefore, neither value is delimited by quotation marks in the SQL statement.

5. Create the following function in Form1. This function creates a recordset consisting of the PubID field of the record from the Publishers table with the company name value of the current selection in the dlstPublishers list. (This code assumes that each record in Publishers has a unique company name.) It then returns the value of that PubID field. In the SQL statement, the value dblPublishers.Text is used as the criterion of the WHERE clause. Because this value is a string (text) value, it must be delimited by quotation marks in the SQL statement. A single pair of double quotation marks--one at the beginning of the variable name and one at the end--won't do because the entire SQL statement is in quotation marks. You could use single quotation marks, like this:

strSQL = strSQL & " WHERE [Company Name] = `" & dblPublishers.Text _
       & "`"
This would work if you could be sure that the value dblPublishers.Text would never include an apostrophe. But because you can't be sure of that (in fact, BIBLIO.MDB does contain one publisher, O'Reilly & Associates, with an apostrophe), a double quotation mark is the safest course.

Function GetPubID() As Long
Dim recPubID As Recordset
    Dim strSQL As String
    `This subquery, once constructed, selects the publisher ID
    `given a company name.
    strSQL = "SELECT [PubID] FROM [Publishers] " & _
        "WHERE [Company Name] = """ & dblPublishers.Text & """"
    `Construct the recordset from our SQL statement.
    Set recPubID = dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
    `If we have a record, get the ID.  If not, return zero.
    If recPubID.RecordCount > 0 Then
        GetPubID = recPubID![PubID]
    Else
        GetPubID = 0
    End If
End Function
6. Add the following code to the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

When the form loads, it opens the database by setting the value of the Database object variable dbfBiblio to BIBLIO.MDB. The Publishers list is a bound list and is filled on startup by the records specified in the RecordSource property of Data1 (see How-To 3.1 for a discussion of this bound list and Data control). The Titles list is initially empty, and it remains so until the user clicks on the name of a publisher. Then the dlstPublisher_Click event code fills the Titles list with the titles published by the selected publisher. It does this by building a SQL statement that includes the PubID of the selected publisher. If the user enters a year in the Year Published text box, its value is appended to the WHERE clause as an additional criterion.

Because the dlstPublishers list does not include the PubID field, its Click event needs to retrieve the value of the PubID field for the selected record. It does this by a call to the GetPubID function. GetPubID returns a numeric value, which is inserted directly into the SQL string.

Comments

You can also use built-in Visual Basic functions in your SQL statement. The functions are evaluated at runtime and their return values inserted into the SQL statement passed to the Jet engine. For example, if you have an integer variable named intIndex, you could use the built-in Choose function to build a SQL statement like this:

strSQL = SELECT * FROM Orders WHERE [Delivery Service] = `" & _
Choose(intIndex, "Speedy", "Rapid", "Quick", "Rabbit", "Tortoise") & "`"

3.4 How do I...

Use wildcards and ranges of values in a SQL query?

Problem

I need to create recordsets where the records returned fall within a range of values or contain certain text strings. How can I do this with SQL?

Technique

You might need to create recordsets that consist of records that fall within a range of values. Or perhaps you need to create recordsets consisting of records in which a given field contains a certain text string. You can accomplish both of these tasks with SQL.

Finding Records Within Criteria Ranges

You can use the standard comparison operators to find records that have a field value within a range of values. For example, to find all records in the Invoices table with Invoice Date values between January 1, 1996, and January 15, 1996, you can use this statement:

SELECT * FROM [Invoices]
WHERE [Invoice Date] >= #1/1/1996# AND [Invoice Date] <= #1/15/1996#

As an alternative, you can use the SQL Between operator. This statement returns the same recordset as the preceding one:

SELECT * FROM [Invoices]
WHERE [Invoice Date] Between #1/1/1996# AND #1/15/1996#

Using Wildcards in String Criteria

You can find records containing designated strings of text within text fields by using the wildcard characters * and ? with the SQL Like operator. The asterisk matches any combination of characters. The question mark matches a single character.

This statement retrieves all records that have the Company field beginning with mcgraw:

SELECT * FROM [Publishers] WHERE [Company] LIKE "mcgraw*"

This statement retrieves all records with the last name Hansen or Hanson:

SELECT * FROM [Authors] WHERE [Last Name] LIKE "hans?n"

You can use more than one wildcard character in a string. This statement retrieves all the records in which the [Company Name] field includes the word hill:

SELECT * FROM [Publishers] WHERE [Company Name] LIKE "*hill*"

Steps

Open and run the project SELECT4.VBP. The form shown in Figure 3.5 appears. Enter *visual basic* in the text box labeled Title Includes Text, and click the Look Up button. Enter 1992 and 1993 in the Published Between boxes, and click Look Up again. Delete the values from the Published Between boxes; then change the entry in the Title Includes Text box to visual basic* and click Look Up. Change the text in the Title Includes Text box to *visual basic and click Look Up.

Figure 3.5. The Wildcard SELECTer form on startup.

1. Create a new project called SELECT4.VBP. Use Form1 to create the objects and properties listed in Table 3.7, and save the form as SELECT4.FRM.

Table 3.7. Options and properties for the Wildcard SELECTer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.4 Example
ListBox Name lstTitles
TextBox Name txtPartialTitle
TextBox Name txtStartYear
TextBox Name txtEndYear
Label Name lblPartialTitle
AutoSize True
Caption Title includes text:
Label Name lblStartYear
AutoSize True
Caption Published between:
Label Name lblEndYear
AutoSize True
Caption and
CommandButton Name cmdLookup
Caption &Look Up
Default True
CommandButton Name cmdClose
Caption &Close

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = "D:\Program Files\Microsoft Visual Studio\
ÂVB6\Biblio.MDB"
3. Add the following code to the Click event of cmdLookup. This sets the values of the three variables to be inserted into the SQL statement to
the contents of the three text boxes. It uses the IIf function to set the variables to default values if a text box is blank or if one of the Year text boxes contains a nonnumeric value. It then builds the SQL statement, inserting the variable values into the WHERE clause, opens the recordset, and fills the list with the recordset contents. (See How-To 3.3 for a discussion of using Visual Basic variables in SQL statements.)

Private Sub cmdLookup_Click()
Dim dbfBiblio As Database, recSelect As Recordset
    Dim strName As String, strSQL As String
    Dim strTitleText As String, strStartYear As String, strEndYear As String
    On Error GoTo LookupError
        `Clear the list box
        lstTitles.Clear
        `Construct the search strings, using wildcards where 
        `appropriate. For example, if the txtPartialTitle field is 
        `blank, the * wildcard is substituted.
        strTitleText = IIf(txtPartialTitle <> "", _
                       txtPartialTitle, "*")
        strStartYear = IIf(IsNumeric(txtStartYear), _
                       txtStartYear, "1")
        strEndYear = IIf(IsNumeric(txtEndYear), _
                     txtEndYear, "9999")
        `Open the database
        Set dbfBiblio = _
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        `Build the SQL statement, substituting our search strings, 
        `built above, in the appropriate locations.
        strSQL = "SELECT [Title] FROM [Titles] " & _
            "WHERE [Title] LIKE `*" & strTitleText & "*' " & _
            "AND [Year Published] BETWEEN " & strStartYear & _
            " AND " & strEndYear & _
            " ORDER BY [Title]"
        `Construct the SQL statement.
        Set recSelect = _
            dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
        `If we get results, load the Title field of each record 
        `into the list box.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstTitles.AddItem recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
LookupError:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub
4. Add the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

The true action of this sample application occurs in the cmdLookup_Click event. After clearing the contents of the lstTitles list box, the code uses the values supplied in the text boxes to construct a SQL statement to run against the dbfBiblio Database object. If records were retrieved after the statement was run with the OpenRecordset method, the lstTitles list box would be populated with the contents of the [Title] field from each record.

Comments

A Visual Basic database stores a date field as a number. In the WHERE clause of a SQL statement, you can treat it like a number; using the Between operator or comparison operators like >= or <= returns the results you would expect.

However, you can treat the date field like text in the WHERE clause of a SQL statement. This method enables you to use wildcard characters for any of the three values in a standard date.

For example, this WHERE clause returns all records with an invoice date in January 1996:

WHERE [Invoice Date] LIKE "1/*/1996"

The following WHERE clause returns all records with an invoice date in 1996:

WHERE [Invoice Date] LIKE "*/*/1996"

Notice that when you use the Like operator and wildcard characters, you delimit the date with quotation marks--not pound signs. Quotation marks tell the Jet database engine, "Treat this date like a string." The pound signs tell it, "Treat this date like a number."

3.5 How do I...

Define and use a parameter query?

Problem

I need to create recordsets with search criteria based on a parameter that will change often and reload quickly each time the parameter changes.

Technique

Normally, when using SQL to search for a specific value or values, you would specify which values you wanted in the statement. For example, to retrieve names and telephone numbers from the [Publishers] table in which the [State] field was equal to "NY", you would use this SQL statement:

SELECT [Name], [Telephone] FROM [Publishers] WHERE [State] = "NY" _
       ORDER BY [Name]

But this limits you. If you wanted all the names and telephone numbers from a different state, you would create a new query. There is, however, a faster way. A parameter query is a special SQL query in which replaceable parameters are used. Think of a parameter in SQL as a variable in Visual Basic. This allows your query to be flexible, and it also allows an increase in performance because the SQL precompiler doesn't have to completely build a new query every time you change a parameter.

To use a parameter in your SQL statement, you first have to specify the parameter--like declaring a variable in Visual Basic. You do this in the PARAMETERS section of your query, which usually precedes the SELECT statement. The declaration, as in Visual Basic, consists of a name and a data type, although the data types vary slightly from the names you might be accustomed to in Visual Basic. The PARAMETERS section is separated from the SELECT section by a semicolon (;) so that the SQL precompiler can tell the difference between the two sections.

To rewrite the preceding query for use with parameters, you might use something like this:

PARAMETERS prmState String; SELECT [Name], [Telephone] FROM [Publishers] _
     WHERE [State] = [prmState] ORDER BY [Name]

The parameter is substituted for the search value but in all other respects does not alter the SQL statement.

Steps

Open and run the project SELECT5.VBP. The form shown in Figure 3.6 appears. Enter NY in the State Abbreviation text box and click Search. Enter any other value in the State Abbreviation text box and click Search again.

Figure 3.6. The SQL Parameter Query form on startup.

1. Create a new project called SELECT5.VBP. Use Form1 to create the objects and properties listed in Table 3.8, and save the form as SELECT5.FRM.

Table 3.8. Options and properties for the Parameter SELECTer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.5 Example
Label Name lblParameter
Caption State abbreviation:
TextBox Name txtParameter
Label Name lblResults
Caption Results:
ListBox Name lstResults
CommandButton Name cmdSearch
Caption &Search
CommandButton Name cmdClose
Caption &Close

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
        "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private mdbfBiblio As Database, mrecSelect As Recordset, _
        mqdfTemp As QueryDef
3. Add the following code to the Load method of Form1. The CreateQueryDef method is used on the database to create a QueryDef object, which will hold the parameter query. Later, the QueryDef will be used to create a recordset for display.

Private Sub Form_Load()
    `Open a Database object first - the familiar BIBLIO.MDB
        Set mdbfBiblio = 
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    `Use the CreateQueryDef method to create a temporary QueryDef 
    `object that will store our parameter query.  The best way to 
    `use a parameter query in DAO is with the QueryDef object.
    Set mqdfTemp = mdbfBiblio.CreateQueryDef("")
    `Set the SQL property to our parameter query SQL statement.
    mqdfTemp.SQL = "PARAMETERS pstrState String;SELECT " & _
        "[Name],[Telephone] " & _
        "FROM [Publishers] WHERE [State] = [pstrState] " & _
        "ORDER By [Name]"
End Sub
4. Add the following code to the Click event of cmdSearch. Now that you have a possible value for your parameter, you reference the pstrState parameter of the QueryDef object you created in the Form_Load routine. Then, using the QueryDef, you create a recordset. Now, the best part of this is when you change the parameter; instead of recreating the recordset, you use the Requery method provided by the Recordset object. Using this method is much faster because the recordset has an existing connection to the database and has its SQL already defined; you're just changing a parameter.

Private Sub cmdSearch_Click()
    Dim lstrTemp As String
    `Set the parameter to the contents of our text box
    mqdfTemp![pstrState] = txtParameter.Text
    `If we haven't run this query yet, we'll need to
    `create it.  If we have, we don't need to create it,
    `just to requery it.
    If mrecSelect Is Nothing Then
        Set mrecSelect = mqdfTemp.OpenRecordset()
    Else
        mrecSelect.Requery mqdfTemp
    End If
    `Clear the list box
    lstResults.Clear
    `Populate the list box with names & phone numbers
    If mrecSelect.RecordCount > 0 Then
        mrecSelect.MoveFirst
        Do Until mrecSelect.EOF
            lstResults.AddItem mrecSelect![Name] & "  (Phone: " _
                & mrecSelect![Telephone] & ")"
            mrecSelect.MoveNext
        Loop
    End If
End Sub
5. Add the following code to the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

When the application is started, Form1 loads. The Form_Load event creates a Database object instance and then uses object's CreateQueryDef method to create a temporary QueryDef object. At this point, your parameter query is created by placing the SQL statement for the query into the SQL property of the newly created QueryDef object.

When the cmdSearch_Click event is triggered, you first populate the QueryDef's Parameter object with information from the txtParameter field. Then the routine checks whether the Recordset object it's about to populate is set to Nothing. If so, the query hasn't been run yet, so the routine constructs a Recordset object by running the OpenRecordset method from the QueryDef object. If not, it uses the Requery method, which simply re-executes the query without having to make a new connection to the database, compile the SQL, and so on.

After it does so, if the query has returned records, the lstResults list box is populated with the information.

Comments

One of the benefits of using a parameter query is the Requery method. The Requery method allows you to re-issue a query with different parameters; Microsoft Access will actually reuse the existing connection, running the query faster. Also, the optimization engine built into Jet works best on static SQL (that is, SQL stored in the database, as opposed to the SQL statements stored in code), so you can get even more benefit from the use of a parameter query that is saved to a Microsoft Access database. For more information on how to use a stored parameter query, examine How-To 5.7 in Chapter 5, "Microsoft Access Database."

3.6 How do I...

Create recordsets by joining fields from multiple tables?

Problem

I've designed my database using good relational database design principles, which means that I have data in multiple tables that are related through key fields. How can I use SQL to return recordsets with data from multiple tables in each recordset record?

Technique

In BIBLIO.MDB, the Publishers table contains information about publishers, and the Titles table contains information about titles. Each publisher is assigned a unique publisher ID, which appears in the PubID field in the Publishers table. In the Titles table, the publisher is indicated by the publisher number as well as in a field named PubID. Figure 3.7 shows this relationship. If you were using procedural coding and wanted to find the name of the publisher of a given title, you would find the title in the Titles table, store the value of the PubID for that title, and then find the matching PubID in the Publishers table.

This job is a lot easier with SQL. When you have a link like the one in Figure 3.7, you can use the keywords INNER JOIN in the FROM clause of a SQL SELECT statement to create a single recordset with fields from both tables. To continue the example, you could create a recordset with the Title field from the Titles table and the Name field from the Publishers table with this SQL statement:

SELECT Titles.Title, Publishers.Name
FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID

In a single-table SELECT statement, the FROM clause is simple--it just gives the name of the table. A multitable statement FROM clause consists of one (or more) subclauses, each based on an INNER JOIN. The syntax of each INNER JOIN is as follows:

<table 1 name> INNER JOIN <table 2 name>
ON <table 1 linking field name> = <table 2 linking field name>

Figure 3.7. The BIBLIO.MDB Publishers and Titles table relationship.

Note that the field names in both the SELECT clause and the FROM clause are fully qualified with their table names, with the period operator separating the table name from the field name. Strictly speaking, this is necessary only when both tables have fields with identical names. In the example, the table names are required in the FROM clause because both tables have a field named PubID; they are optional in the SELECT clause because only the Titles table has a field named Title and only the Publishers table has a field named Name. It's good practice, however, to fully qualify all field names in multitable SQL statements. This not only makes the code easier to interpret but also makes it less likely that your code will be broken by subsequent changes to the structure of the database.


MULTIFIELD JOINS

It's quite common to have relationships based on multiple fields within each table. For example, assume that you have an Employees table and an Hours Worked table. You identify each employee by two fields, [Last Name] and [First Name]. These two fields appear in each table and are used to link Employee records to Hours Worked records.
Code these multifield joins by creating an INNER JOIN subclause, with multiple ON expressions tied together with the AND keyword. Each ON expression links one pair of common fields. The FROM clause you'd use to link the tables in the example would be this:


FROM Employees INNER JOIN [Hours Worked]
ON Employees.[Last Name] = [Hours Worked].[Last Name]
AND Employees.[First Name] = [Hours Worked].[First Name]

Steps

Open the project SELECT6.VBP. The form shown in Figure 3.8 appears. Use the Data control's record navigation buttons to page through the records in the recordset.

Figure 3.8. The SQL Inner Join form on startup.

1. Create a new project called SELECT6.VBP. Use Form1 to create the objects and properties listed in Table 3.9.

Table 3.9. Options and properties for the INNER JOINer form.

OBJECT PROPERTY SETTING
Form Name Form1
Caption Chapter 3.6 Example
TextBox Name txtYearPublished
DataField Year Published
DataSource dtaData
TextBox Name txtPublisher
DataField Name
DataSource dtaData
TextBox Name txtTitle
DataField Title
DataSource dtaData
CommandButton Name cmdClose
Caption &Close
Data Name dtaData
Caption dtaData
RecordSource SELECT DISTINCTROW Titles.Title, Publishers.Name, Titles.[Year Published] FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID ORDER BY Titles.Title
Label Name lblYearPublished
AutoSize True
Caption Year Published:
Label Name lblPublisher
AutoSize True
Caption Publisher:
Label Name lblTitle
AutoSize True
CAPTION TITLE:

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
3. Add the following code as the Load event of Form1:

Private Sub Form_Load()
    `Set the DatabaseName for the Data control.
    dtaData.DatabaseName = BIBLIO_PATH
End Sub
4. Add the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

This program makes use of the innate capabilities of bound controls (discussed in more detail in Chapter 1, "Accessing a Database with Bound Controls") to illustrate the use of an INNER JOIN. When the program is started, the Form_Load event will set the DatabaseName property of the dtaData Data control to the location of BIBLIO.MDB. At this point, the dtaData Data control will run the SQL statement stored in its RecordSource property. The Data control will then handle the rest.

Comments

The INNER JOIN can be extremely useful in databases, but don't go "whole hog" with it. The more tables you add to a JOIN, no matter what type, the slower the SQL statement will execute. Consider this a database developer's maxim: Retrieve only the data you need; if you don't need it, don't include it. That goes for JOINs as well. If you don't need the table, don't JOIN it.

3.7 How do I...

Find records in a table without corresponding entries in a related table?

Problem

I have an Orders table and a Customers table, related on the Customer Number field. I'd like to find all the customers who have not placed an order in the past six months. How can I do this?

Technique

The INNER JOIN, discussed in the preceding How-To, allows you to find all the records in a table that have matching records in another table, when the two tables are related on a key field and when "matching" means that values in the key fields match. SQL also provides an outer join, which lets you list all the records in one of the related tables whether or not they have matching records in the other table.

For example, assume that you have two tables, Customers and Invoices, with these entries:

Customers Table                     Invoices Table
 Customer Number  Customer Name Customer Number  Invoice Date Invoice Amount
        100       ABC Company         102        12/12/1996   $589.31
        101       MNO Company         100        12/15/1996   $134.76
        102       XYZ Company         102        12/22/1996   $792.13

Create a recordset using the following SQL statement with an INNER JOIN in the FROM clause:

SELECT Customers.[Customer Name], Customers.[Customer Number], 
    Invoices.[Customer Number], Invoices.[Invoice Date], 
    Invoices.[Invoice Amount]
FROM Customers INNER JOIN Invoices
ON Customers.[Customer Number] = Invoices.[Customer Number]
ORDER BY Customers.[Customer Number], Invoices.[Invoice Date]

Executing that statement returns this recordset:

           Customers                Invoices
Customer Name  Customer Number  Customer Number  Invoice Date Invoice Amount
ABC Company          100              100        12/15/1996   $134.76
XYZ Company          102              102        12/12/1996   $589.31
XYZ Company          102              102        12/22/1996   $792.13

MNO Company, customer number 101, would not appear at all in the recordset because there are no records in the Invoices table for customer number 101, and an INNER JOIN returns only records with matching key field values in both tables. But see what happens when you change the join type in the FROM to a LEFT JOIN, one of the two types of outer joins:

SELECT Customers.[Customer Name], Customers.[Customer Number], 
    Invoices.[Customer Number], Invoices.[Invoice Date], 
    Invoices.[Invoice Amount]
FROM Customers LEFT JOIN Invoices
ON Customers.[Customer Number] = Invoices.[Customer Number]
ORDER BY Customers.[Customer Number], Invoices.[Invoice Date]

Executing this SQL statement returns this recordset:

            Customers              Invoices
Customer Name  Customer Number Customer Number  Invoice Date  Invoice Amount
ABC Company          100             100        12/15/1996    $134.76
MNO Company          101
XYZ Company          102             102        12/12/1996    $589.31
XYZ Company          102             102        12/22/1996    $792.13

The recordset consists of all the records that the INNER JOIN version produced, and one additional record for each record in the table on the left side of the FROM clause that has no matching records in the table on the right side of the FROM clause.


LEFT JOINS AND RIGHT JOINS

There are two outer joins: LEFT JOIN and RIGHT JOIN. The direction of the join refers to the relative position of the table names in the FROM clause of the SQL statement. A LEFT JOIN returns a record from the table on the left side of the FROM clause, whether or not a matching record exists on the right side. A RIGHT JOIN returns a record from the table on the right side of the FROM clause, whether or not a matching record exists on the left side. These two FROM clauses, therefore, have identical results:


FROM Customers LEFT JOIN Invoices
ON Customers.[Customer Number] = Invoices.[Customer Number]
FROM Invoices RIGHT JOIN Customers
ON Invoices.[Customer Number] = Customers.[Customer Number]

The "missing" fields on the right side of the recordset all have the value NULL. You can use this fact to modify the SQL statement to select only the records from the left table that do not have matching records in the right table:

SELECT Customers.[Customer Name], Customers.[Customer Number], 
    Invoices.[Customer Number], Invoices.[Invoice Date], 
    Invoices.[Invoice Amount]
FROM Customers LEFT JOIN Invoices
ON Customers.[Customer Number] = Invoices.[Customer Number]
WHERE Invoice.[Customer Number] IS NULL
ORDER BY Customers.[Customer Number], Invoices.[Invoice Date]

This statement returns the following recordset:

             Customers              Invoices
Customer Name  Customer Number  Customer Number  Invoice Date Invoice Amount
MNO Company          101

The field used in the WHERE clause can be any field from the right-side table because all right-side fields will be NULL when there is no record to match a left-side table record.

Steps

Open the project SELECT7.VBP. The form shown in Figure 3.9 appears. The list shows all the publishers in the Publishers table that do not have entries in the Publisher Comments table.

Figure 3.9. The SQL Left Join form on startup.

1. Create a new project called SELECT7.VBP. Use Form1 to create the objects and properties listed in Table 3.10.

Table 3.10. Options and properties for the Outer JOINer form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.7 Example
Data Name dtaData
Caption dtaData
RecordSource SELECT Publishers.[Company Name] FROM Publishers LEFT JOIN [Publisher Comments] ON Publishers.PubID = [Publisher Comments].PubID WHERE [Publisher Comments].PubID IS NULL ORDER BY [Company Name]
Visible False
DBList Name dlstAuthors
RowSource dtaData
ListField Author
CommandButton Name cmdClose
Caption &Close
Label Name lblAuthors
Caption Authors without Title records:

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


3. Add the following code to the Load event of Form1:

Private Sub Form_Load()
    `Set the DatabaseName of the Data control.
    dtaData.DatabaseName = BIBLIO_PATH
End Sub


4. Add the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End

End Sub

How It Works

The dtaData recordset is built by the following SQL statement:

SELECT Publishers.[Company Name]
FROM Publishers LEFT JOIN [Publisher Comments]
ON Publishers.PubID = [Publisher Comments].PubID
WHERE [Publisher Comments].PubID IS NULL
ORDER BY [Company Name]

The WHERE clause of that SQL statement creates a LEFT JOIN between the left-side table (Publishers) and the right-side table (Publisher Comments). Ignoring the WHERE clause for a moment, this LEFT JOIN would create a snapshot with one record for each record in the Publisher Comments table, plus one record for every record in the Publishers table that does not have a matching record in Publisher Comments.

The WHERE clause eliminates from the snapshot all records in which there is a Publisher Comment, because the [Publisher Comments].PubID field will not be NULL where there is a record in Publisher Comments. For snapshot records created by records in Publishers without matching records in Publisher Comments, [Publisher Comments].PubID is NULL; the WHERE clause causes these records to be included in the output snapshot.

Comments

Like the INNER JOIN, explained in the preceding How-To, this can be a powerful tool if used well. You should experiment with the behavior of all sorts of joins--you might be surprised at what you get. Use this How-To's code as a basic example, and go from there, trying out different SQL joins to get a feel for what to expect with other SQL queries involving JOIN statements.

3.8 How do I...

Retrieve information such as counts, averages, and sums and display it by binding it to a Data control?

Problem

I'd like to extract descriptive statistics about the data in a table (for example, averages and sums or numeric fields, minimum and maximum values, and counts of records that meet certain criteria). How can I use SQL to accomplish this task?

Technique

SQL includes a rich set of aggregate functions--functions you can embed in SQL statements to return descriptive statistics about the data in your database. Table 3.11 lists the aggregate functions available and shows what each function returns. Note that all functions ignore NULL values in the recordset.

Table 3.11. SQL aggregate functions.

AGGREGATE FUNCTION Returns
Sum Sum of the values in a designated numeric field
Avg Average of the non-NULL values in a designated numeric field
Count Count of non-NULL values in one or more designated fields
Min Minimum value in a designated numeric or text field
Max Maximum value in a designated numeric or text field
First Value of a designated field in the first record in the recordset
Last Value of a designated field in the last record in the recordset
StDev Sample standard deviation of the non-NULL values in a designated field
StDevP Population standard deviation of the non-NULL values in a designated field
Var Sample variance of the non-NULL values in a designated field
VarP Population variance of the non-NULL values in a designated field

The syntax for using these functions in the SELECT clause of a SQL statement is the same for all functions:

<functionname>(<fieldname>) AS <outputfieldname>

The <fieldname> is the name of the field in the table whose records you are examining. The <outputfieldname> is the name you give to the result column in the recordset created by the SQL statement. The two field names must be different, and the <outputfieldname> cannot duplicate the name of a field in any table referenced in the SQL statement.

For example, assume that you want to get a total of the Invoice Amount field for all Invoice Table records with Invoice Dates between January 1, 1996, and January 31, 1996. Your SQL statement could be this:

SELECT SUM([Invoice Amount]) AS SumOfInvoices
FROM Invoices
WHERE [Invoice Date] BETWEEN #1/1/1996# AND #1/31/1996#

That statement would return a recordset consisting of one record with one field, with a field name of SumOfInvoices. The field's value would be the total of all the invoices between the specified dates.

You can include more than one aggregate function in a SQL statement. The following statement would return a single record with two fields, SumOfInvoices and AverageInvoice. SumOfInvoices would be the sum of all invoices for the designated customer. AverageInvoice would be the average invoice amount for that customer (disregarding any fields for which the Invoice Amount is NULL).

SELECT SUM([Invoice Amount]) AS SumOfInvoices, AVG([Invoice Number])
        as AverageInvoice
FROM Invoices
WHERE [Customer Number] = 12345

Steps

Open the project SELECT8.VBP. The form shown in Figure 3.10 appears. The labels on the form show several statistics about the authors in the Authors table of BIBLIO.MDB.

Figure 3.10. The SQL Aggregate form.

1. Create a new project called SELECT8.VBP. Use Form1 to create the objects and properties listed in Table 3.12. Save the form as SELECT8.FRM.

Table 3.12. Options and properties for the Author Statistics form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.8 Example
Data Name dtaData
Caption dtaData
RecordSource SELECT Count(*) AS CountOfAuthor, Avg([Year Born]) AS [AvgOfYear Born], Min([Year Born]) AS [MinOfYear Born], Max([Year Born]) AS [MaxOfYear Born] FROM Authors
Visible False
RecordsetType Snapshot
CommandButton Name cmdClose
Cancel True
Caption &Close
Default True
Label Name dlblCount
BorderStyle Fixed Single
DataField CountOfAuthor
DataSource dtaData
Label Name dlblMin
BorderStyle Fixed Single
DataField MinOfYear Born
DataSource dtaData
Label Name dlblMax
BorderStyle Fixed Single
DataField MaxOfYear Born
DataSource dtaData
Label Name dlblAvg
BorderStyle Fixed Single
DataField AvgOfYear Born
DataSource dtaData
Label Name lblCount
AutoSize True
Caption Number of authors:
Label Name lblMin
AutoSize True
Caption Earliest year born:
Label Name lblMax
AutoSize True
Caption Latest year born:
Label Name lblAvg
AutoSize True

CAPTION AVERAGE YEAR BORN:

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


3. Add the following code to the Load event of Form1:

Private Sub Form_Load()
    `Set the DatabaseName of the Data control.
    dtaData.DatabaseName = BIBLIO_PATH
End Sub
4. Add the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

The Data control creates a one-record recordset with this SQL statement:

SELECT Count(*) AS CountOfAuthor, Avg([Year Born]) AS [AvgOfYear Born],
Min([Year Born]) AS [MinOfYear Born], Max([Year Born]) AS [MaxOfYear Born]
FROM Authors

The single record contains four fields, each reporting one statistic about the records in the Authors table. The four bound labels on the form are each bound to one of the recordset fields.

Comments

The SQL statement used in the example for this How-To included this expression in its SELECT clause:

Count(*) as CountOfAuthor

Using the wildcard character * as the argument to the Count aggregate function indicates that you want the count of all the records in the table. You can achieve the same thing by using a field that you know to be non-NULL in every record (for example, the primary key of the table, which must by definition be non-NULL):

Count ([AuthorID]) as CountOfAuthor

However, it's better to use the wildcard character *, because the Jet database engine is optimized to perform Count queries with the wildcard. You get the same answer either way, but you get it faster with the wildcard.

3.9 How do I...

Create a recordset consisting of records with duplicate values?

Problem

I need to create a recordset that shows records with duplicate values. How can I do this with SQL?

TECHNIQUE
Three features of SQL--the GROUP BY clause, the HAVING clause, and SQL IN subqueries--facilitate the identification of duplicate values in a table.

The GROUP BY Clause

SQL provides the GROUP BY clause, which combines records with identical values into a single record. If you include a SQL aggregate function (such as COUNT) in the SELECT statement, the GROUP BY clause applies that function to each group of records to create a summary value.

For example, to return a recordset with one record for each unique state/city pair from the Publishers table in BIBLIO.MDB, you can use this SQL statement:

SELECT State, City, COUNT(*) AS CountByCityAndState FROM Publishers
GROUP BY State, City

The HAVING Clause

The HAVING clause is similar to the WHERE clause, but you use HAVING with GROUP BY. The argument to HAVING specifies which grouped records created by GROUP BY should be included in the output recordset. For example, this SQL statement returns one record for each unique state/city pair from the Publishers table in BIBLIO.MDB, restricting the records to those in which the state is CA:

SELECT [State],[City] FROM [Publishers]
GROUP BY [State],[City]
HAVING [State] = `CA'

You can use HAVING with the aggregate COUNT function (see the preceding How-To for information on aggregate functions) to restrict the output recordset to records in which the values grouped by the GROUP BY clause have a specified range of occurrences. This example selects only city/state pairs that occur more than once in the table:

SELECT [State],[City] FROM [Publishers]
GROUP BY [State],[City]
HAVING COUNT(*) > 1

The SELECT and GROUP BY clauses in this example create a recordset to which the HAVING clause applies the COUNT aggregate function. The HAVING COUNT(*) > 1 clause eliminates from the final output recordset record groups that occur only once.

You can use multiple criteria with the HAVING clause. This example selects only city/state pairs that occur more than once in the table where the state is CA:

SELECT [State],[City] FROM [Publishers]
GROUP BY [State],[City]
HAVING COUNT(*) > 1 AND [State] = `CA'

SQL IN Subqueries

An IN subquery is a SELECT statement nested inside the WHERE clause of another SELECT statement. The subquery returns a set of records, each consisting of a single field. The WHERE clause then compares a field from the "main" SELECT statement to the field returned by the subquery. The resultant recordset consists of those records from the main recordset where the main field equals the subquery field.

Consider this simple SELECT query:

SELECT [City], [Company Name] FROM [Publishers]
ORDER BY [City]

This query creates a recordset consisting of one record for every record in the [Publishers] table, sorted by [City]. Now add a WHERE clause containing an IN subquery:

SELECT [City], [Company Name] FROM [Publishers]
WHERE [City] IN
        (SELECT [City] FROM [Publishers]
         GROUP BY [City]
         HAVING COUNT(*) > 1)
ORDER BY [City]

The subquery in the example is parenthesized and indented. (The parentheses are required; the indenting is not.) The subquery returns one record for every [City] value that occurs more than one time in the [Publishers] table. If a [City] value occurs only once, it is not included in the subquery output.

The WHERE clause of the main query compares the [City] field of every record in the table to the set of [City] values returned by the subquery. If there is a match, the record is included in the main query's output recordset. If there is no match, the record is excluded from the output recordset. Because the subquery [City] values include only those occurring more than once in the table, the WHERE clause includes in the output recordset only those records with a [City] value that occurs more than once.

If you need a recordset based on a single duplicated field, the last illustration is sufficient. If you need to compare multiple fields to find duplicate values, additional steps are required. For example, your [Publishers] table contains records in which the [City] field is duplicated but in which the [State] field differs, as in the following table:

CITY                  STATE
Springfield           IL
Springfield           MA
Springfield           OH

(The BIBLIO.MDB database supplied with Visual Basic does not have any records in which this condition exists, but a real-life example might.)

Finding the true duplicates here requires additions to the subquery. The additions to the original subquery are shown here in bold:

(SELECT [City] FROM [Publishers] AS Tmp
GROUP BY [City], [State]
HAVING COUNT(*) > 1 AND [State] = Publishers.[State])

The addition of the State field to the GROUP BY clause creates a record for every unique combination of City and State; the three Springfields will now each appear in the recordset returned by the GROUP BY. The additional criterion, State = Publishers.State, in the HAVING clause compares the State field in each GROUP BY record output to the State field in the original Publishers table and selects only those in which the fields are equal; note that the table name on the right side of the equal sign is mandatory. Because of the additional criterion in the HAVING clause, it is necessary to assign the output of the subquery to a temporary variable--arbitrarily called Tmp--but any legal name that does not duplicate an existing field name will do.

You can use up to 10 criteria in a subquery. For additional criteria, simply append them to the GROUP BY clause with a comma and to the HAVING clause with the AND keyword.

Steps

Open and run the project SELECT9.VBP. The form shown in Figure 3.11 appears. The grid control on the form shows records from the Publishers table for which the city and state appear more than once in the table.

Figure 3.11. The duplicate SELECTer form on startup.

1. Create a new project called SELECT9.VBP. Use Form1 to create the objects and properties listed in Table 3.13, and save the form as SELECT9.FRM.

Table 3.13. Objects and properties for the Duplicate SELECTer form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.9 Example
Label Name lblCount
Alignment 2 - Center
BorderStyle 1 - Fixed Single
Label Name lblDupValues
Caption Duplicated values:
Grid Name grdValues
Cols 3
FixedCols 0
Scrollbars 2 - Vertical
CommandButton Name cmdClose
Cancel True
Caption &Close

2. Add the following code to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


3. Enter the following code as the Load event for Form1. For the Form_Load event, the routine first creates a Database object. Then it starts creating the SQL statement from the inside out by first creating the subquery in the strSubQuery string and then "wrapping" the rest of the query around it inside the strSQL string. After execution, if records are present, the grdValues grid is configured and populated with the contents of the [State], [City], and [Company Name] fields.

Private Sub Form_Load()
    Dim dbfBiblio As Database, recSelect As Recordset
    Dim strSQL As String, strSubQuery As String
    Dim intCount As Integer, intGridRow As Integer
   ` Get the database name and open the database.
    Set dbfBiblio = DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    ` Build the subquery, starting with its SELECT statement.
    strSubQuery = "SELECT City FROM Publishers AS Tmp " & _
        "GROUP BY City, State " & _
        "HAVING COUNT(*) > 1 AND State = Publishers.State "
    ` Build the SQL statement
    ` Start by designating the fields to be included in the 
    ` recordset and the WHERE IN clause
    strSQL = "SELECT City, State, [Company Name]" & _ 
        "FROM Publishers " 
        "WHERE City IN (" & strSubQuery & ") " & _
        "ORDER BY State, City"
    ` Run the query.
    Set recSelect = dbfBiblio.OpenRecordset(strSQL, _
        dbOpenSnapshot)
    ` Make sure the query returned at least one record
    If recSelect.RecordCount > 0 Then
        ` Get a count of records in the recordset and display it 
        ` on the form.
        recSelect.MoveLast
        intCount = recSelect.RecordCount
        lblCount.Caption = intCount
        ` Initialize the grid
        With grdValues
            .Rows = intCount + 1
            .ColWidth(0) = 700: .ColWidth(1) = 2000: 
                         .ColWidth(2) = 4000
            .Row = 0: .Col = 0: .Text = "State"
            .Col = 1: .Text = "City"
            .Col = 2: .Text = "Publisher"
        End With
        `Populate the grid
        recSelect.MoveFirst
        For intGridRow = 1 To intCount
            With grdValues
                .Row = intGridRow
                .Col = 0: .Text = recSelect![State]
                .Col = 1: .Text = recSelect![City]
                .Col = 2: .Text = recSelect![Company Name]
            End With
            recSelect.MoveNext
        Next intGridRow
    End If
End Sub


4. Enter the following code as the Click event of cmdClose. This event ends the application.

Private Sub cmdClose_Click()
    End
End Sub

How It Works

The Form_Load() event subroutine creates a SQL statement, first by building the subquery and then by creating the main query with the inserted subquery. (Refer to the "Technique" section for an explanation of the syntax of the SQL statement.) The subroutine then executes the query and creates a Recordset object. If the recordset contains at least one record, the subroutine initializes the grid control and inserts each record into the grid.

Comments

If you insert the word NOT in front of the word IN, a SELECT statement containing an IN subquery returns a recordset consisting of records that do not meet the criteria of the subquery. Assume that you changed the query in the example by inserting the word NOT:

SELECT [City], [Company Name] FROM [Publishers]
WHERE [City] NOT IN
       (SELECT [City] FROM [Publishers] AS Tmp
        GROUP BY [City], [State]
        HAVING COUNT(*) > 1 AND [State] = Publishers.[State])
ORDER BY [City]

This query would produce a recordset consisting of records with a city/state combination that occur only once in the table.

complexity

3.10 How do I...

Use Visual Basic functions within a SQL statement?

Problem

I need to create a recordset with special formatting based on the contents of a field, but I can't find any standard SQL function to use for the formatting.

Technique

One of the benefits of using the Jet database engine and Data Access Objects is the capability of embedding Visual Basic for Applications (VBA) functions in Access SQL for various tasks that SQL by itself could not accomplish easily.

Steps

Open and run the project SELECT10.VBP. The form shown in Figure 3.12 appears. The grid control on the form shows records from the Publishers table, before and after the execution of the LCase() function to convert the case from uppercase to lowercase.

Figure 3.12. The Visual Basic code with SQL form, showing Publishers data.

1. Create a new project called SELECT10.VBP. Use Form1 to create the objects and properties listed in Table 3.14, and save the form as SELECT10.FRM.

Table 3.14. Objects and properties for the Duplicate SELECTer form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.10 Example
Label Name lblPublishers
Caption Publisher Names
MSFlexGrid Name grdValues
Cols 3
FixedCols 1
AllowUserResizing 1 - flexResizeColumns
Scrollbars 2 - Vertical
Width 5655
CommandButton Name cmdClose
Cancel True
Caption &Close

2. Add the following statements to the declarations section of Form1. Ensure that the BIBLIO_PATH constant is set to the location of BIBLIO.MDB on your workstation.

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


3. Enter the following code as the Load event for Form1. As with the preceding How-To, the event starts by creating a Database object, opening BIBLIO.MDB for use. Then a SQL statement is created, using the VBA function LCase to convert the string to lowercase, as denoted by the second parameter in the command. (For more information on the LCase command, search for LCase in the Visual Basic Help Index.) Note that the constant for the second parameter, vbProperCase, was not used here--some constants might not be accessible by the SQL precompiler used by the DAO library.

Private Sub Form_Load()
    Dim dbfBiblio As Database, recSelect As Recordset
    Dim strSQL As String
    Dim intCount As Integer, intGridRow As Integer
    Set dbfBiblio = DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    ` Build the query, starting with its SELECT statement.
    ` Note the LCase() function; a VBA function, NOT a SQL 
    ` function.
    strSQL = "SELECT Publishers.PubID, Publishers.Name, " & _
             "LCase([Publishers].[Name],3) AS CheckedName " & _
             "FROM Publishers;"
    ` Run the query to create the recordset.
    Set recSelect = _
        dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
    ` Make sure the query returned at least one record
    If recSelect.RecordCount > 0 Then
        `Get the record count & display it on the form
        recSelect.MoveLast
        intCount = recSelect.RecordCount
        lblPublishers.Caption = "Publisher Names (" & _
CStr(intCount) & "records)"
        `Initialize the grid
        With grdValues
            .Rows = intCount + 1
            .ColWidth(0) = 700: .ColWidth(1) = 2000: 
            .ColWidth(2) = 4000
            .Row = 0: .Col = 0: .Text = "Pub ID"
            .Col = 1: .Text = "Name"
            .Col = 2: .Text = "Name After LCase()"
        End With
        `Populate the grid
        recSelect.MoveFirst
        For intGridRow = 1 To intCount
            With grdValues
               .Row = intGridRow
               .Col = 0: .Text = recSelect![PubID]
               .Col = 1: .Text = recSelect![Name]
               .Col = 2: .Text = recSelect![CheckedName]
            End With
            recSelect.MoveNext
        Next intGridRow
    End If
End Sub


4. Enter the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

The Form_Load() event creates a SQL statement, showing both the raw data in the [Name] field and the same data after processing by the LCase() VBA function to "scrub" the raw data. The recordset data is placed directly into the grid with no further processing.

Comments

VBA functionality can expand DAO query power enormously, allowing for everything from math functions to string processing within the SQL. Rather than the tedious and time-consuming process of performing the same action by looping through a recordset and using the same VBA function to modify the data field by field, the ease of using a single SQL statement should strongly encourage you to experiment with VBA functions in Access SQL.

3.11 How do I...

Make bulk updates to database records?

Problem

I need to make an identical change to a number of records that meet certain criteria. How can I accomplish this task with a single SQL statement?

TECHNIQUE
In addition to SELECT queries, which create recordsets based on criteria you specify, SQL provides a group of action query statements. One type of action query is the UPDATE query, which makes specified changes to a set of records that meet designated criteria.

An UPDATE query contains the clauses shown in Table 3.15. The example shown in Table 3.15 increases the [Price Each] field by 3% for each record in the [Parts List] table that has a [Part Number] beginning with the string "XYZ7".

Table 3.15. The UPDATE statement.

CLAUSE Purpose Example
UPDATE Names the table UPDATE [Parts List]
SET Designates the fields to be updated and their new values SET [Price Each] = [Price Each] * 1.03
WHERE Specifies the records to be updated WHERE [Part Number] LIKE "XYZ7*"

You run a SQL action query statement using the Execute method of the Database object. Assuming that you have a Database object db, you would run the query shown in the table using this Visual Basic code (the entire statement would normally appear on one line):

db.Execute("UPDATE [Parts List] SET [Price Each] = " & _
    "[Price Each] * 1.03 WHERE [Part Number] LIKE `XYZ7'")

Steps

The BIBLIO.MDB file distributed with Visual Basic contains outdated information about four publishers. These publishers were formerly located on College Ave. in Carmel, IN. They have moved to an address in Indianapolis. The UPDATE.VBP project updates all four publishers' records to show their new address. It also provides the capability to restore the firms' original Carmel address.

Open the project UPDATE.VBP and run the project. Click the Update button and the form appears as shown in Figure 3.13. Click the Restore button, and the addresses change to show the Carmel address.

Figure 3.13. The SQL Update form after the update.

1. Create a new project called UPDATE.VBP. Use Form1 to create the objects and properties listed in Table 3.16, and save the form as UPDATE.FRM.

Table 3.16. Objects and properties for the UPDATEr form.

OBJECT Property Setting
Form Name Form1
Caption UPDATEr
CommandButton Name cmdClose
Cancel True
Caption &Close
CommandButton Name cmdRestore
Caption &Restore
CommandButton Name cmdUpdate
Caption &Update
ListBox Name lstData

2. Add the following statements to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private dbfBiblio As Database


3. Add the following code as the Click event of cmdUpdate.

Private Sub cmdUpdate_Click()
Dim strSQL As String
    On Error GoTo UpdateError
        `Build the UPDATE statement.
        strSQL = "UPDATE Publishers " & _
            "SET City = `Indianapolis', " & _
                 Address = `201 W. 103rd St.', " & _
            "Zip = `46290' " & _
            "WHERE ([City] = `Carmel') AND " & _
                   (Address LIKE `*11711*College*')"
        `Execute the update query.
        dbfBiblio.Execute strSQL
        ListRecords "Indianapolis"
    On Error GoTo 0
Exit Sub
UpdateError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


This procedure builds a SQL UPDATE statement that changes the contents of the [City], [Address], and [Zip] fields for each record that meets the criteria. The criteria are the city being equal to "Carmel" and the address containing the strings "11711" and "College". The LIKE clause in the address is necessary because BIBLIO.MDB, as supplied, uses a different form of the same address for each of the four publishers at 11711 N. College Ave.

The procedure executes the SQL statement, using the Execute method of the Database object. It then calls the ListRecords subroutine to display the records.

4. Add the following code as the Click event of cmdRestore. The cmdRestore routine reverses the action of cmdUpdate, using the same methodology.

Private Sub cmdRestore_Click()
Dim strSQL As String
    On Error GoTo RestoreError
        `Build the UPDATE statement.
        strSQL = "UPDATE Publishers " & _
            "SET City = `Carmel', " & _
                        "Address = `11711 N. College Ave.', " & _
            "Zip = `46032' " & _
            " WHERE ([City] = `Indianapolis') AND " & _
                    "(Address = `201 W. 103rd St.')"
        `Execute the update query.
        dbfBiblio.Execute strSQL
        ListRecords "Carmel"
    On Error GoTo 0
Exit Sub
RestoreError:
    MsgBox Error$, vbExclamation
Exit Sub
End Sub


5. Create the subroutine ListRecords by entering the following code. ListRecords builds a SQL SELECT statement that selects records, based on the city name passed as the parameter, and then lists these records in the list box on the form.

Private Sub ListRecords(cityName As String)
    Dim recSelect As Recordset
    Dim strSQL As String, strAddress As String
    On Error GoTo ListError
        ` Set the correct street address based on the city name.
        strAddress = IIf(strCity = "Indianapolis", _
                     "201 W. 103rd St.", _
                     "11711 N. College Ave.")
        ` Create the recordset for the list box.
        strSQL = "SELECT [Company Name], [Address], [City], " & _
            "[State], [Zip] " & _
            "FROM Publishers " & _
            "WHERE [City] = `" & strCity & "`" & _
                   "AND [Address] = `" & _
            strAddress & "`"
        `Construct the recordset.
        Set recSelect = _
                   dbfBiblio.OpenRecordset(strSQL, dbOpenSnapshot)
        `Clear the list box
        lstData.Clear
        `Show each record in the list box.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do
                lstData.AddItem 
                    Left$(recSelect![Company Name], 10) _
                    & ", " & recSelect![Address] & ", " & _
                             recSelect![City] _
                    & ", " & recSelect![State] & " " & _
                             recSelect![Zip]
                recSelect.MoveNext
            Loop While Not recSelect.EOF
        End If
    On Error GoTo 0
Exit Sub
ListError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


6. Add the following code as the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

Until the user clicks either the Update or the Restore button, the form just lies in wait. If the user clicks the Update button, the form executes a SQL statement, modifying any record the statement finds with specific [City] and [Address] field contents, changing the [City], [Address], and [Zip] fields. The Restore performs exactly the same action but reverses the actions taken by the Update button, searching for the newly altered records and restoring them to their previous values.

Comments

This method is usually the best way to make bulk updates to records in any database; it gets the database to do the work rather than the calling application, usually in a more efficient fashion. But, as with any powerful tool, this method can be misused. Ensure that your WHERE clause incorporates only those records to be changed. Have too "loose" a selection, and records might be mistakenly altered; this might happen much too quickly for the error to be stopped. Be cautious, and your caution will serve you well.

3.12 How do I...

Create and delete tables?

Problem

I need to create a temporary table, use it for a while, and then get rid of it. How can I accomplish this using SQL?

Technique

SQL provides two statements that allow you to create and delete tables. CREATE TABLE creates a new table, using a name and field list that you specify. DROP TABLE deletes a named table.

To create a table with CREATE TABLE, you need to pass it two arguments: the name of the table to be created and a field list, with the field list enclosed in parentheses. The field list consists of a set of field descriptions separated by commas. Each field description has two parts: a field name and a data type. The field name and data type are separated by a space.

Execute the CREATE TABLE statement by passing it as the parameter of the Execute method of the Database object. The following Visual Basic statement creates a new table in the database represented by the Database object variable dbfTest. The new table is named My Parts and has two fields: [Part Name], which is a text field, and [Quantity], which is a long integer.

dbfTest.Execute("CREATE TABLE [My Parts] ([Part Name] TEXT, " & _
      "[Quantity] LONG)")

As with any table or field name, the brackets are required if the name contains a space, and they are optional if there is no space. The convention is to capitalize the data type, but this capitalization is not required.

The data type names used by the Jet database engine do not exactly match the names required by SQL. Table 3.17 shows the SQL data types and the corresponding Jet data type for each.

Table 3.17. SQL data types used by CREATE TABLE and their corresponding Jet database engine data types.

SQL DATA TYPE Equivalent Jet Data Type
BINARY N/A--for queries on attached tables that define a BINARY data type
BIT Yes/No
BYTE Numeric--Byte
COUNTER Counter
CURRENCY Currency
DATETIME Date/Time
SINGLE Numeric--Single
DOUBLE Numeric--Double
SHORT Numeric--Integer
LONG Numeric--Long
LONGTEXT Memo
LONGBINARY OLE objects
TEXT Text

The DROP TABLE requires just one argument--the name of the table to be removed from the database. Like CREATE TABLE, the DROP TABLE statement is executed through the Execute method of the Database object. The following Visual Basic statement deletes the table My Parts from the database represented by dbfTest:

dbfTest.Execute("DROP TABLE [My Parts]")

Steps

The NEWTABLE.VBP project lets you create tables in BIBLIO.MDB and assign the table's fields using any data type. Open the project NEWTABLE.VBP and run the project. The form shown in Figure 3.14 appears.

Figure 3.14. The SQL Create Table form at startup.

Click the List Tables button, and the form shown in Figure 3.15 appears. This form lists the tables currently in BIBLIO.MDB. Click Close to return to the Table Creator form.

In the Table Name text box, type any legal table name. In the Field Name text box, type a field name; then select a field type from the drop-down list. Click Add Field to create the field. Create several additional fields; for each field, type a field name, select a field type, and click Add Field. When you have several fields defined, the form will appear as shown in Figure 3.16. Click Create Table to add the table to BIBLIO.MDB. The table name and field names will disappear to prepare the Table Creator form to create another table. You can click List Tables to see your table BIBLIO.MDB.

After you've created several tables, click List Tables. Select one of the tables you created and click Delete. (The program will not let you delete a table with data in it, so you will not be able to delete any of the original BIBLIO.MDB tables.) The table disappears from the table list.

Figure 3.15. The Table List form, showing table names.

Figure 3.16. The SQL Create Table form, with new fields added.

1. Create a new project called NEWTABLE.VBP. Rename Form1 to frmMain, create the objects and properties listed in Table 3.18, and save the form as NEWTABLE.FRM.

Table 3.18. Objects and properties for the Table Creator form.

OBJECT Property Setting
Form Name frmMain
Caption Chapter 3.12 Example - Table Creator
CommandButton Name cmdListTables
Caption &List Tables
CommandButton Name cmdCreateTable
Caption Create &Table
CommandButton Name cmdRemoveField
Caption &Remove Field
CommandButton Name cmdAddField
Caption &Add Field
Default True
CommandButton Name cmdClose
Cancel True
Caption &Close
ComboBox Name cboFieldTypes
Style 2 - Dropdown List
ListBox Name lstFields
TextBox Name txtFieldName
TextBox Name txtTableName
Label Name lblTableName
Caption &Table Name:
Label Name lblFieldName
Caption &Field Name:
Label Name lblFieldType
Caption Field T&ype:
Label Name lblFieldList
Caption Field Li&st:

2. Insert a new form into the project. Rename it frmTableList, create the objects and properties listed in Table 3.19, and save the form as TABLIST.FRM.

Table 3.19. Objects and properties for the Current Tables form.

OBJECT Property Setting
Form Name frmTableList
BorderStyle 3 - Fixed Dialog
Caption Chapter 3.12 Example - Table List
MaxButton False
MinButton False
CommandButton Name cmdDelete
Caption &Delete
CommandButton Name cmdClose
Caption Close
ListBox Name lstTables
Sorted True

3. Add the following statements to the declarations section of frmMain:

Option Explicit
Private Const IllegalCharacters = "[].!'"
Private Const FIELDNAME = 1
Private Const TABLENAME = 2
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


4. Enter the following code for the Form_Load event for frmMain:

Private Sub Form_Load()
    `Fill the Field Type combo box.
    FillTypeList
End Sub


5. Create the FillTypeList subroutine in frmMain with the following code. This procedure fills the drop-down list with the available data types, using the Jet database engine names.

Sub FillTypeList()
`Fill the Field Type combo box with types of available fields
    With cboFieldTypes
        .AddItem "Counter"
        .AddItem "Currency"
        .AddItem "Date/Time"
        .AddItem "Memo"
        .AddItem "Number: Byte"
        .AddItem "Number: Integer"
        .AddItem "Number: Long"
        .AddItem "Number: Single"
        .AddItem "Number: Double"
        .AddItem "OLE Object"
        .AddItem "Text"
        .AddItem "Yes/No"
    End With
End Sub


6. Enter the following code as the Click event of frmMain's cmdListTables. This subroutine displays the List Tables form modally.

Private Sub cmdListTables_Click()
    ` Display the Table List form modally.
    frmTableList.Show vbModal
End Sub


7. Enter the following code as the Click event of frmMain's cmdAddField. The cmdAddField routine first calls the LegalName function to verify that the user has entered a legal field name and verifies that the user has selected a field type. It then translates the data type shown in the drop-down list from the Jet name to the SQL name. It formats the field name and data type and adds it to the field list. It then clears the field name and field type for entry of the next field.

Private Sub cmdAddField_Click()
Dim strFieldType As String
    `Check first if the Field Name text box contains a legal name
    If LegalName(FIELDNAME) Then
        `If it does, check if the Field Type has been selected.
        If cboFieldTypes.ListIndex > -1 Then
            `If both criteria are satisfied, store the SQL field 
            `type in the strFieldType string.
            Select Case cboFieldTypes.Text
                Case "Counter"
                    strFieldType = "COUNTER"
                Case "Currency"
                    strFieldType = "CURRENCY"
                Case "Date/Time"
                    strFieldType = "DATETIME"
                Case "Memo"
                    strFieldType = "LONGTEXT"
                Case "Number: Byte"
                    strFieldType = "BYTE"
                Case "Number: Integer"
                    strFieldType = "SHORT"
                Case "Number: Long"
                    strFieldType = "LONG"
                Case "Number: Single"
                    strFieldType = "SINGLE"
                Case "Number: Double"
                    strFieldType = "DOUBLE"
                Case "OLE Object"
                    strFieldType = "LONGBINARY"
                Case "Text"
                    strFieldType = "TEXT"
                Case "Yes/No"
                    strFieldType = "BIT"
            End Select
            `Add the new field to the Field List list box.
            lstFields.AddItem "[" & txtFieldName & "] " & _
                              strFieldType
            `Reset the Field Name and Field Type controls.
            txtFieldName = ""
            cboFieldTypes.ListIndex = -1
        Else
            MsgBox "You must select a field type.", vbExclamation
        End If
    End If
End Sub


8. Create the LegalName function in frmMain by entering the following code. The function performs a number of checks to verify that the name entered by the user as a table name or field name is acceptable to the Jet engine. For each check, it generates a user-defined error if the name fails the test. The error-handling code displays a message that explains to the user what the problem is and then returns False to the calling routine. If the name passes all the tests, the error-handling code is never called, and the function returns True.

Function LegalName(intNameType As Integer) As Boolean
    Dim i As Integer
    Dim strObjectName As String
    Dim dbfBiblio As Database, tdfNewTable As TableDef
    On Error GoTo IllegalName
        `Depending on the type of name being checked, store either 
        `the field or table name text box contents.
        If intNameType = FIELDNAME Then
            strObjectName = txtFieldName
        Else
            strObjectName = txtTableName
        End If
        `If blank, raise an error.
        If Len(strObjectName) = 0 Then Err.Raise 32767
        `If it has a leading space, raise an error.
        If Left$(strObjectName, 1) = " " Then Err.Raise 32766
        `If it contains any of the characters in the 
        `IllegalCharacters constant, raise an error
        For i = 1 To Len(IllegalCharacters)
            If InStr(strObjectName, Mid(IllegalCharacters, 
              i, 1)) > 0 Then Err.Raise 32765
        Next i
        `If it contains any ANSI character from Chr$(0) to 
        `Chr$(31), (you guessed it) raise an error.
        For i = 0 To 31
            If InStr(strObjectName, Chr(i)) > 0 _
               Then Err.Raise 32764
        Next i
        `Check if the field or table name already exists.  If so,
        `raise an error.
        If intNameType = FIELDNAME Then
            For i = 0 To lstFields.ListCount - 1
                If strObjectName = lstFields.List(i) _
                   Then Err.Raise 32763
            Next i
        ElseIf intNameType = TABLENAME Then
            Set dbfBiblio = 
                DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
            For Each tdfNewTable In dbfBiblio.TableDefs
                If tdfNewTable.Name = strObjectName _
                   Then Err.Raise 32762
            Next
        End If
        `If they've managed to get through all that validation, 
        `the function should be True, to indicate success.
        LegalName = True
    On Error GoTo 0
Exit Function
IllegalName:
    Dim strErrDesc As String, context As String
    `Note the use of an IIf statement to reduce code size.
    context = IIf(intNameType = FIELDNAME, "field name", _
              "table name")
    `Build an error message based on the user-defined error that 
    `occurred.
    Select Case Err.Number
        Case 32767
            strErrDesc = "You must enter a " & context & "."
        Case 32766
            strErrDesc = "The " & context & _
                         " cannot begin with a space."
        Case 32765
            strErrDesc = "The " & context & _
                " contains the illegal character " & _
                Mid(IllegalCharacters, i, 1) & "."
        Case 32764
            strErrDesc = "The " & context & _
                " contains the control character " & _
                "with the ANSI value" & Str$(i) & "."
        Case 32763
            strErrDesc = "The field name " & strObjectName & _
                " already exists in the field name list."
        Case 32762
            strErrDesc = "The table name " & strObjectName & _
                " already exists in the database " & _
                  BIBLIO_PATH & "."
        Case Else
            ` Visual Basic's default error message.
            strErrDesc = Err.Description
    End Select
    MsgBox strErrDesc, vbExclamation
    `The function indicates False, or failure.
    LegalName = False
Exit Function
End Function



9. Enter the following code as the Click event of frmMain's cmdRemoveField. This procedure deletes the field selected by the user.

Private Sub cmdRemoveField_Click()
    ` If the user has selected a field, remove it from the list.
    ` Otherwise, just ignore the click.
    If lstFields.ListIndex > -1 Then lstFields.RemoveItem _
       lstFields.ListIndex
End Sub


10. Enter the following code as the Click event of frmMain's cmdCreateTable. This procedure calls LegalName to verify that the table name is acceptable and verifies that the user has defined at least one field. It creates the field list for the SQL statement by reading through the data in lstFields and building a comma-delimited string from the entries in that list box. It then builds the SQL statement and uses the Execute method of the Database object to create the table.

Private Sub cmdCreateTable_Click()
    Dim strSQL As String, strFieldList As String
    Dim i As Integer
    Dim dbfBiblio As Database
    On Error GoTo CreateTableError
        Screen.MousePointer = vbHourglass
        If LegalName(TABLENAME) Then
            If lstFields.ListCount > 0 Then
                strFieldList = " (" & lstFields.List(0)
                For i = 1 To lstFields.ListCount - 1
                    strFieldList = strFieldList & ", " & _
                                   lstFields.List(i)
                Next i
                strFieldList = strFieldList & ") "
                strSQL = "CREATE TABLE [" & txtTableName & "]" _
                       & strFieldList
                Set dbfBiblio = DBEngine.Workspaces(0). _
                       OpenDatabase(BIBLIO_PATH)
                dbfBiblio.Execute (strSQL)
                Screen.MousePointer = vbDefault
                MsgBox "Table created successfully."
                txtTableName = ""
                lstFields.Clear
            Else
                Screen.MousePointer = vbDefault
                MsgBox "You must define at least one field.", _
                       vbExclamation
            End If
        End If
    On Error GoTo 0
Exit Sub
CreateTableError:
    Screen.MousePointer = vbDefault
    MsgBox Error$, vbExclamation
Exit Sub
End Sub


11. Enter the following code as the Click event of frmMain's cmdClose. Unlike most of the cmdClose_Click events in previous How-To's, this one has a bit more to it. If the user has entered a partial table definition (as determined by a table name or one or more created fields), a message box appears, asking the user whether to abandon the current creation, and it requires a Yes or No answer. If the user answers Yes, the program ends. If there is no partial table definition, the program ends without showing the message box.

Private Sub cmdClose_Click()
    Dim strErrDesc As String
    ` If the user has entered a partial table definition, make 
    ` sure that the user wants to abandon it. If so, end the 
    ` program.
    If txtTableName <> "" Or lstFields.ListCount > 0 Then
        strErrDesc = "Do you want to abandon operations on " & _
                     "the current table?"
        If MsgBox(strErrDesc, vbQuestion + vbYesNo + 
           vbDefaultButton2) = vbYes Then
            End
        End If
    Else
        ` No partial table definition, so just end the program
        End
    End If
End Sub


12. Switch to frmTableList. Enter the following code into the declarations section of frmTableList, modifying the path in the Const statement to point to your copy of BIBLIO.MDB.

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


13. Enter the following code as frmTableList's Form_Load event. This calls the ListTables subroutine, explained in the next step, to fill the lstTables list box with the database's tables.

Private Sub Form_Load()
    ` Fill the list box with the current non-system tables in 
    ` BIBLIO.MDB.
    ListTables
End Sub


14. Create the ListTables subroutine in frmTableList by entering the following code. ListTables is called when the form loads and when the user deletes a table. It uses the TableDefs collection of the Database object to build a list of tables in the BIBLIO.MDB database. The TableDefs collection contains one record for each table in the database, including the (normally hidden) system tables. Because the Name property of all system table TableDef objects begins with the string "MSys", this procedure assumes that any table starting with that string is a system table and ignores it. The names of all other tables get added to the lstTables list box.

Private Sub ListTables()
Dim dbfBiblio As Database, tdfTableList As TableDef
    On Error GoTo ListError
        Screen.MousePointer = vbHourglass
        `Clear the list box, then open the database.
        lstTables.Clear
        Set dbfBiblio = _
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        ` Cycle through the table definitions in BIBLIO_PATH.
        ` If the table is a system table (name begins with MSys), 
        ` ignore it. Otherwise, add it to the list.
        For Each tdfTableList In dbfBiblio.TableDefs
            If Left$(tdfTableList.Name, 4) <> "MSys" Then _
               lstTables.AddItem tdfTableList.Name
        Next
        Screen.MousePointer = vbDefault
    On Error GoTo 0
Exit Sub
ListError:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description, vbExclamation
    Unload frmTableList
Exit Sub
End Sub


15. Enter the following code as the Click event frmTableList's cmdDelete. DROP TABLE deletes a table whether or not the table contains data. Because you do not want to delete any tables with data, this procedure checks to make sure that the table is empty and then deletes it.

Private Sub cmdDelete_Click()
Dim dbfBiblio As Database
    On Error GoTo DeleteError
        Screen.MousePointer = vbHourglass
        `If a table is selected, then continue
        If lstTables.ListIndex > -1 Then
            `Confirm that the table has no records
            If TableIsEmpty() Then
                ` Delete the selected table from BIBLIO_PATH.
                Set dbfBiblio = DBEngine.Workspaces(0). _
                    OpenDatabase(BIBLIO_PATH)
                dbfBiblio.Execute ("DROP TABLE [" & _
                                  lstTables.Text & "]")
                ` Display the modified list of tables.
                ListTables
                Screen.MousePointer = vbDefault
            Else
                `The table has records, so inform the user.
                Screen.MousePointer = vbDefault
                MsgBox lstTables.Text & " is not empty.", _
                       vbExclamation
            End If
        Else
            `No table has been chosen, so inform the user.
            Screen.MousePointer = vbDefault
            MsgBox "You have not selected a table to delete.", _
                   vbExclamation
        End If
    On Error GoTo 0
Exit Sub

DeleteError:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description, vbExclamation
    Unload frmTableList
Exit Sub
End Sub


16. Create the TableIsEmpty function by entering the following code into frmTableList. This function returns True if the table currently selected in lstTables is empty.

Function TableIsEmpty() As Boolean
Dim dbfBiblio As Database, tdfTableList As TableDef
    On Error GoTo TableIsEmptyError
        Set dbfBiblio = 
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        ` Cycle through the table definitions in BIBLIO_PATH.
        ` When the table currently selected in lstTables is found, 
        ` check to see whether it has records. If it does not, 
        ` return True; otherwise, return False.
        For Each tdfTableList In dbfBiblio.TableDefs
            If tdfTableList.Name = lstTables.Text Then
                TableIsEmpty = IIf(tdfTableList.RecordCount = 0, _
                     True, False)
                Exit For
            End If
        Next
    On Error GoTo 0
Exit Function
TableIsEmptyError:
    MsgBox Err.Description, vbExclamation
    Unload frmTableList
Exit Function
End Function


17. Enter the following code as the Click event of frmTableList's cmdClose:

Private Sub cmdClose_Click()
    Unload frmTableList
End Sub

How It Works

The frmMain form essentially builds a CREATE TABLE SQL statement by using the table name listed in the lstTables control, with the fields listed in the lstFields list box. This might seem greatly simplified, but it guides all the reasoning behind the code added in this How-To.

The main action occurs in the cmdCreateTable_Click event of frmMain. Here, based on the choices the user made regarding the name of the table and the name and type of the fields to be added, the CREATE TABLE SQL statement is concatenated and executed. Clicking the cmdListTables button displays a list of existing tables in the Access database in case the user wants to rewrite an existing empty table. (The TableIsEmpty function is used to ensure that valu-able data is not overwritten; the program will destroy only an empty table.) The cboFieldTypes combo box allows the program to filter the various field types in a manner accessible to the user.

Comments

One of the key items to remember in this How-To is the destructive behavior of the CREATE TABLE statement, as mentioned in step 15. If a CREATE TABLE statement is issued defining a table with the same name as one that already exists in the Access database, it destroys the existing table. Although this behavior is not true across all database platforms, it's usually better to be safe than sorry and include a routine similar to the TableIsEmpty function in this How-To.

3.13 How do I...

Append and delete records?

Problem

I have a table to which I'd like to add records that are built from records in other tables. I'd also like to delete records based on criteria I specify. How can I accomplish these tasks with SQL?

Technique

SQL provides two statements, the INSERT INTO and DELETE statements, that append records to a table and delete records from a table, respectively.

THE INSERT INTO STATEMENT
SQL's INSERT INTO statement is used to append records to an existing table. The INSERT INTO statement has three clauses, shown in Table 3.20.

Table 3.20. The syntax of the INSERT INTO statement.

CLAUSE Purpose Example
INSERT INTO Names the table and fields into which data are to be inserted INSERT INTO [Publisher Titles] ([Name], [Title])
SELECT Names the fields from which data are to be taken SELECT Publishers.Name, Titles.Title
FROM Names the table or other source of the data FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID

The INSERT INTO clause takes two parameters, the table name ([Publisher Titles] in the example) and the field names into which data are to be inserted. The field names are enclosed in parentheses and delimited by commas.

The SELECT clause statement consists of a list of fields from which the data to be inserted into the fields named in the INSERT INTO clause will be drawn. There must be a one-to-one correspondence between the fields in the INSERT INTO clause and the fields in the SELECT clause. If you have more INSERT INTO fields than SELECT fields, or vice versa, an error will result. If the field names are from multiple tables--as in the example--and if the names are ambiguous (that is, both tables have fields with the same names), they must be qualified with the table names.

The FROM clause statement names the table or other source of the fields named in the SELECT clause. In the example, the FROM clause names not a single table but a pair of tables linked by an INNER JOIN. (See How-To 3.6 for details on INNER JOINs.)

As with other SQL action queries, you run the INSERT INTO clause by using it as the argument for the Execute method of the Database object. To execute the query shown in the table against a database object represented by the variable dbfTest, you would create the following Visual Basic statement (note the continuation character):

dbfTest.Execute("INSERT INTO [Publisher Titles] ([Name], " & _
    "[Title]) SELECT Publishers.Name, " & _
    "Titles.Title FROM Publishers INNER JOIN Titles ON " & _
    "Publishers.PubID = Titles.PubID")

The DELETE Statement

Use the SQL DELETE statement to delete records from a table, based on criteria you specify in the DELETE statement. The DELETE statement has the syntax shown in Table 3.21.

Table 3.21. The syntax of the DELETE statement.

CLAUSE Purpose Example
DELETE FROM Names the table from which records are to be deleted DELETE FROM [Publisher Titles]
WHERE Criteria that select records for deletion WHERE [Publication Date]

Execute the DELETE statement by passing it as the parameter to the Execute method statement of the Database object. If you have a Database object variable named dbfTest, this Visual Basic statement executes the SQL shown in the table:

dbfTest.Execute("DELETE FROM [Publisher Titles] WHERE [Publication Date] <= 1990")

Steps

Open and run the project UPDATE.VBP. Click the Create Table button and then the Append Records button. These two actions create a table named [Publisher Titles], fill it with records, and display the records in the form, as shown in Figure 3.17. Notice the titles from Addison-Wesley on your screen. (You might need to scroll down to see them.)

Figure 3.17. The SQL Insert Into form, showing appended records.

Click the Delete Records button. The Select Publisher form shown in Figure 3.18 appears. Select Addison-Wesley and click OK. The previous form reappears with the list refreshed to show the records currently in the [Publisher Titles] table. Notice that the Addison-Wesley titles are missing.

Figure 3.18. The Publisher List form, showing publisher names.

1. Create a new project called APPEND.VBP. Rename Form1 to frmMain, create the objects and properties listed in Table 3.22, and save the form as APPEND.FRM.

Table 3.22. Objects and properties for the Append and Delete form.

OBJECT Property Setting
Form Name frmMain
Caption Chapter 3.13 - Example
ListBox Name lstData
Sorted True
CommandButton Name cmdDeleteRecords
Caption &Delete Records
CommandButton Name cmdClose
Caption &Close
CommandButton Name cmdDropTable
Caption D&rop Table
CommandButton Name cmdAppendRecords
Caption &Append Records
CommandButton Name cmdCreateTable
Caption Create &Table

2. Insert a new form into the project. Rename it to frmSelectPublisher, create the objects and properties listed in Table 3.23, and save the form as PublisherSelect.FRM.

Table 3.23. Objects and properties for the Select Publisher form.

OBJECT Property Setting
Form Name frmSelectPublisher
BorderStyle 3 - Fixed Dialog
Caption Chapter 3.13 - Publisher List
MaxButton False
MinButton False
CommandButton Name cmdOK
Caption &OK
Default True
CommandButton Name cmdCancel
Caption &Cancel
CheckBox Name chkDeleteAll
Caption &Delete All
ListBox Name lstPublishers
Sorted True

3. Add the following statements to the declarations section of frmMain:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private strPublisherToDelete As String
Private dbfBiblio As Database


4. Enter the following code as in the Load event of frmMain. The Form_Load code checks to see whether the [Publisher Titles] table exists in the database and, if it exists, whether it has any records. It then enables and disables the appropriate command buttons.

Private Sub Form_Load()
Dim tdfTable As TableDef
    Dim blnTableFound As Boolean
    On Error GoTo LoadError
        blnTableFound = False
        `Open the database.
        Set dbfBiblio = 
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        `Iterate through the TableDefs collection.  If the table 
        `"Publisher Titles" is found, configure the 
        `form's buttons appropriately.
        For Each tdfTable In dbfBiblio.TableDefs
            If tdfTable.Name = "Publisher Titles" Then
                blnTableFound = True
                cmdDropTable.Enabled = True
                cmdCreateTable.Enabled = False
                If tdfTable.RecordCount > 0 Then
                    cmdDeleteRecords.Enabled = True
                    cmdAppendRecords.Enabled = False
                    FillList
                Else
                    cmdDeleteRecords.Enabled = False
                    cmdAppendRecords.Enabled = True
                End If
                Exit For
            End If
        Next
        `If the table is not found, configure the form's buttons
        `appropriately.
        If blnTableFound = False Then
            cmdDropTable.Enabled = False
            cmdCreateTable.Enabled = True
            cmdAppendRecords.Enabled = False
            cmdDeleteRecords.Enabled = False
        End If
    On Error GoTo 0
Exit Sub
LoadError:
    MsgBox Err.Description, vbExclamation
    Unload Me
Exit Sub
End Sub


5. Create the FillList subroutine in frmMain by entering the following code. The FillList routine fills the list box lstData with the records from the [Publisher Titles] table.

Sub FillList()
Dim recSelect As Recordset
    Dim strSQL As String
    On Error GoTo FillListError
        `Clear the list box.
        lstData.Clear
        `Get all the records from the Publisher Titles table.
        Set recSelect = dbfBiblio.OpenRecordset( _
           "SELECT * FROM " & _
           "[Publisher Titles]", _
            dbOpenSnapshot)
        `Put the records into the list box.
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstData.AddItem recSelect![Name] & ": " & _
                                recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
FillListError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


6. Enter the following code as the Click event for frmMain's cmdCreateTable. This code creates the [Publisher Titles] table. Refer to the preceding How-To for information on the CREATE TABLE statement.

Private Sub cmdCreateTable_Click()
    Dim strSQL As String

    On Error GoTo CreateTableError
        `Build the CREATE TABLE statement.
        strSQL = "CREATE TABLE [Publisher Titles] " & _
            "([Name] TEXT, [Title] TEXT)"
        `Execute the statement.  Since it's an action query,
        `you don't use the OpenRecordset command.  It would
        `fail, since an action query does not return a recordset.
        dbfBiblio.Execute (strSQL)
        `Configure the form's buttons appropriately.
        cmdCreateTable.Enabled = False
        cmdDropTable.Enabled = True
        cmdAppendRecords.Enabled = True
    On Error GoTo 0
Exit Sub
CreateTableError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


7. Enter the following code as the Click event for frmMain's cmdDropTable. This code deletes the [Publisher Titles] table. Refer to the preceding How-To for information on the DROP TABLE statement.

Private Sub cmdDropTable_Click()
Dim dbName As String
    On Error GoTo DropTableError
        `Build & execute the DROP TABLE statement.
        dbfBiblio.Execute ("DROP TABLE [Publisher Titles]")
        `Configure the form's buttons appropriately.
        cmdDropTable.Enabled = False
        cmdCreateTable.Enabled = True
        cmdAppendRecords.Enabled = False
        cmdDeleteRecords.Enabled = False
        `Clear the list box.
        lstData.Clear
    On Error GoTo 0
Exit Sub
DropTableError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


8. Enter the following code as the Click event for frmMain's cmdAppendRecords. This command builds the SQL statement that will append the records to the database and then executes the statement. The SQL statement is identical to that shown in Table 3.20.

Private Sub cmdAppendRecords_Click()
    Dim strSQL As String
    On Error GoTo AppendRecordsError
        Screen.MousePointer = vbHourglass
        `Build the INSERT INTO statement
        strSQL = _
            "INSERT INTO [Publisher Titles] ( [Name], Title ) " & _
            "SELECT Publishers.Name, Titles.Title " & _
            "FROM Publishers INNER JOIN Titles " & _
            "ON Publishers.PubID = Titles.PubID"
        `Execute the statement.
        dbfBiblio.Execute (strSQL)
        `Fill the list box via the FillList subroutine.
        FillList
        `Configure the form's buttons appropriately.
        cmdDeleteRecords.Enabled = True
        cmdAppendRecords.Enabled = False
        Screen.MousePointer = vbDefault
    On Error GoTo 0
Exit Sub
AppendRecordsError:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub



9. Enter the following code as the Click event for frmMain's cmdDeleteRecords. This procedure deletes the designated records from the database. It calls the GetPublisher function of frmSelectPublisher, returning a value to be placed in strPublisherToDelete. Then it examines the public variable strPublisherToDelete; if strPublisherToDelete is an empty string, it indicates that the user wants to cancel the deletion, so no records are deleted. If strPublisherToDelete is "*", the user wants to delete all the records. Otherwise, frmSelectPublisher contains the name of the publisher whose titles the user wants to delete. The procedure builds the appropriate SQL DELETE statement and then executes the statement.

Private Sub cmdDeleteRecords_Click()
    Dim strSQL As String
    On Error GoTo DeleteRecordsError
    `Use the GetPublisher function on frmSelectPublisher to return
    `a publisher to delete.
    strPublisherToDelete = frmSelectPublisher.GetPublisher
    `If one is selected, then delete it.
    If strPublisherToDelete <> "" Then
        `Build the DELETE statement.
        strSQL = "DELETE FROM [Publisher Titles]"
        `If the publisher to delete isn't the * wildcard, then
        `modify the SQL to choose the selected publisher(s).
        If strPublisherToDelete <> "*" Then
            strSQL = strSQL & _
                     " WHERE [Publisher Titles].[Name] = " & _
                """" & strPublisherToDelete & """"
        End If
        `Execute the statement.
        dbfBiblio.Execute (strSQL)
        `Fill the list box.
        FillList
    End If
    cmdAppendRecords.Enabled = (lstData.ListCount = 0)
    cmdDeleteRecords.Enabled = (lstData.ListCount > 0)
Exit Sub
DeleteRecordsError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


10. Enter the following code as the Click event for frmMain's cmdClose:

Private Sub cmdClose_Click()
    End
End Sub


11. Switch to frmPublisherSelect and enter the following code into the declarations section. Modify the path in the Const statement to point to your copy of BIBLIO.MDB.

Option Explicit
Private Const BIBLIO_PATH = _
        "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private strPublisherToDelete As String


12. Enter the following code as the Load event for frmSelectPublisher. On loading, the form builds a recordset of publisher names in the [Publisher Titles] table through a SQL SELECT statement with the DISTINCT keyword. (See How-To 3.2 for information on the DISTINCT keyword.) It uses that recordset to fill the lstPublishers list box.

Private Sub Form_Load()
    Dim dbfBiblio As Database, recSelect As Recordset
    Dim strSQL As String
    On Error GoTo LoadError
        Set dbfBiblio = _
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        strSQL = "SELECT DISTINCT [Name] FROM [Publisher Titles]"
        Set recSelect = dbfBiblio.OpenRecordset(strSQL)
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstPublishers.AddItem recSelect![Name]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
LoadError:
    MsgBox Err.Description, vbExclamation
    strPublisherToDelete = ""
    Me.Hide
Exit Sub
End Sub


13. Enter the following code as the Click event for frmSelectPublisher's cmdOK. This procedure sets the public variable strPublisherToDelete. If the user has clicked the Delete All button, strPublisherToDelete is set to the string "*". Otherwise, strPublisherToDelete is set to the name of the selected publisher.

Private Sub cmdOK_Click()
If chkDeleteAll Then
        strPublisherToDelete = "*"
        Me.Hide
    ElseIf lstPublishers.ListIndex > -1 Then
        strPublisherToDelete = lstPublishers.Text
        Me.Hide
    End If
End Sub


14. Enter the following code as the DblClick event for frmSelectPublisher's lstPublishers. This allows the program to call the cmdOK_Click event, preventing duplication of code. A double-click of the Publishers list brings about exactly the same result as if the user had selected a publisher with a single left-click and then clicked the OK button.

Private Sub lstPublishers_DblClick()
    cmdOK_Click
End Sub


15. Enter the following code as the Click event for frmSelectPublisher's cmdCancel. This code ensures that the strPublisherToDelete string is blank, preventing the calling form's code from inadvertently deleting a publisher. Note that the form is hidden (as opposed to unloaded) here. This form is called by the GetPublisher public function and is unloaded by that function.

Private Sub cmdCancel_Click()
    strPublisherToDelete = ""
    Me.Hide
End Sub


16. Enter the following code to create the GetPublisher method for frmSelectPublisher. This is a public function, allowing you to use this form like a dialog box, resulting in this function being sent back to the calling form. You will find that this method for using forms is preferable to the "one use" form in many situations, especially when a "generic" form is used for multiple purposes.

Public Function GetPublisher() As String
    Me.Show vbModal
    GetPublisher = strPublisherToDelete
    Unload Me
End Function

How It Works

When the user clicks the cmdCreateTable button, a CREATE TABLE statement is executed (for more information on the CREATE TABLE statement, see the preceding How-To) to create an empty table in BIBLIO.MDB. The cmdAppendRecords button, when clicked, fills that empty table by executing the INSERT...INTO statement, creating the information from a SELECT query run on two other tables in the database. When the table is fully populated, the list box lstTitles fills from the new table's data via the FillList subroutine. The cmdDeleteRecords button, which deletes the newly created records, first calls the GetPublisher public function on the frmSelectPublisher form. The form presents a dialog box with options to delete records from either a single publisher or all publishers in the table. Based on this selection, the GetPublisher function returns either a publisher's name or the asterisk wildcard character. Using this information, the cmdDeleteRecords button builds and executes a DELETE statement. Last, but not least, the cmdDropTable button simply executes a DROP TABLE statement on the new table.

Comments

One of the more interesting capabilities of Visual Basic is its capacity for public functions and subroutines on forms. This allows for a wide degree of flexibility in the way you can use forms, including your ability to use a form in a manner similar to that of, say, a common dialog, by calling a public function on the form. This functionality serves well in this How-To because it makes the selection process for deletion of a group of records much easier and cleaner in terms of design and user interface.

The INSERT...INTO and DELETE statements are useful for creating and emptying temporary tables. Temporary tables, although not always the most efficient way to go, do have their purposes, and these two new tools in your arsenal should go a long way toward their proper and efficient management.

3.14 How do I...

Create a new table with data from existing tables?

Problem

I know I can use CREATE TABLE and INSERT INTO to create a table and add records to it. But in my application, I do this many times, and I'd like to accomplish it all with a single SQL operation. How can I do this?

Technique

The SELECT...INTO statement lets you create a new table with data from existing tables in a single operation. Table 3.24 shows its syntax. Similar to INSERT...INTO and DELETE statements, both covered in the preceding How-To, this tool provides an excellent way to work with temporary tables.

Table 3.24. The syntax of the SELECT...INTO statement.

CLAUSE Purpose Example
SELECT Names the fields in the existing table that will be re-created in the new table SELECT Publishers.Name, Titles.Title
INTO Names the new table INTO [Publisher Titles]
FROM Names the table (or other source) of the data FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID

To run the SELECT...INTO query, use the Execute method of the Database object. The following Visual Basic statement (on one line) executes the query shown in the table on the database represented by the variable dbfTest:

dbfTest.Execute("SELECT Publishers.Name, " & _
    "Titles.Title INTO [Publisher Titles]" & _
    " FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID")

With the example presented previously, a new table, titled [Publisher Titles], is created in the database, constructed from information gleaned from two other tables, connected by an INNER JOIN. The difference between the SELECT...INTO command and the INSERT...INTO command is simple: the INSERT...INTO command creates new records and performs an INSERT on the existing recipient table, whereas the SELECT...INTO creates a new recipient table before performing an INSERT. The SELECT...INTO statement, because of this behavior, is the ideal method of creating a temporary table in one step. In the preceding How-To, you needed two steps--one to create the table and one to add the records. SELECT...INTO combines these two steps into one, making it simpler to use and simpler to debug if problems arise. Note that the behavior on some databases differs as to exactly what happens when a SELECT...INTO statement is executed, with the recipient table having the same name as an existing table in the database. In a Microsoft Access database, the SELECT...INTO command deletes the existing table first. Some databases, however, might trigger an error in performing this action. To examine this behavior, you should create a sample table with data, execute a SELECT...INTO statement with that sample table as recipient, and note the results.

Steps

Open and run the project MAKETABL.VBP. Click the Create Table button; the list box fills with the records added to the newly created table (see Figure 3.19). Click the Drop Table button to delete the table.

Figure 3.19. The SQL Select Into form, with new table and records.

1. Create a new project called MAKETABL.VBP. Use Form1 to create the objects and properties listed in Table 3.25, and save the form as MAKETABL.FRM.

Table 3.25. Objects and properties for the Table Maker form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.14 Example
ListBox Name lstData
Sorted True
CommandButton Name cmdClose
Caption &Close
CommandButton Name cmdDropTable
Caption D&rop Table
CommandButton Name cmdCreateTable
Caption Create &Table

2. Add the following statements to the declarations section of Form1:

Option Explicit

Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private dbfBiblio As Database


3. Enter the following code as the Load event for Form1. On loading, this procedure looks for the [Publisher Titles] table in the database. If it finds the table, it fills the list box with the table's data and enables the Drop Table button. If it does not find the table, it enables the Create Table button.

Private Sub Form_Load()
Dim tdfTable As TableDef
    Dim blnTableFound As Boolean
    On Error GoTo LoadError
        blnTableFound = False
        `Open the database
        Set dbfBiblio = _
            DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
        `Check each table in the TableDefs collection; 
        `if the name matches, then allow the user to drop the 
        `table, and populate the list box.
        For Each tdfTable In dbfBiblio.TableDefs
            If tdfTable.Name = "Publisher Titles" Then
                blnTableFound = True
                cmdDropTable.Enabled = True
                cmdCreateTable.Enabled = False
                FillList
                Exit For
            End If
        Next
        `If no table was found, allow the user to create the 
        `table.
        If blnTableFound = False Then
            cmdDropTable.Enabled = False
            cmdCreateTable.Enabled = True
        End If
    On Error GoTo 0
Exit Sub
LoadError:
    MsgBox Err.Description, vbExclamation
    Unload Me
Exit Sub
End Sub


4. Create the FillList subroutine by entering the following code into Form1. This subroutine fills the list box with the contents of the [Publisher Titles] table.

Sub FillList()
Dim recSelect As Recordset
    Dim strSQL As String
    On Error GoTo FillListError
        `Clear the list box
        lstData.Clear
        `Get the [Publisher Titles] table in a recordset
        Set recSelect = dbfBiblio.OpenRecordset( _
            "SELECT * FROM " & _
            "[Publisher Titles]", dbOpenSnapshot)
        `If there are any records, fill the list box
        If recSelect.RecordCount > 0 Then
            recSelect.MoveFirst
            Do Until recSelect.EOF
                lstData.AddItem recSelect![Name] & ": " & _
                                recSelect![Title]
                recSelect.MoveNext
            Loop
        End If
    On Error GoTo 0
Exit Sub
FillListError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


5. Enter the following code as the Click event for cmdCreateTable. This procedure builds the SELECT...INTO SQL statement, building the [Publisher Titles] table from the combination of the [Name] field from the [Publishers] table and the [Title] field from the [Titles] table, as described in the "Technique" section of this How-To. When built, it then executes the statement and calls the FillList subroutine to fill the list box on the form. Finally, this step enables the Delete Records button, because (you hope) you now have records to delete.

Private Sub cmdCreateTable_Click()
Dim strSQL As String
    On Error GoTo CreateTableError
        Screen.MousePointer = vbHourglass
        `Build the SELECT INTO statement.
        strSQL = "SELECT Publishers.Name, Titles.Title " & _
            "INTO [Publisher Titles] " & _
            "FROM Publishers INNER JOIN Titles " & _
            "ON Publishers.PubID = Titles.PubID"
        `Create the new table by executing the SQL statement.
        dbfBiblio.Execute (strSQL)
        `Fill the list box with records.
        FillList
        `Set the command buttons.
        cmdCreateTable.Enabled = False
        cmdDropTable.Enabled = True
        Screen.MousePointer = vbDefault
    On Error GoTo 0
Exit Sub
CreateTableError:
    Screen.MousePointer = vbDefault
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


6. Enter the following code as the Click event for cmdDropTable. The routine executes a DROP TABLE statement against the newly created [Publisher Titles] table and reenables the Create Table button.

Private Sub cmdDropTable_Click()
On Error GoTo DropTableError
        `Execute the DROP TABLE statement
        dbfBiblio.Execute ("DROP TABLE [Publisher Titles]")
        `Set the command buttons
        cmdDropTable.Enabled = False
        cmdCreateTable.Enabled = True
        `Clear the list box.
        lstData.Clear
    On Error GoTo 0
Exit Sub
DropTableError:
    MsgBox Err.Description, vbExclamation
Exit Sub
End Sub


7. Enter the following code as the Click event for cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

When Form1 loads, it first attempts to find the [Publisher Titles] table. If it finds the table, it loads the table's information into the list box by calling the FillList subroutine, disables the Create Table button, and then enables the Drop Table buttons. If it doesn't find the table, it enables the Create Table button and disables the Drop Table button.

If the Create Table button is enabled, when clicked, it constructs and executes a SELECT...INTO statement, creating the [Publisher Titles] table and pulling in information from both the [Publishers] and the [Titles] table to populate it in one step. When complete, it loads the data into the list box by using the FillList subroutine.

The Drop Table button, if enabled, issues a DROP TABLE statement when clicked, destroying your [Publisher Titles] table in one fell swoop.

Comments

When you use SELECT...INTO to create the table, the fields in the new table inherit only the data type and field size of the corresponding fields in the query's source table. No other field or table properties are picked up from the existing table.

3.15 How do I...

Modify a table's structure?

Problem

I need to be able to add or drop columns from a table without having to use Access or go through the lengthy process of working with TableDef and Field objects. Can I do this with a simple SQL statement?

Technique

The ALTER TABLE statement lets you add or drop columns or indexes as needed, with a single SQL operation. The syntax is explained in Table 3.26, with a sample statement.

Table 3.26. The syntax of the ALTER TABLE statement.

CLAUSE Purpose Example
ALTER TABLE Selects the table to be altered ALTER TABLE [Publisher Titles]
ADD COLUMN Adds a column, defining its data type at the same time ADD COLUMN [Notes] MEMO NOT NULL

The ALTER TABLE statement uses several other keywords, as listed in Table 3.27.

Table 3.27. Additional syntax for the ALTER TABLE statement.

CLAUSE Purpose Example
DROP COLUMN Removes a column DROP COLUMN [Notes]
ADD CONSTRAINT Adds an index to the table ADD CONSTRAINT [Key1] [Notes]
DROP CONSTRAINT Removes an index statement DROP CONSTRAINT [Key1]

The Execute method is used on a Database object to perform an ALTER TABLE statement. The following example executes the queries shown in Table 3.26 on the database represented by the variable dbfTest:

dbfTest.Execute("ALTER TABLE [Publisher Titles] ADD COLUMN [Notes]
                MEMO NOT NULL")

Steps

Open and run the project ADDFIELD.VBP. Type a valid field name into the Field Name text box. Select a field type from the Field Type drop-down list and then click the Add Field button; the list box fills with the fields added to the newly created table (see Figure 3.20). Highlight one of the newly created fields and click the Remove Field button to delete the field. Note that this example is similar in appearance to the example in How-To 3.12.

1. Create a new project called ADDFIELD.VBP. Use Form1 to create the objects and properties listed in Table 3.28, and save the form as MAKETABL.FRM.

Figure 3.20. The SQL Alter Table form on startup.

Table 3.28. Objects and properties for the Table Maker form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.14 Example
ListBox Name lstFields
Sorted True
Label Name lblTableName
Caption Table &Name:
Label Name lblFieldName
Caption &Field Name:
Label Name lblFieldType
Caption Field T&ype:
Label Name lblFieldList
Caption Field Li&st:
TextBox Name txtTableName
Enabled False
TextBox Name txtFieldName
ComboBox Name cboFieldType
Style 2 - Dropdown List
CommandButton Name cmdClose
Caption &Close
CommandButton Name cmdAddField
Caption &Add Field
CommandButton Name cmdRemoveField
Caption &Remove Field

2. Add the following statements to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private Const IllegalCharacters = "[].!'"
Private dbfBiblio As Database


3. Add the following statements to the Load event of Form1. The FillTypeList and FillFieldList routines, detailed next, are called to prepare the form for use.

Private Sub Form_Load()
    `Open the database
    Set dbfBiblio = _
        DBEngine.Workspaces(0).OpenDatabase(BIBLIO_PATH)
    `Set the txtTableName control to the table that will be 
    `edited.
    txtTableName = "Title Author"
    `Fill the Field Type combo box
    FillTypeList
    `Fill the Field List list box
    FillFieldList
End Sub


4. Create the following subroutine in Form1. The FillFieldList subroutine will iterate through the Fields collection of the [Title Author] table, including the names and data types in the lstFields list box.

Sub FillFieldList()
    Dim tbfTemp As TableDef, fldTemp As Field
    Dim strFieldType As String
    `Iterate through the TableDefs collection of the database, 
    `searching for the table name specified in the txtTableName 
    `edit control.
    For Each tbfTemp In dbfBiblio.TableDefs
        `If we find the table, iterate through the Fields 
        `collection, adding each field and its field type to the 
        `Field List list box
        If tbfTemp.Name = txtTableName.Text Then
            For Each fldTemp In tbfTemp.Fields
                Select Case fldTemp.Type
                    Case dbBigInt
                        strFieldType = "BIGINT"
                    Case dbBinary
                        strFieldType = "BINARY"
                    Case dbBoolean
                        strFieldType = "BOOLEAN"
                    Case dbByte
                        strFieldType = "BYTE"
                    Case dbChar
                        strFieldType = "CHAR(" _
                                       & fldTemp.FieldSize & ")"
                    Case dbCurrency
                        strFieldType = "CURRENCY"
                    Case dbDate
                        strFieldType = "DATE"
                    Case dbDecimal
                        strFieldType = "DECIMAL"
                    Case dbDouble
                        strFieldType = "DOUBLE"
                    Case dbFloat
                        strFieldType = "FLOAT"
                    Case dbGUID
                        strFieldType = "GUID"
                    Case dbInteger
                        strFieldType = "INTEGER"
                    Case dbLong
                        strFieldType = "LONG"
                    Case dbLongBinary
                        strFieldType = "LONGBINARY"
                    Case dbMemo
                        strFieldType = "LONGTEXT"
                    Case dbNumeric
                        strFieldType = "NUMERIC"
                    Case dbSingle
                        strFieldType = "SINGLE"
                    Case dbText
                        strFieldType = "TEXT"
                    Case dbTime
                        strFieldType = "TIME"
                    Case dbTimeStamp
                        strFieldType = "TIMESTAMP"
                    Case dbVarBinary
                        strFieldType = "VARBINARY"
                End Select
                lstFields.AddItem fldTemp.Name & _
                                  " [" & strFieldType & "]"
            Next
        Exit For
        End If
    Next
End Sub


5. Create the following subroutine in Form1. The FillTypeList subroutine adds the various data types to the cboFieldType drop-down combo box.

Sub FillTypeList()
    `Fill the Field Type combo box with types of available fields
    With cboFieldTypes
        .AddItem "Counter"
        .AddItem "Currency"
        .AddItem "Date/Time"
        .AddItem "Memo"
        .AddItem "Number: Byte"
        .AddItem "Number: Integer"
        .AddItem "Number: Long"
        .AddItem "Number: Single"
        .AddItem "Number: Double"
        .AddItem "OLE Object"
        .AddItem "Text"
        .AddItem "Yes/No"
    End With
End Sub


6. Add the following code to the Click event of cmdAddField. The routine checks for a field name, ensures that the name is legal (no invalid characters, no leading spaces, and so on), determines its data type from the Field Type combo box, and finally builds the SQL needed to send the ALTER TABLE command to the database.

Private Sub cmdAddField_Click()
    Dim strFieldType As String, strSQL As String
    `Check first if the Field Name text box contains a legal name
    If LegalName(True) Then
        On Error GoTo BadAdd
            `If it does, check if the Field Type has been 
            `selected.
            If cboFieldTypes.ListIndex > -1 Then
                `If both criteria are satisfied, store the SQL 
                `field type in the strFieldType string.
                Select Case cboFieldTypes.Text
                    Case "Counter"
                        strFieldType = "COUNTER"
                    Case "Currency"
                        strFieldType = "CURRENCY"
                    Case "Date/Time"
                        strFieldType = "DATETIME"
                    Case "Memo"
                        strFieldType = "LONGTEXT"
                    Case "Number: Byte"
                        strFieldType = "BYTE"
                    Case "Number: Integer"
                        strFieldType = "SHORT"
                    Case "Number: Long"
                        strFieldType = "LONG"
                    Case "Number: Single"
                        strFieldType = "SINGLE"
                    Case "Number: Double"
                        strFieldType = "DOUBLE"
                    Case "OLE Object"
                        strFieldType = "LONGBINARY"
                    Case "Text (25 chars)"
                        strFieldType = "TEXT(25)"
                    Case "Yes/No"
                        strFieldType = "BIT"
                End Select
                `Crate the ALTER TABLE statement
                strSQL = "ALTER TABLE [" & txtTableName.Text & _
                    "] ADD COLUMN " _
                    & "[" & txtFieldName & "] " & strFieldType
                `Execute the SQL
                dbfBiblio.Execute (strSQL)
                `Add the new field to the Field List list box.
                lstFields.AddItem txtFieldName & " [" & _
                                  strFieldType & "]"
                `Reset the Field Name and Field Type controls.
                txtFieldName = ""
                cboFieldTypes.ListIndex = -1
            Else
                MsgBox "You must select a field type.", _
                       vbExclamation
            End If
        On Error GoTo 0
    End If
Exit Sub
BadAdd:
    MsgBox Err.Description, vbExclamation
End Sub


7. Create the LegalName function in Form1 with the following code. This function checks for a valid field name containing at least one character, without trailing spaces, that doesn't contain an illegal character. If it passes all that, it performs one more check, depending on the value of intAction. If intAction is True, indicating that the field is to be added, the function checks whether a field already exists. If intAction is False, indicating that the field is to be deleted, it ensures that there is no data in the field anywhere in the table.

Function LegalName(intAction As Boolean) As Boolean
    Dim i As Integer
    Dim recNameCheck As Recordset
    On Error GoTo IllegalName
        `If blank, raise an error.
        If Len(txtFieldName.Text) = 0 Then Err.Raise 32767
        `If it has a leading space, raise an error.
        If Left$(txtFieldName.Text, 1) = " " Then Err.Raise 32766
        `If it contains any of the characters in the 
        `IllegalCharacters constant, raise an error
        For i = 1 To Len(IllegalCharacters)
            If InStr(txtFieldName.Text, Mid(IllegalCharacters, _
               i, 1)) _
                > 0 Then Err.Raise 32765
        Next i
        `If it contains any ANSI character from Chr$(0) to 
        `Chr$(31), (you guessed it) raise an error.
        For i = 0 To 31
            If InStr(txtFieldName.Text, Chr(i)) > 0 _
               Then Err.Raise 32764
        Next i
        If intAction Then
            `It's an add field; ensure that the name doesn't 
            `already exist. If so, raise an error.
            For i = 0 To lstFields.ListCount - 1
                If txtFieldName.Text = lstFields.List(i) _
                   Then Err.Raise 32763
            Next i
        Else
            `It's a drop field; ensure that the field being erased 
            `contains no data. If so, raise an error
            Set recNameCheck = dbfBiblio.OpenRecordset( _
                "SELECT [" & _
                txtFieldName.Text & "] FROM [" _
                                  & txtTableName.Text & _
                "] WHERE [" & txtFieldName.Text & "] IS NOT NULL")
            If recNameCheck.RecordCount Then Err.Raise 32762
        End If

        `If they've managed to get through all that validation, 
        `the function should be True, to indicate success.
        LegalName = True
    On Error GoTo 0
Exit Function
IllegalName:
    Dim strErrDesc As String
    `Build an error message based on the user-defined error that 
    `occurred.
    Select Case Err.Number
        Case 32767
           strErrDesc = "You must enter a field name."
        Case 32766
           strErrDesc = _
              "The field name cannot begin with a space."
        Case 32765
           strErrDesc = _
              "The field name contains the illegal character " & _
                Mid(IllegalCharacters, i, 1) & "."
        Case 32764
           strErrDesc = _
              "The field name contains the control character " & _
                "with the ANSI value" & Str$(i) & "."
        Case 32763
           strErrDesc = "The field name " & txtFieldName.Text & _
                " already exists in the field name list."
        Case 32762
           strErrDesc = "The field name " & txtFieldName.Text & _
                " has data; it cannot be deleted."
        Case Else
           ` Visual Basic's default error message.
           strErrDesc = Err.Description
    End Select
    MsgBox strErrDesc, vbExclamation
    `The function indicates False, or failure.
    LegalName = False
Exit Function
End Function


8. Add the following code to the Click event of cmdRemoveField:

Private Sub cmdRemoveField_Click()
    Dim strSQL As String, strTemp As String
    ` If the user has selected a field, remove it from the list.
    ` Otherwise, just ignore the click.
    If lstFields.ListIndex > -1 Then
        `Call the lstFields_Click event, to ensure that 
        `txtFieldName is still populated. The user might have 
        `erased it after selecting a
        `field to delete.
        Call lstFields_Click
        If LegalName(False) Then
            `Build the ALTER TABLE statement
            strSQL = "ALTER TABLE [" & txtTableName.Text & _
                "] DROP COLUMN [" & _
                txtFieldName.Text & "]"
            `Execute the SQL
            dbfBiblio.Execute (strSQL)
            `Delete the field from the Field List
            lstFields.RemoveItem lstFields.ListIndex
        End If
    End If
End Sub


9. Add the following code to the Click event of lstFields. This code extracts the name of the field selected in lstFields and passes it to the txtFieldName text box.

Private Sub lstFields_Click()
    Dim strTemp As String
    `If a field has been selected, extract the field's name from
    `the list entry and display it in the txtFieldName control.
    If lstFields.ListIndex > -1 Then
        strTemp = lstFields.List(lstFields.ListIndex)
        strTemp = Left(strTemp, InStr(strTemp, "[") - 2)
        txtFieldName.Text = strTemp
    End If
End Sub


10. Add the following code to the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

When you load Form1, it prepares for use by running the FillTypeList routine, which loads the cboFieldTypes combo box with the various field types allowed by Visual Basic, and the FillFieldList routine, which loads all the field information from a given table into the lstFields list box. The Form_Load event defaults the table name for this routine to the [Title Author] table.

Each time the user adds a field to the table, an ALTER TABLE statement is concatenated in the cmdAddField_Click routine. Several steps are performed to ensure that the entered field is valid and meets the criteria for the statement. The routine uses the LegalName function to determine whether the field name specified is legal for use--doesn't have any illegal characters, has at least one character, and doesn't start with a space (ASCII 32). After that step, the routine fetches the field's type from the cboFieldTypes combo box and translates the English-readable selection into a valid SQL data type. After the translation is complete, it builds and executes the ALTER TABLE statement, using the ADD COLUMN keywords to create the field. If run successfully, it adds the newly created field to the lstFields list box.

Removing a field, however, is much less involved. Given the selected field name from the lstFields list box, and after the LegalName function is called to ensure that the selected field contains no data, another ALTER TABLE statement is issued, this time utilizing the DROP COLUMN keywords to remove the field from the table. After execution is complete, the field is then removed from the lstFields list box.

Comments

The ALTER TABLE has different behaviors depending on the database platform. Microsoft SQL Server, for example, won't allow a field-level constraint (for example, restricting a field's data to a certain range of values) to be added to an already existing field. As with the SELECT...INTO statement (covered previously in How-To 3.14), the best way to ensure that you get a complete understanding of how the database reacts to the ALTER TABLE statement is to experiment and observe the results.

3.16 How do I...

Create a crosstab query?

Problem

I need to be able to supply a worksheet-style query showing cross-referenced information easily. How do I do this?

Technique

The new features of the Microsoft Jet (3.5 and above) engines include the capability to create crosstab, or cross-tabulated, queries. Think of a crosstab query as a spreadsheet, with the information provided by the query read by referencing the row and column of the spreadsheet. For example, using your old familiar friend BIBLIO.MDB, you need to get a count of all the titles published since 1975, year by year, listed by publisher. Normally, this job would take a couple of queries, but the crosstab query allows you to use some SQL "sleight of hand" in performing this action by adding a couple of new SQL keywords to your arsenal.

In the following sample query, notice the TRANSFORM and PIVOT keywords. These new additions allow Jet to construct a crosstab query.

TRANSFORM Count(Titles.Title) AS [TitlesCount] 
    SELECT Publishers.Name FROM Publishers INNER JOIN Titles ON 
    (Titles.PubID = Publishers.PubID) WHERE Titles.[Year Published] 
    > 1975 GROUP BY Publishers.Name
PIVOT Titles.[Year Published]

Table 3.29 lists the TRANSFORM and PIVOT keywords, used to create a crosstab query.

Table 3.29. The syntax of the crosstab query.

CLAUSE Purpose Example
TRANSFORM Selects the data to be shown in the body of the query TRANSFORM Count(Titles.Title) AS [TitlesCount]
SELECT Chooses the row information for the query, in a crosstab query SELECT Publishers.Name FROM Publishers INNER JOIN Titles ON( Titles.PubID = Publishers.PubID) WHERE Titles.[Year Published] > 1975


PIVOT
SELECTS THE COLUMN INFORMATION FOR THE QUERY PIVOT TITLES.[YEAR PUBLISHED]
To better understand the results of this query, visualize the results as a spreadsheet. The SELECT creates the rows of the spreadsheet; in the preceding example, a row is created for each publisher with a title published after 1975. The PIVOT creates the columns of the spreadsheet--a column for each year a title was published after 1975. The TRANSFORM statement creates the information on the spreadsheet where each row and column intersect--in the preceding query, a count of titles.

Steps

Open and run the project CROSSTAB.VBP. When the form appears, a grid displays the count of all the titles published after 1975, listed by publisher, as shown in Figure 3.21.

Figure 3.21. The SQL Transform form, displaying crosstab data.

1. Create a new project called CROSSTAB.VBP. Use Form1 to create the objects and properties listed in Table 3.30, and save the form as CROSSTAB.FRM.

Table 3.30. Objects and properties for the Crosstab form.

OBJECT Property Setting
Form Name Form1
Caption Chapter 3.16 Example
Data Name dtaData
MSFlexGrid Name grdCrossTab
DataSourcw dtaData
Label Name lblCrossTab
Caption Titles per year published after 1975, sorted by Publisher ID
CommandButton Name cmdClose
Caption &Close

2. Add the following statements to the declarations section of Form1:

Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"


3. Add the following statements to the Load event of Form1. At this point, the event constructs the SQL statement used for the crosstab query (explained earlier in this How-To) and places it in the RecordSource property of the dtaData Data control. After it's added, the Data control is refreshed to execute the query and return the needed records, which will automatically display in the bound MSFlexGrid control, grdCrossTab.

Private Sub Form_Load()
    Dim strSQL As String
    `Construct the crosstab query statement.  Note the use of 
    `several new SQL keywords, including TRANSFORM and PIVOT.  
    `These two keywords are the building blocks of the crosstab 
    `query.
    strSQL = "TRANSFORM Count(Titles.Title) AS [TitlesCount] " & _
        "SELECT Publishers.Name FROM Publishers "& _
                INNER JOIN Titles " & _
        "ON (Titles.PubID " & _
        "= Publishers.PubID) " & _
           WHERE Titles.[Year Published] > 1975 " & _
        "GROUP BY Publishers.Name " & _
        "PIVOT Titles.[Year Published]"
    `Set up the Data control
    With dtaData
        .DatabaseName = BIBLIO_PATH
        .RecordSource = strSQL
        .Refresh
    End With
End Sub


4. Add the following statements to the Click event of cmdClose:

Private Sub cmdClose_Click()
    End
End Sub

How It Works

When Form1 loads, it constructs the sample crosstab query detailed earlier in the How-To. It then uses the dtaData Data control to execute it and retrieve the records for display in grCrossTab.

Comments

The crosstab query is a powerful tool for generating tabular data, especially aggregate or arithmetically derived information such as counts or statistical data, for a quick summary. Normally, without the TRANSFORM or PIVOT keywords, performing a query like the one you used would require two or three queries and possibly a temporary table, making it a complex task not only to execute but also to maintain. A crosstab query takes all that work and hands it to the database to perform, making the tedious job of cross-indexing information that much faster and easier.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.