Before I end this section of the chapter, we must look at the process of specifying queries with SQL statements and attaching them to the database. To get started, follow these steps:
- Start the Visual Data Manager and open the BIBLlO database.
- In the Database window, right- click the All Titles query, and from the shortcut menu, select Design. Visual Data Manager displays another window containing the definition of the query, as shown in Figure 17.18.
FIGURE 17.18
The name of the query appear in the RecordSource property of a Data control connected to the BIBLIO database. We will use this query later to revise the Many- Tbls application, but first let’s look at the tools of the Visual Data Manager for building SQL queries.
Building SQL Queries
If you have found SQL difficult to learn, you’ll be pleasantly surprised to find out that the Visual Data Manager can generate simple SQL statements with point-and click operations. Right-click in the Database window of the Visual Data Manager, and from the shortcut menu, select New Query to open the Query Builder window, as shown in Figure 17.19.
Let’s use the Visual Data Manager to build a simple SQL statement. Follow these steps to create a query that retrieves all titles and their publishers:
- Select the names of the Publishers and Titles tables by clicking !heir names in the Tables list. The fields of the selected tables will appear in the Fields to Show list.
- Now click the names of the fields you want to include in the query (the RecordSet that this query will return). Click the following field names: Titles .Title, Titles.[Year Published], and Publishers.Name .
Now we’ll get the difficult part of the SQL statement out of the way. Click the Set Table Joins button to define the joins (in other words, to specify how the tables will be combined). The Visual Data Manager doesn’t use the JOIN operator. It implements joins with the WHERE keyword, which means you can’t rely on this tool to implement advanced queries. - In the Select Table Pair list, click the names of the tables Titles and Publishers. The fields of the two tables will appear in the lists under the heading Select Fields to Join On, as shown in Figure 17.20.
FIGURE 17.20 - Now click the PubID field in both lists. The Titles and Publishers tables are joined on the value of the PubID field. Records with matching PubID fields are joined.
- Click the Add Join to Query button to add the join to the query.
- Click the Close button to return to the Query Builder window. The query is defined. .
- Click the Show button to display the SQL statement that implements the query, or click theRun button to execute it. When prompted as to whether this is a SQLPassThrough Query, click No. You will see a new Form with a Data control that lets you navigate through the retrieved records.
- Click the Save button to attach the query to the database. Visual Data Manager prompts you for the name of the query and attaches it to the database. The next time you connect a Data control to the database, you will see the query’s name in the list of available RecordSource items.
You can use the Query Builder to specify selection criteria, which are also implemented with the WHERE keyword, to group and order the results of the query and to limit the size of the RecordSet to a number of records if it’s too long.
To add selection criteria, for example, select a field name in the Field Name list, select the operator, and then specify a value in the Value box. If you don’t remember the values of a specified field, click the List Possible Values button, and the Visual Data Manager retrieves all the values of the specified list and displays them in the list, from which you can select one.
The SQL statements generated by the Query Builder are relatively simple and are adequate for simple queries (in case you were wondering why I chose such a simple example). For example, the Query Builder can’t create multiple joins. However, you can always edit the SQL statement (by opening the query in design mode) and add . more keywords to it. If you must create SQL statements, yet you don’t feel comfortable with the language, you can use a more elaborate tool, such as Micrsoft Access.
VB6 at Work: SQLTbls, ManyTbls Revised
“The Many Thls application c~mbined all the tables of the BIBLIO database to display the titles, along with their authors and publishers. It used the Seek method to combine the tables, not because this was the most efficient implementation button demonstrate the use of indices and the Seek method. New we are going to implement a similar application, but we’ll use SQL statements to define the Recordset with the fields we need. Follow these steps:
- Open the Many Thls application and save it as SQLTbls (change the name of the Form to SQLTbls too) and store it in a new folder.
- Delete all the Data controls on the Form and place a new one on it. Name it TITLES, and set its Caption property to Titles-Authors-Publishers.
- Connect this control to the BIBLIO database by setting the DatabaseName property to the path name of the database on your hard disk.
- Now, expand the RecordSource property list and select All Titles. This is the name of a SQL query stored in the database, and it returns the desired Record . Set (the titles with their authors and publishers).
- Because this RecordSet is created with a SQL statement,-it’s a DynaSet-type RecordSet, so you must set the Data control’s RecordSetType property to 1-DynaSet.
- Now, adjust the DataSource and DataFields properties of the data-bound controls on the Form so that they see the corresponding fields of the RecordSet via the Data1 Data control. You must set their DataSource property to Data1 and their DataField properties to Title, Company Name, Year Published, and Author.
- The last two fields on the application’s main Form (Description and Comments) are not part of~ All Titles query, so delete them horn the Form. You can also . change the query or create a new one that contains these-fields, based on the All Titles query.
- Now, delete all the code in the application, because you don’t need it any longer.
The SQL statement, in effect, takes the role of t!te application’s code. It does the same thing, only it’s as compact and as efficient as it can be.
