HOWTO: Query the Top N or N% Records in Visual Basic

ID: Q168241

The information in this article applies to:

Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
Microsoft Visual Basic Standard and Professional Editions for Windows, versions 3.0, 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 and higher.

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;

You can use TOP with the PERCENT keyword to return the first N% records matching the conditions of a query, where N% is between 0% and 100%. For example, the following query returns the first 10% of all the records in the Titles table:


   SELECT TOP 10 PERCENT Titles.* FROM Titles;

The text below describes how to use the TOP predicate with the different versions of Visual Basic and Microsoft Access database formats.

Using TOP Predicate in Visual Basic 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$)

The last line of this code sample will generate the following error message:

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

This error message occurs because Visual Basic itself is trying to parse the SQL syntax given in the CreateQueryDef object in the context of the version of the database being used. This parsing or validation of the SQL Syntax, however, is not performed when setting a Data Control RecordSource property, a Dynaset object, or a Snapshot object for a 1.x database.

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

While Visual Basic itself still does not recognize the syntax, the Microsoft Jet 2.0 database engine takes over the parsing and validation, even for the QueryDef object. Therefore, Visual Basic can use the following code without producing an error:


   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 Visual Basic 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, Windows 98, and Windows NT version 3.51 to create a search index and query on TOP, or search for this Help topic:


   ALL predicate

This Help topic discusses the proper syntax for the ALL, DISTINCT, DISTINCTROW, and TOP predicates.

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+:

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

Establish a reference to the Microsoft DAO 3.x Object Library using the Tools menu in Visual Basic 4.0 or the Project menu in Visual Basic 5.0 and higher.

Add a Command Button (Command1) to the form.

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

Run the program and press the Command1 button.

Change the line defining SQL$ to be:


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

Run the program again, and note the difference in results.

Additional query words: kbVBp400 kbVBp500 kbVBp300 kbVPp kbWinOS98 kbVBp600 kbNoKeyWord

Keywords :
Version :
Platform : WINDOWS
Issue type : kbhowto