How to Query the Top N or N% records in Visual Basic

• Professional Edition of Microsoft Visual Basic for Windows, version 3.0 with Microsoft Access 2.0 and the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer
• Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

This article describes how to use the TOP predicate to return the top N number of records that meet the conditions of a query.

With the release of Microsoft Access version 2.0, the TOP predicate was introduced in Microsoft Access SQL syntax. Visual Basic version 3.0 itself will not accept the TOP predicate directly, but it is possible to use the TOP predicate with Visual Basic version 3.0 if you are using the Microsoft Jet 2.x engine that ships with Microsoft Access version 2.0.

The TOP predicate is now accepted syntax in Visual Basic version 4.0.

MORE INFORMATION

You can use the TOP predicate to return the first N records that match the conditions of a query, where N is a number less than or equal to the total records returned by the query. For example, the following query returns the first 10 records in the Titles table:

   SELECT TOP 10 Titles.* FROM Titles;

   SELECT TOP 10 PERCENT Titles.* FROM Titles;

Using TOP Predicate in VB 3.0 with Microsoft Access 1.x Database

To use the TOP predicate with Visual Basic version 3.0 in Titles, you will need Microsoft Access version 2.0 and the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer in addition to Visual Basic even if the target database is in Microsoft Access version 1.x format.

Visual Basic version 3.0 was released with the Microsoft Access version 1.x database, so TOP is not supported directly. This is demonstrated by trying to create a querydef using the TOP predicate as shown here:

   Dim mydb As Database, MyQuery As QueryDef Dim Sql$

   Sql$ = "SELECT TOP 10 Titles.* FROM Titles;"
   Set mydb = OpenDatabase("C:\VB\BIBLIO.MDB")
   Set MyQuery = mydb.CreateQueryDef("TopQuery", Sql$)

   Invalid SQL Statement: expected 'DELETE', 'INSERT', 'PROCEDURE',
      'SELECT', or 'UPDATE'

The following code works with Visual Basic version 3.0 and a Microsoft Access version 1.x database:

   Dim Sql$
   Dim MyDynaset as Dynaset
   Dim MySnapshot as Snapshot
   Sql$ = "SELECT TOP 10 Titles.* FROM Titles;"

   data1.recordSource = Sql$
   ' -or-
   MyDynaset = MyDb.CreateDynaset( Sql$ )
   ' -or-
   MySnapshot = MyDb.CreateSnapshot( Sql$ )

Using TOP Predicate in VB 3.0 with Microsoft Access 2.X Database

You can use the TOP predicate with Visual Basic version 3.0 and databases in Microsoft Access version 2.x format. This includes use within:

• A QueryDef object.
• A Snapshot object.
• A Dynaset object.
• A Data Control.

   Dim mydb As Database, MyQuery As QueryDef
   Dim Sql$

   Sql$ = "SELECT TOP 10 Titles.* FROM Titles;"
   Set mydb = OpenDatabase("C:\VB\BIBLIO.MDB")
   Set MyQuery = mydb.CreateQueryDef("TopQuery", Sql$)

Using TOP Predicate in VB 4.0 with Microsoft Access 2.x or 3.x Database

Visual Basic version 4.0 recognizes the TOP predicate as valid SQL Syntax. Although the TOP predicate itself is not listed as a index entry in the Visual Basic version 4.0 Help menu, you can use the Find feature found under Windows 95 and Windows NT version 3.51 to create a search index and query on TOP, or search for this Help topic:

   ALL predicate

Step-by-Step Example

The following example shows how to use the TOP predicate with a Querydef using the new syntax for Data Microsoft Access Objects within Visual Basic version 4.0.

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

2. Add a command button (Command1) to the form.

3. Add the following code to the click event for Command1:

      Private Sub Command1_Click()
         Dim MyDB As Database, MyQuery As QueryDef, MySet As Recordset
         Dim Sql$, QueryDefName$
         QueryDefName$ = "TopTitles"
         Sql$ = "SELECT TOP 10 Titles.* FROM Titles;"
   
         ' Open Database, and create querydef:
         Set MyDB = Workspaces(0).OpenDatabase("C:\VB4\BIBLIO.MDB")
         Set MyQuery = MyDB.CreateQueryDef(QueryDefName$, Sql$)
   
         ' Create Recordset from query and load list with results:
         Set MySet = MyDB.OpenRecordset(QueryDefName$)
         MySet.MoveFirst
         While MySet.EOF = False
            debug.print MySet(0)
            MySet.MoveNext
         Wend
   
         ' Clean up -- close objects and remove querydef:
         MyDB.QueryDefs.Delete QueryDefName$
         MyQuery.Close
         MySet.Close
         MyDB.Close
         Set MyQuery = Nothing
         Set MySet = Nothing
         Set MyDB = Nothing
      End Sub
   
   

4. Run the program and press the Command1 button.

5. Change the line defining SQL$ to be:

   Sql$ = "SELECT TOP 10 PERCENT Titles.* FROM Titles;"

6. Run the program again, and notice the difference in results.