Microsoft KB Archive/168241

= How To Query the Top N or N% Records in Visual Basic =

Article ID: 168241

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * 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 Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition

-



This article was previously published under Q168241



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 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.</li>  Change the line defining SQL$ to be: Sql$ = "SELECT TOP 10 PERCENT Titles.* FROM Titles;" </li> Run the program again, and note the difference in results.</li></ol>

Additional query words: kbVBp400 kbVBp500 kbVBp300 kbVPp kbWinOS98 kbVBp600 kbNoKeyWord kbGrpVBDB

Keywords: kbhowto KB168241

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.