How to Perform a Union Query in Visual Basic Version 3.0 (113592)



The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0

This article was previously published under Q113592

SUMMARY

The sample program in this article demonstrates how to perform a Union query on a Microsoft Access version 2.0 database (NWIND.MDB) and on a Microsoft Access version 1.x database (BIBLIO.MDB included with Visual Basic version 3.0).

Union queries are new to Microsoft Access version 2.0, so you must have installed the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer. For more information about the Compatibility Layer, please see the following article in Microsoft Knowledge Base:

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

For information on how to obtain the Compatibility Layer, please see the following article in the Microsoft Knowledge Base:

113951 How to Obtain & Distribute the Compatibility Layer

The sample in this article also makes use of the NWIND.MDB database that is distributed with Microsoft Access version 2.0. This version of the NWIND.MDB database is different from the one that shipped with Microsoft Access version 1.1.

MORE INFORMATION

Union queries help you combine fields from two or more tables or queries. A union query returns all the records from corresponding fields in the included tables or queries. In contrast, a join query returns a recordset containing data only from records whose related fields meet a specific criteria.

Example Using the Union Query in Visual Basic

  1. Start a new project in Visual Basic. Form1 is created by default.
  2. Add one Label (Label1), two Command buttons (Command1 and Command2), two List Boxes (List1 and List2), and two Data controls (Data1 and Data2) to Form1 using the following placement:

    • Put Label1 at the top and center of Form1.
    • Put Command1 under Label1 and left of Command2
    • Put Command2 under Label1 and right of Command1
    • Put List1 under Command1 and to left of List2
    • Put List2 under Command2 and to right of List1
  3. Using the following table as a guide, set the properties of the controls you added in step 2.
       Control Name   Property       New Value
       ---------------------------------------------------------------
       Label1         AutoSize       True
       Label1         Caption        Union Query Sample
       Command1       Caption        Access 2.0 Nwind Sample
       Command2       Caption        VB 3.0 Biblio(Access 1.x) Sample
       Data1          Visible        False
       Data2          Visible        False
    						
  4. Place the following code in the Command1 click event procedure of Form1:
       Sub Command1_Click ()
    
          ' Build the Union query:
          ' Select all Companies from Suppliers and Customers in Brazil:
          sql$ = "SELECT [Company Name] FROM Suppliers"
          sql$ = sql$ & " WHERE Country = 'Brazil' UNION SELECT [Company Name]"
          sql$ = sql$ & " FROM Customers WHERE Country = 'Brazil'; "
    
          ' Place the query in the recordsource and refresh the data control:
          data1.DatabaseName = "C:\ACCESS2\SAMPAPPS\NWIND.MDB"  '
          data1.RecordSource = sql$
          data1.Refresh
    
          ' Add records of the query to the list box:
          Do Until data1.Recordset.EOF
             list1.AddItem data1.Recordset("Company Name")
             data1.Recordset.MoveNext
          Loop
    
       End Sub
    						
  5. Place the following code in the Command2 Click event procedure of Form1:
       Sub Command2_Click ()
    
          ' Build the Union query:
          ' Select all publishers where publishers and titles begin with 'm':
          sql$ = "SELECT titles.pubid FROM titles "
          sql$ = sql$ & " WHERE title like 'm*' UNION SELECT publishers.pubid"
          sql$ = sql$ & " FROM publishers WHERE name like 'm*';"
    
          ' Initialize the data control:
          data2.DatabaseName = "C:\VB3\BIBLIO.MDB"  ' Change path if needed.
          data2.RecordSource = sql$
          data2.Refresh
    
          ' Add the results to the list box:
          Do Until data2.Recordset.EOF
             list2.AddItem data2.Recordset("pubid")
             data2.Recordset.MoveNext
          Loop
    
       End Sub
    						
  6. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button to get a list of all the companies and suppliers in Brazil. Then click the Command2 button to get a list of all the publisher identifications that have book titles or a publisher name that begins with the letter m.

Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB113592