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