Data Access and Transactions

Limiting the Number of Records

If you know you’ll require only the first few records, it makes sense to limit the number of records retrieved from the database. You can do this with the MaxRecords property of the Recordset object. The default setting for this property is zero, which means that the provider normally returns all requested records. Setting it to some other value will limit the size of the rowset returned from the query. The effect is the same as if you had used the Microsoft® SQL Rowcount directive as part of your query.

The MaxRecords property is often used together with a SQL “ORDER BY” clause to produce a “Top Ten” list based on some attribute of the data. The next example returns a Recordset containing the 10 most expensive publications in the Titles table.

<%
   'NOTE: DSN-less connection.
   strCnn = "driver={SQL Server};server=(local);" &_
   "uid=sa;pwd=;database=pubs"
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.MaxRecords = 10
   rs.Open "SELECT Title, Price FROM Titles ORDER BY Price DESC",_
   strCnn, adOpenForwardOnly, adLockReadOnly, adCmdText
%>

Note You must set the MaxRecords property before the Recordset is opened. After setting MaxPrecords, the property is read-only.