Microsoft KB Archive/114344

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

Article ID: 114344

Article Last Modified on 7/11/2006

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q114344



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



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.

Additional query words: 3.00 CompLayer

Keywords: KB114344

-

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

© Microsoft Corporation. All rights reserved.