ACC97: Differences Between MaxRecords and TopValues Properties

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

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
      ' <username> and <strong password> with the UID and PWD
      ' of an account that has the appropriate permissions.
      qdfPassThrough.Connect = _
         "ODBC;DATABASE=pubs;UID=<username>;PWD=<strong password>;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