How to Create Nested Queries in Visual Basic 3.0 Prof Edition

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

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)

   UPDATE Orders SET Orders.ItemCount =
      DCount('*'','Orders','Orders.OrderNum = Item.OrderNum')

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;

   SELECT DISTINCTROW  title,[year published] from titles
      WHERE titles.[year published] = 1988
      WITH OWNERACCESS OPTION;

   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' )

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. For additional information, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q108423

TITLE     : How to Attach an External Database Table to a VB 3.0 Database

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:

   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:

' Now, the following SQL statement will obtain the desired results.
' Enter the following five lines as one, single line in Visual Basic:

   title,titles.[year published]
   from titles , [Year Book Was Published] where titles.[year published] =
   [Year Book Was Published].[year published]
   WITH OWNERACCESS OPTION;")

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:

   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:

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:

   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:

   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:

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:

   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;

   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 ()

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:

   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:

' Next, create the inner query:

' Print to the form the contents of the sub query for a sanity check:

   For i = 0 To ds.Fields.Count - 1
      Print ds(i); Tabb$;
   Next i
   Print
   ds.MoveNext

Print NL$, NL$

' Now formulate a query based on the previous query.
' Enter the following four lines as one, single line of code:

   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:

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:

   For i = 0 To ds.Fields.Count - 1
      Print ds(i); Tabb$;
   Next i
   Print
   ds.MoveNext

Print NL$, NL$

' Or print the contents of a Dynaset directly based on the subquery:

' Enter the following two lines as one, single line of code:

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

ds.Close qd.Close db.Close

End Sub