ACC97: Differences Between MaxRecords and TopValues Properties

• Microsoft Access 97

SUMMARY

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

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.

Examples of Using the MaxRecords and TopValues Properties

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.



3. Create a new query based on the linked table and include all fields.



4. On the View menu, click Properties.



5. Enter 5 in the MaxRecords property.



6. 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

   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.
      qdfPassThrough.Connect = _
         "ODBC;DATABASE=pubs;UID=sa;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 : QryProp
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbinfo