PRB: MaxRecords Property Is Not Used in Access Queries with ADO (186267)

The information in this article applies to:

ActiveX Data Objects (ADO) 1.0
ActiveX Data Objects (ADO) 1.5
ActiveX Data Objects (ADO) 2.0
ActiveX Data Objects (ADO) 2.1 SP2
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
ActiveX Data Objects (ADO) 2.7

This article was previously published under Q186267

SYMPTOMS

Using the MaxRecords property of an ADO Recordset object does not affect the number of records returned in queries against Microsoft Access databases.

CAUSE

The MaxRecords property depends on functionality exposed by the underlying OLE DB provider or ODBC driver to limit the number of rows returned by the query. This functionality is optional for ODBC drivers and OLE DB providers. The Microsoft Access ODBC driver and Jet OLEDB provider do not expose this functionality. This behavior may occur for other OLE DB providers and ODBC drivers as well.

RESOLUTION

If you want to limit the number of records returned in a query against a Microsoft Access database, use the TOP syntax in the query string rather than the Recordset's MaxRecords property.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

Instead of using the following code:

   strSQL = "SELECT * FROM Customers"
      rsCustomers.MaxRecords = 10
      rsCustomers.Open strSQL, cnNWind, adOpenStatic
      MsgBox rsCustomers.RecordCount & " records returned using MaxRecords."

Use this code:

   strSQL = "SELECT TOP 10 * FROM Customers"
   rsCustomers.Open strSQL, cnNWind, adOpenStatic
   MsgBox rsCustomers.RecordCount & " records returned using TOP syntax."

With the first snippet of code, you should see that the MaxRecords property does not affect the number of records returned by the query. The second snippet of code should return only ten records.

Modification Type:	Minor	Last Reviewed:	3/2/2005
Keywords:	kbDatabase kbDCOM kbDSupport kbJET kbMDACNoSweep kbprb KB186267