How to use TOP N query from Microsoft Access 2.0 in VB

This article was previously published under Q147724
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
One of the new types of queries added to Microsoft Access version 2.0 forWindows is called a TOP N query. If you have Microsoft Access version 2.0or higher, and Microsoft Visual Basic for Windows version 4.0 and higher,you can use this type of query from Visual Basic version 4.0 programs.

This article gives a brief example that uses both the Access TOP N andTOP N PERCENT queries from Visual Basic.
MORE INFORMATION
For more information on these particular queries, please review theMicrosoft 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:
       Private Sub Command1_Click ()      Dim ds As Recordset      Dim db As database      Set db = Workspaces(0).OpenDatabase("BIBLIO.MDB")      Set ds = _         db.OpenRecordset("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:
       Private Sub Command2_Click ()      Dim ds As Recordset      Dim db As database      Set db = Workspaces(0).OpenDatabase("BIBLIO.MDB")      Set ds = db.OpenRecordset("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:
       Private Sub Command3_Click ()      Dim ds As Recordset      Dim db As database      Set db = Workspaces(0).OpenDatabase("BIBLIO.MDB")      Set ds = db.OpenRecordset("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. On the Run menu, click Start (ALT, R, S), or press the F5 key to run the program. Click each of the buttons (Command1, Command2, and Command3) in succession.
kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp
Properties

Article ID: 147724 - Last Review: 02/24/2014 08:24:12 - Revision: 2.2

  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
  • Microsoft Access 2.0 Standard Edition
  • kbnosurvey kbarchive kbhowto KB147724
Feedback