VB3 How to Use TOP N Query from Microsoft Access 2.0 in VB

ID: Q113956

3.00 WINDOWS

The information in this article applies to:

- Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

One of the new types of queries added to Microsoft Access version 2.0 is called a TOP N query. If you have the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer, Microsoft Access version 2.0, and Visual Basic version 3.0, you can use this type of query from Visual Basic version 3.0 programs. This article gives a brief example that uses both the Microsoft Access TOP N and TOP N PERCENT queries from Visual Basic.

MORE INFORMATION

For more information on these particular queries, please review the Microsoft Access documentation.

Step-by-Step Example

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

2. Add three Labels, three List Box controls, and three Command buttons

   to Form1.

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         Caption        Selecting the first 10 titles from the
                                 Titles table according to title field.
   Label2         Caption        Selecting the first 5 titles from the
                                 Titles table according to date published.
   Label3         Caption        Selecting the first 7 years of percent
                                 published from the Titles table according
                                 to the year published field.
   Command1       Caption        First 10 Title names
   Command2       Caption        First 5 titles dates published
   Command3       Caption        First 7 years percent published

4. Place the following code in the Command1 Click event procedure:

   Sub Command1_Click ()
      Dim ds As Dynaset
      Dim db As database

      Set db = OpenDatabase("BIBLIO.MDB")
      ' Enter the following two lines as one, single line:
      Set ds =
         db.CreateDynaset("select top 5 title from titles order by title")

      Do Until ds.EOF
         list1.AddItem "" & ds("title")
         ds.MoveNext
      Loop
      ds.Close
      db.Close
   End Sub

5. Place the following code in the Command2 Click event procedure:

   Sub Command2_Click ()
      Dim ds As Dynaset
      Dim db As database

      Set db = OpenDatabase("BIBLIO.MDB")
      ' Enter the following two lines as one, single line:
      Set ds = db.CreateDynaset("select top 5 [year published],
         title from titles order by [year published]")

      Do Until ds.EOF
         list2.AddItem "" & ds("title")
         ds.MoveNext
      Loop
      ds.Close
      db.Close
   End Sub

6. Place the following code in the Command3 Click event procedure:

   Sub Command3_Click ()
      Dim ds As Dynaset
      Dim db As database

      Set db = OpenDatabase("BIBLIO.MDB")
      ' Enter the following two lines as one, single line:
      Set ds = db.CreateDynaset("select top 7 Percent [year published],
         title from titles order by [year published]")

      Do Until ds.EOF
         list3.AddItem "" & ds("title")
         ds.MoveNext
      Loop
      ds.Close
      db.Close
   End Sub

7. From the Run menu, choose Start (ALT, R, S), or press the F5 key

   to run the program. Click each of the buttons (Command1, Command2, and
   Command3) in succession.

KBCategory: KBSubcategory: APrgDataAcc Additional reference words: 3.00

Keywords          : kbcode APrgDataAcc 
Version           : 3.00
Platform          : WINDOWS

Last Reviewed: May 22, 1998