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

Last reviewed: June 21, 1995
Article ID: Q114344
The information in this article applies to:

- 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

ARTICLE-ID: Q113683
TITLE     : Fact Sheet on Microsoft Jet 2.0/VB 3.0 Compatibility Layer

ARTICLE-ID: Q113684
TITLE     : Installation Issues with Jet 2.0/VB 3.0 Compatibility Layer

ARTICLE-ID: Q113685
TITLE     : Files Included in Jet 2.0/ VB 3.0 Compatibility Layer

ARTICLE-ID: Q113951
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.


Additional reference words: 3.00 CompLayer
KBCategory: kbprg kbcode
KBSubcategory: APrgDataAcc


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.