Microsoft KB Archive/129744

{| = How to Query the Top N or N% records in Visual Basic =
 * width="100%"|

Last reviewed: April 16, 1996

Article ID: Q129744 The information in this article applies to:
 * 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; 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 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$) 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 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.

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. For the QueryDef object, parsing and validation is bypassed in Visual Basic and handled by the Jet database engine itself. 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 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 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. 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 notice the difference in results.
 * }