Microsoft KB Archive/113335

= How to Create Nested Queries in Visual Basic 3.0 Prof Edition =

Article ID: 113335

Article Last Modified on 9/1/2006

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q113335



SUMMARY
Subqueries are Select statements that are embedded within an outer query. The Microsoft Access engine in Visual Basic version 3.0 and in Microsoft Access version 1.1 cannot handle subqueries directly.

Here's an example of a subquery: UPDATE Orders SET Orders.ItemCount = (SELECT COUNT(*) WHERE     Orders.OrderNum = Item.OrderNum) This particular query could be handled with the intrinsic Microsoft Access engine's DCount function: UPDATE Orders SET Orders.ItemCount = DCount('*'','Orders','Orders.OrderNum = Item.OrderNum') However, there are many cases when an intrinsic function is not available but the need for a subquery remains. In these cases, the database programmer can use nested queries to accomplish the same task.



MORE INFORMATION
NOTE: The following queries are based on the sample database (BIBLIO.MDB) that comes with Visual Basic version 3.0. BIBLIO.MDB contains entries on actual reference works dealing with database programming.

For example, subqueries are useful when you need to find all the books that were published the same year as the book, "The database experts' guide to SQL." You could do this in two steps. First, find out what year that the book was published: SELECT DISTINCTROW [year published] from titles WHERE titles.title = 'The database experts'' guide to SQL' WITH OWNERACCESS OPTION; Then take the result (1988) and build a second query based on this result: SELECT DISTINCTROW title,[year published] from titles WHERE titles.[year published] = 1988 WITH OWNERACCESS OPTION; Microsoft SQL Server syntax would support the combination of these two queries into one query with a subquery: SELECT DISTINCT title,[year published] From titles WHERE titles.[year published] = (Select [year published] from titles      WHERE titles.title = 'The database experts'' guide to SQL' ) However, the Microsoft Access engine cannot parse this directly, so the alternative is to create a QueryDef in a Microsoft Access-format database; then reference this querydef in a succeeding query. This nesting can be of multiple levels. However, all the nested queries must be resolved in order to return the result from the outer or topmost query. Therefore, you may find that at some multiple level of nesting, performance will become unacceptable or the engine's internal workspace capacity will be exceeded.

NOTE: Even if the data sources are not Microsoft Access databases (.MDB files), the performance gains from making the data sources into attached tables in a .MDB database as well as the added benefit of being able to create and store querydefs, makes the use of even a data-free .MDB file a compelling choice. The .MDB file could be used to hold only the TableDefs of attached tables and QueryDefs, whether permanently stored or created on the fly during the execution of your program.

Visual Basic Code -- Example One
Here's an illustration of the Visual Basic solution to the limitation on subqueries. The following shows the code that can handle the specific example referenced above by using a nested query. The goal is to obtain all the books published the same year as the book "The database experts' guide to SQL." Dim db As database, ds As Dynaset, qd As QueryDef Set db = OpenDatabase("C:\VB\BIBLIO.MDB")

' Formulate subquery. Enter all three lines as one, single line of code: subq$ = "SELECT DISTINCTROW [year published] from titles   where titles.title = 'The database experts'' guide to SQL'   WITH OWNERACCESS OPTION;"

' NOTE: Because the book title contains an embedded single quotation mark ' or apostrophe, note the use of doubled apostrophes in the book title ' string literal to avoid confusing the SQL parser.

' For testing purposes, delete any existing QueryDef, and change the ' first run db.DeleteQueryDef ("Year Book Was Published") into a comment. ' Next, create a QueryDef in the BIBLIO.MDB: Set qd = db.CreateQueryDef("Year Book Was Published", subq$)

' Now, the following SQL statement will obtain the desired results. ' Enter the following five lines as one, single line in Visual Basic: Set ds = db.CreateDynaset("SELECT DISTINCTROW  title,titles.[year published]   from titles, [Year Book Was Published] where titles.[year published] =   [Year Book Was Published].[year published]   WITH OWNERACCESS OPTION;") Notice that the column or field name returned by the QueryDef, [year published], is available as a valid field reference in the Where clause of the outer query. If the column names are aliased, using the As syntax, then the aliasname must be used by the outer query when referring to columns returned by the query, as in Example Two.

Visual Basic Code -- Example Two
Dim db As database, ds As Dynaset, qd As QueryDef

Set db = OpenDatabase("E:\PROGDIR\VB3\BIBLIO.MDB")

' Enter the following four lines as one, single line: s$ = "select authors.au_id as temp1,authors.author as temp2,  titles.title as temp3, titles.pubID as temp4 from authors,titles,   authors inner join titles on authors.au_ID=titles.au_ID   order by authors.author"

' For testing purposes, delete any existing QueryDef. Turn the following ' into a comment line on the first run: db.DeleteQueryDef ("Nested")

Set qd = db.CreateQueryDef("Nested", s$)

' Now build a query based on the columns in the inner nested QueryDef. ' Enter the following two lines as one, single line of code: Set ds = db.CreateDynaset("select temp1, temp2, temp3, publishers.name from  publishers, Nested where publishers.pubid=temp4 order by temp2")

Same Queries Without the Use of Aliases
The same queries without the use of aliases would be as follows. Note that you need to enter each query as one, single line: ' Enter the following three lines as one, single line: s$ = "select authors.au_id ,authors.author, titles.title , titles.pubID  from authors,titles, authors inner join titles on   authors.au_ID=titles.au_ID order by authors.author"

' For testing purposes, delete any existing QueryDef. Turn the following ' into a comment line on the first run: db.DeleteQueryDef ("Nested")

Set qd = db.CreateQueryDef("Nested", s$)

' Now build a query based on the columns in the inner nested QueryDef. ' Enter the following three lines as one, single line of code: Set ds = db.CreateDynaset("select authors.au_id,authors.author,  titles.title,publishers.name from publishers,   Nested where publishers.pubid=titles.pubid order by authors.author")

Getting the Results of a Non-Existence Query
A more challenging application is to get the results of a non-existence query. For example, if you needed to find out all the publishers who have no books in the database that were published in the year 1988, some form of subquery would be needed. Therefore, you could do it using nested queries in the Microsoft Access engine in Visual Basic version 3.0 and Microsoft Access version 1.1.

The first QueryDef is built on the following SQL statement: SELECT DISTINCTROW titles.title, titles.[year published],titles.pubid FROM titles WHERE titles.[year published]= 1988 WITH OWNERACCESS OPTION; This returns those book titles, along with their associated pubid field, that were published in 1988. Next, the following outer join (Left Join in Microsoft Access syntax), allows you to look for non-existence, in the form of Nulls in the result set: SELECT DISTINCTROW publishers.pubid, publishers.name FROM publishers, [titles in 1988], publishers LEFT JOIN [titles in 1988] ON publishers.pubid = [titles in 1988].pubid WHERE [titles in 1988].pubid Is Null WITH OWNERACCESS OPTION;

Visual Basic Code -- Example Three
NOTE: Place the command button Command1 in the lower left corner of the form to allow room for the printing to the form. Sub Command1_Click Dim db As database Dim ds As Dynaset Dim qd As QueryDef Dim NL$, Tabb$, subq$, query$

NL$ = Chr$(13) & Chr$(10) Tabb$ = Chr$(9) & Chr$(9)

Set db = OpenDatabase("E:\PROGDIR\VB3\BIBLIO.MDB")

' Formulate the query that selects titles published in 1988. ' Enter the following three lines as one, single line of code: subq$ = "SELECT DISTINCTROW titles.title, titles.[year  published],titles.pubid FROM titles   WHERE titles.[year published]= 1988 WITH OWNERACCESS OPTION;"

' For testing purposes, delete any existing QueryDef. Turn the following ' line into a comment on the first run: db.DeleteQueryDef ("titles in 1988")

' Next, create the inner query: Set qd = db.CreateQueryDef("titles in 1988", subq$)

' Print to the form the contents of the sub query for a sanity check: Me.WindowState = 2 ' Maximize form for more room Set ds = qd.CreateDynaset Print " Name : "; ds.Name While Not ds.EOF For i = 0 To ds.Fields.Count - 1 Print ds(i); Tabb$; Next i  Print ds.MoveNext Wend

Print NL$, NL$

' Now formulate a query based on the previous query. ' Enter the following four lines as one, single line of code: query$ = "SELECT DISTINCTROW publishers.pubid, publishers.name FROM  publishers, [titles in 1988], publishers LEFT JOIN [titles in 1988]   ON publishers.pubid = [titles in 1988].pubid WHERE [titles   in 1988].pubid Is Null WITH OWNERACCESS OPTION;"

' For testing purposes, delete any existing QueryDef. But turn the ' following into a comment on the first run: db.DeleteQueryDef ("publishers who have no titles in 1988")

Set qd = db.CreateQueryDef("publishers who have no titles in 1988", query$) Set ds = qd.CreateDynaset

' Print to the form the contents of the outer query: Print " Name : "; ds.Name While Not ds.EOF For i = 0 To ds.Fields.Count - 1 Print ds(i); Tabb$; Next i  Print ds.MoveNext Wend

Print NL$, NL$

' Or print the contents of a Dynaset directly based on the subquery: Set ds = db.CreateDynaset(query$)

' Enter the following two lines as one, single line of code: Print "The results of a direct CreateDynaset with SQL referencing the QueryDef [titles in 1988]"

Print " Name : "; ds.Name While Not ds.EOF For i = 0 To ds.Fields.Count - 1 Print ds(i); Tabb$; Next i  Print ds.MoveNext Wend

ds.Close qd.Close db.Close End Sub

Additional query words: 3.00

Keywords: KB113335

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.