Microsoft KB Archive/183244

= ACC97: Differences Between MaxRecords and TopValues Properties =

Article ID: 183244

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q183244



Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
This article describes the differences between the MaxRecords property and the TopValues property.



MORE INFORMATION
The MaxRecords property sets or returns the maximum number of records of a query on an ODBC table and is useful in situations where limited client resources prohibit management of large numbers of records from ODBC tables. The TopValues property is useful when you want to return certain records based on a specified percentage. MaxRecords is only for ODBC data sources, not for queries on tables contained in the database, and is available in Visual Basic.

The TopValues property returns a specified number of records or a percentage of records that meet the criteria you specify in the design of a query on any table. TopValues is not available in Visual Basic but can be used on tables contained in the database or ODBC tables. To set the amount of records, TopValues requires a percent sign (%).

TopValues can return a number or a percentage, whereas MaxRecords sets or returns only a number of records.

Using the MaxRecords Property

 * 1) Open the sample database Northwind.mdb.
 * 2) Link a table from a SQL Server.

For more information on linking to SQL tables, search the Help index for "SQL Server, importing or linking ODBC data sources", and then "Import or link SQL database tables or data from other ODBC data sources", or ask the Microsoft Access Office 97 Assistant.
 * 1) Create a new query based on the linked table and include all fields.
 * 2) On the View menu, click Properties.
 * 3) Enter 5 in the MaxRecords property.
 * 4) Run the query.

Note that the query returns five records and that the records are in the order specified by the query's ORDER BY clause.

Using the TopValues Property

 * 1) Open the sample database Northwind.mdb.
 * 2) Open the Quarterly Orders query in Design view.
 * 3) On the View menu, click Properties, and set the TopValues property to 5%.
 * 4) Run the query.

Note that the query returns 5% of the record count rounded up--that is, a table containing 20 records will return 1 record and a table containing 21 records will contain 2 records.

Example of Using MaxRecords in Visual Basic for Applications
The following example assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

The following sample code is from the online Help system of Microsoft Access. To find the example, search the Help index for "maxrecords." Click MaxRecords property and click Display. In the Topics Found dialog box, click MaxRecords property (DAO) and click Display. On the DAO Reference page, click Example.

Sub MaxRecordsX

Dim dbsCurrent As Database Dim qdfPassThrough As QueryDef Dim qdfLocal As QueryDef Dim rstTemp As Recordset

' Open a database from which QueryDef objects can be created. Set dbsCurrent = OpenDatabase("DB1.mdb")

' Create a pass-through query to retrieve data from ' a Microsoft SQL Server database. Set qdfPassThrough = dbsCurrent.CreateQueryDef("")

' Set the properties of the new query, limiting the ' number of returnable records to 20. You will need to replace ' and with the UID and PWD ' of an account that has the appropriate permissions. qdfPassThrough.Connect = _ "ODBC;DATABASE=pubs;UID= ;PWD= ;DSN=Publishers" qdfPassThrough.SQL = "SELECT * FROM titles" qdfPassThrough.ReturnsRecords = True qdfPassThrough.MaxRecords = 20

Set rstTemp = qdfPassThrough.OpenRecordset

' Display results of query. Debug.Print "Query results:"

With rstTemp Do While Not .EOF Debug.Print, .Fields(0), .Fields(1) .MoveNext Loop .Close End With

dbsCurrent.Close

End Sub

Additional query words: top values max records

Keywords: kbinfo KB183244

-

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

© Microsoft Corporation. All rights reserved.