Set the number of records retrieved in an Access project

To improve performance, you can set the maximum number of records to download from a SQL Server database when you are working with data from a form or datasheet in a Microsoft Access project.

1. Open a datasheet or form in navigation mode.

2. Perform one of the following actions:

Select Maximum records from the Records menu.

Click Maximum record limit on the right side of the navigation button.

3. In the set maximum number of records dialog box, type the maximum number of records in the text box. Typing 0 means there is no limit and all records will be retrieved.

Note: You should be cautious when deleting restrictions, especially when the recordset for a form or datasheet is large, because it lowers the performance of your Access project. However, if you want to export data to another Access project, or run an append or make-table query to copy data, you can limit the maximum number of records to 0 if you want to retrieve all records.

Note that if the maximum number of record limits is set to a high value and the browsing data takes a long time, you can click Cancel query or press Ctrl+break to cancel the operation.

If you are using a form, Microsoft Access saves the settings value with the form when you adjust the setting value. The default maximum record limit for all new forms and datasheets is 10,000, but the default limit value can be changed. To change the default limit, click Options on the Tools menu, click the Advanced tab, and change the default value in the default Maximum records text box. Entering 0 means that there are no restrictions by default. You can also set the default value for an individual form by using the Max record property.

By default, Access displays the maximum record limit button. However, you can hide the button by setting the Maximum Record button property of a form or datasheet to No. This property has no effect on menu commands.

Access asynchronously loads records from a SQL Server database, which means that you can interact with access before all records are retrieved. Access displays the number of records loaded from the SQL Server database next to the maximum Record button.