ACC2002: How Access Projects (ADPs) Use the DefaultMaxRecords Setting and the T-SQL Set Rowcount Statement (283200)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q283200
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SUMMARY

When you create an application, it is good design practice to limit the number of records brought over a network from a database server to the client computers. When you use an Access 2002 project (.adp), Access uses several methods behind the scenes to control the number of records processed by SQL Server, including SQL Server 2000 Desktop Edition, to avoid unnecessary network traffic. This article discusses some of the methods that Access uses for this purpose and some of the objects and actions that can be affected under different scenarios.

MORE INFORMATION

The MaxRecords and DefaultMaxRecords Settings

Views, stored procedures, functions, forms, and data access pages in an Access project can all have an associated MaxRecords property. The value of an object's MaxRecords setting is used by Access to limit the number of records processed by SQL Server.

If the MaxRecords value for a particular object has not been specified, Access uses the DefaultMaxRecords property. This value is set to 10,000 records when you are creating a new file, but you can change the value to any valid integer. To modify this setting, follow these steps:
  1. Open the Access project (.adp) that you want to change the setting for.
  2. On the Tools menu, click Options.
  3. Click the Advanced tab.
  4. Set the DefaultMaxRecords option under the Client-server settings box to a higher number, or set it to 0 to not limit the number of records affected. The setting you choose affects only the Access project that is currently open.
  5. Click OK to close the Options dialog box.
When you change the value of the DefaultMaxRecords property, all existing views, stored procedures, and functions that do not have an individual MaxRecords value set are affected by the new default setting. Forms and pages, however, are not changed automatically. When forms and pages are created, the individual MaxRecords property for each object is set to the value of DefaultMaxRecords.

To modify an object's individual MaxRecords property, follow these steps.

For tables, views, functions, and stored procedures:
  1. Open the object in Datasheet view.
  2. On the Records menu, click Maximum Records.
  3. In the Maximum Record Count dialog box, enter the maximum number of records that you want to have displayed.
  4. Click OK.
NOTE: This setting will only persist if you are connected to SQL Server 2000 or later. Earlier versions of SQL Server do not allow for extended object properties; therefore, Access cannot save this setting. For data access pages:
  1. In Design view of the page, right-click the page, and then click Page Properties.
  2. Click the Data tab, and then set the MaxRecords property to the value that you want.
  3. Save the page.
For forms:

You can follow the same steps as for tables in Form view of a form. In addition, you can also set the MaxRecords property in Design view of a form as follows:
  1. In Design view of the form, right-click the form, and then click Properties to display a property sheet.
  2. On the Edit menu, click Select Form to ensure that you are viewing the property sheet for the form.
  3. In the property sheet for the form, click the Data tab, and then set the MaxRecords property to the value that you want.
  4. Save the form.

The SET ROWCOUNT Statement

SET ROWCOUNT is a Transact-SQL statement that is used by SQL Server to limit the number of records affected when you retrieve or manipulate data. When sent to SQL server as a command of its own, the setting applies to the currently running connection, or process, and does not affect other concurrently running processes. This is the main mechanism Access uses behind the scenes to implement the DefaultMaxRecords setting for the project.

Before sending any command to manipulate or retrieve records from SQL server, Access sends a SET ROWCOUNT X statement to limit (or not limit) the number of records that are returned. If Access determines that the current SET ROWCOUNT setting for the connection is already at the desired level, it may not send another statement. The value of X used in the SET ROWCOUNT statement that is sent to SQL Server is determined by several factors that can vary, as follows.

When Opening a Table, View, or Function

Access first checks for an individual MaxRecords extended property value for the object being opened. If none has been specifically set, Access uses the DefaultMaxRecords value.

When Opening a Stored Procedure

A stored procedure is affected just like any other query with one important difference: you can specify your own SET ROWCOUNT statement in the stored procedure. When you do this, your setting can override the MaxRecords property, but only in the context of the stored procedure. For example, when you try to execute a stored procedure with an embedded SET ROWCOUNT statement, the following happens:

  1. Access will send a SET ROWCOUNT statement based on the MaxRecords property as described earlier in this article.
  2. Access will send a command to execute the stored procedure.
  3. The stored procedure sends its own SET ROWCOUNT statement.
  4. The stored procedure continues processing commands and is bound only by the SET ROWCOUNT value in step 3.
  5. When the stored procedure finishes processing, any records to be returned are given over to SQL Server.
  6. SQL Server applies the connection's SET ROWCOUNT value from step 1 and passes any remaining records to Access.

If the sole purpose of the stored procedure is to run a select query, it would be pointless to have a SET ROWCOUNT statement within the stored procedure that is greater than the object's MaxRecords value because the MaxRecords value will still limit the number of records returned. However, this technique is especially useful for action queries when you may want to update an unlimited number of records without being affected by the SET ROWCOUNT value of the connection.
For additional information about how action queries are affected by the DefaultMaxRecords setting and the SQL SET ROWCOUNT statement, click the article number below to view the article in the Microsoft Knowledge Base:

287429 ACC2002: Action Query Does Not Affect All Valid Records in an Access Project

When Opening a Form or Data Access Page

Access uses the individual MaxRecords property value for the form. All forms and pages in Access 2002 have an individual MaxRecords property set to DefaultMaxRecords when creating the object. Access does not consider the value of any MaxRecords property associated with the form's underlying data source, such as a table or a query.

When Opening a Report

Access sends a SET ROWCOUNT 0 statement to allow for an unlimited number of records to be returned. There is no individual MaxRecords property for reports. Access also does not consider the value of any MaxRecords property associated with the report's underlying data source or the DefaultMaxRecords property.

When Using ADO to Execute a Stored Procedure or to Open a Recordset Against a Table or Query

Access sends a SET ROWCOUNT 0 statement to allow for an unlimited number of records to be returned or processed. Access does not consider the value of any MaxRecords property associated with the underlying data source or the DefaultMaxRecords property.

REFERENCES

For more information about SQL Server programming commands such as SET ROWCOUNT, visit the Microsoft SQL Server Developer Center at the following Microsoft Web site:

http://msdn.microsoft.com/sqlserver/

Modification Type:MajorLast Reviewed:11/6/2003
Keywords:AccessCS KbClientServer kbdta kbhowto KB283200
©2003 Microsoft Corporation. All rights reserved.