
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
| 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.
| 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 MATTERIn 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.
| 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.
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
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"
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
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.
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.
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
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
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
Private Sub RefreshControls(strSQL as string)
dtaData.RecordSource = strSQL
dtaData.Refresh
dlstData.Refresh
End Sub
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.
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.
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 ""
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB" Dim dbfBiblio As Database
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
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
strSQL = strSQL & " WHERE [Company Name] = `" & dblPublishers.Text _
& "`"
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
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") & "`"
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.
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
Option Explicit Private Const BIBLIO_PATH = "D:\Program Files\Microsoft Visual Studio\ ÂVB6\Biblio.MDB"
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
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."
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.
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
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
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
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
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."
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 JOINSIt'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.
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:
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private Sub Form_Load()
`Set the DatabaseName for the Data control.
dtaData.DatabaseName = BIBLIO_PATH
End Sub
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.
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 JOINSThere 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.
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:
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private Sub Form_Load()
`Set the DatabaseName of the Data control.
dtaData.DatabaseName = BIBLIO_PATH
End Sub
Private Sub cmdClose_Click() EndEnd 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.
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.
| 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 InvoicesWHERE [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.
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:
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private Sub Form_Load()
`Set the DatabaseName of the Data control.
dtaData.DatabaseName = BIBLIO_PATH
End Sub
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.
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.
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
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
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
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.
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
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
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.
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".
| 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.
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB" Private dbfBiblio As Database
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
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
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
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.
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.
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:
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
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"
Private Sub Form_Load()
`Fill the Field Type combo box.
FillTypeList
End Sub
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
Private Sub cmdListTables_Click()
` Display the Table List form modally.
frmTableList.Show vbModal
End Sub
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
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
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
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private Sub Form_Load()
` Fill the list box with the current non-system tables in
` BIBLIO.MDB.
ListTables
End Sub
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
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
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
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.
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.
| 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.
| 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
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB" Private strPublisherToDelete As String Private dbfBiblio As Database
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
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
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
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
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
Private Sub cmdClose_Click()
End
End Sub
Option Explicit
Private Const BIBLIO_PATH = _
"D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
Private strPublisherToDelete As String
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
Private Sub cmdOK_Click()
If chkDeleteAll Then
strPublisherToDelete = "*"
Me.Hide
ElseIf lstPublishers.ListIndex > -1 Then
strPublisherToDelete = lstPublishers.Text
Me.Hide
End If
End Sub
Private Sub lstPublishers_DblClick()
cmdOK_Click
End Sub
Private Sub cmdCancel_Click()
strPublisherToDelete = ""
Me.Hide
End Sub
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.
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.
| 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.
| 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 |
Option Explicit
Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB" Private dbfBiblio As Database
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
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
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
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
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.
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.
| 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.
| 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.
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB" Private Const IllegalCharacters = "[].!'" Private dbfBiblio As Database
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
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
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
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
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
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
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
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.
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.
| 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 |
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.
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
Option Explicit Private Const BIBLIO_PATH = _ "D:\Program Files\Microsoft Visual Studio\VB6\Biblio.MDB"
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
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.