How to Perform SubQuery on Microsoft Access 2.0 DB in VB 3.0 (114344)

SUMMARY

The code sample in this article demonstrates how to set up and use a SubQuery on a Microsoft Access version 2.0 database (NWIND.MDB) and on a Microsoft Access version 1.x database (BIBLIO.MDB from Visual Basic version 3.0).

NOTE: For the NWIND.MDB database, the code sample requires that you have Microsoft Access version 2.0 and the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer.

For more information about the Compatibility layer, please see the following articles in the Microsoft Knowledge Base:

113594 Updated ACC2COMP.TXT for Jet 2.0/VB 3.0 Compatibility Layer

113683 Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

113684 Installation Issues with Jet 2.0/VB 3.0 Compatibility Layer

113951 How to Obtain & Distribute the Compatibility Layer

MORE INFORMATION

Using the SubQuery in Visual Basic

Start a new project in Visual Basic. Form1 is created by default.

Load the following into a text editor and save it as SUBQ.FRM. Then load the SUBQ.FRM form into your Visual Basic project and set it as your start-up form. Edit each statement that appears on more than one line so that it appears on one, single line before loading the form into Visual Basic. Also double check the paths to the database files.

   VERSION 2.00
   Begin Form SubQ
    Caption         =   "Form1"
    ClientHeight    =   4020
    ClientLeft      =   1095
    ClientTop       =   1485
    ClientWidth     =   7365
    Height          =   4425
    Left            =   1035
    LinkTopic       =   "Form1"
    ScaleHeight     =   4020
    ScaleWidth      =   7365
    Top             =   1140
    Width           =   7485
    Begin Data Data2
       Caption         =   "Data2"
       Connect         =   ""
       DatabaseName    =   "C:\VB\BIBLIO.MDB"
       Exclusive       =   0   'False
       Height          =   375
       Left            =   4680
       Options         =   0
       ReadOnly        =   0   'False
       RecordSource    =   "Titles"
       Top             =   3240
       Visible         =   0   'False
       Width           =   2175
    End
    Begin CommandButton Command2
       Caption         =   "VB 3.0 Biblio(Access 1.x) Subquery
        sample"
       Height          =   735
       Left            =   3480
       TabIndex        =   3
       Top             =   840
       Width           =   3855
    End
    Begin ListBox List2
       Height          =   1200
       Left            =   4320
       TabIndex        =   2
       Top             =   1800
       Width           =   2775
    End
    Begin ListBox List1
       Height          =   1200
       Left            =   360
       TabIndex        =   1
       Top             =   1800
       Width           =   2775
    End
    Begin CommandButton Command1
       Caption         =   "Access 2.0 Nwind Subquery sample"
       Height          =   735
       Left            =   120
       TabIndex        =   0
       Top             =   840
       Width           =   3255
    End
    Begin Data Data1
       Caption         =   "Data1"
       Connect         =   ""
       DatabaseName    =   "C:\ACCESS\SAMPAPPS\NWIND.MDB"
       Exclusive       =   0   'False
       Height          =   375
       Left            =   720
       Options         =   0
       ReadOnly        =   0   'False
       RecordSource    =   "Customers"
       Top             =   3240
       Visible         =   0   'False
       Width           =   1695
    End
    Begin Label Label1
       ' Edit the following four lines into one, single line:
       Caption         =   "Two samples of using Subqueries with
        an Access 2.0 NWIND database and another sample of using
        the Subquery with the VB 3.0 Biblio.MDB(Access 1.1)
        database"
       Height          =   615
       Left            =   1200
       TabIndex        =   4
       Top             =   120
       Width           =   5295
    End
   End

   Sub Command1_Click ()
   ' Edit the following five lines into one, single line:
   data1.RecordSource = "SELECT DISTINCTROW Customers.[Contact
   Name], Customers.[Company Name], Customers.[Contact Title],
   Customers.Phone FROM Customers WHERE ((Customers.[Customer ID]
   In (SELECT DISTINCTROW Orders.[Customer ID] FROM Orders WHERE
   Orders.[Order Date] >= #04/1/93# <#07/1/93#)));"
   data1.Refresh
   Do Until data1.Recordset.EOF
      list1.AddItem "" & data1.Recordset("customers.[company name]")
      data1.Recordset.MoveNext
   Loop
   End Sub

   Sub Command2_Click ()
   ' Edit the following three lines into one, single line:
   data2.RecordSource = "SELECT titles.title FROM titles WHERE
   ((titles.pubid IN (SELECT DISTINCTROW publishers.pubid FROM
   publishers WHERE state = 'WA')));"
   data2.Refresh
   Do Until data2.Recordset.EOF
      list2.AddItem "" & data2.Recordset("titles.title")
      data2.Recordset.MoveNext
   Loop
   End Sub

From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button, then the Command2 button.