Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Chapter 9
Query

The DBUser class described in Chapter 8 uses a QueryDef object to update a user’s password information in the Passwords table. It uses the object’s Execute method to run an SQL UPDATE statement.

QueryDef objects can execute other SQL commands as well. The Query application described in this chapter uses this fact to implement an extremely flexible database management tool. Query allows a user to enter and execute any SQL statement.

The first section in this chapter, “Using Query,” describes Query from the user’s point of view. It explains how the user can enter and execute queries and save query text and results in files.

The “Key Techniques” section lists the Visual Basic programming techniques used by the Query application. The rest of the chapter describes those techniques in detail.

Using Query

Figure 9.1 shows the Query program in action. The user enters one or more SQL statements separated by semi-colons in the upper text box and clicks the Run button. The program executes the statements and presents the results in the lower text box.

The Query application interacts with three file-like items that the user may want to manipulate: the SQL statement text, the result text, and the database file itself.

After entering a series of SQL statements, the user might want to save those statements in a file so they can be easily reloaded and executed later. Query’s File menu provides a standard set of New, Open, Save, and Save As commands that allow the user to manipulate SQL statement files. The File menu also provides a recent file list that shows the four SQL files most recently accessed by the program. All of these commands are similar to those used by the ExpenseReporter application described in Chapters 1 and 2.

Query is intended for use as an ad hoc database querying tool. It assumes the user will usually enter a few SQL statements, execute them, and quit. Because the user will probably not want to save the SQL statements, Query does not present a warning before exiting if the statements have not been saved to a file. It would be easy to change this behavior using the techniques described in Chapter 1.

Figure 9.1  The Query application.

The second file-like item in Query is the result text. This text contains success and failure messages, and the results of any SQL SELECT statements. The File menu’s Save Results As command allows the user to save these results into a text file. The user can then use the results to create simple reports.

Query makes the results easier to manipulate by displaying them in a text box rather than in a label control. The user can use the mouse to copy the text in the text box and paste it into other applications. The Locked property of the text box is set to true so the user cannot accidentally modify the results within the Query program.

The final file-like item used by Query is the database file. Before the user can execute SQL statements, the program must be connected to a database. The Database menu’s Connect command allows the user to open a database file. The Disconnect command closes the database so the user can open another.

The Database menu also contains a recent database list similar to the File menu’s recent query file list. This list holds the names of the four most recently accessed database files. The user can select one of these to reconnect to a database quickly without searching for it using a file selection dialog.

Key Techniques

The following list describes the key concepts demonstrated by the Query application. The rest of this chapter describes these concepts in detail.

•  Creating Databases. The Data Manager is not the only way to create a new database. This section shows how a Visual Basic application can create a new Access database.

•  Composing SQL Commands. The structured query language provides a large assortment of commands for manipulating relational databases. This section describes some of the more important SQL commands.

•  Processing SQL Statements. Processing SQL statements is the main goal of the Query application. This section explains how Query separates SQL statements, removes comments from them, and executes them.

Creating Databases

Subroutine ConnectToDatabase first attempts to open the specified database file using the default Workspace object’s OpenDatabase method. If that method raises error number 3024, the database does not exist so the program asks the user if it should create the database.

If so, ConnectToDatabase uses the Workspace object’s CreateDatabase command to create the new database. The format of the new database will depend on the version of the data access objects used by the application. If the application is a 16-bit Visual Basic program, Query uses version 2.5 of the data access objects so it creates a version 2.5 format database.

If the application is a 32-bit Visual Basic program, it can use either the 2.5 or 3.0 version of the data access objects. The References command in the Visual Basic development environment’s Tools menu determines which version of the data access objects the program uses.

Whether the subroutine ConnectToDatabase opened an existing database or created a new one, it next uses the database’s CreateQueryDef method to create a new QueryDef object. That object will be used later to execute SQL statements on the database.