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

- Microsoft Visual Basic programming system for Windows, version 3.0

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:

ARTICLE-ID: Q113594

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

TITLE     : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

TITLE     : Installation Issues with Jet 2.0/VB 3.0 Compatibility Layer

TITLE     : Files Included in Jet 2.0/ VB 3.0 Compatibility Layer

TITLE     : How to Obtain & Distribute the Compatibility Layer

MORE INFORMATION

Using the SubQuery in Visual Basic

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

2. 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
   
   

3. 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.