Show All

Set the number of records retrieved in an Access project (ADP)

Note  The information in this topic applies only to a Microsoft Access project (.adp).

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

1. Open a datasheet or form in browse mode.
2. Do one of the following:
   • Select Maximum Records from the Records menu.
   • Click maximum record limit to the right of the navigation buttons.
3. In the Set Maximum Record Count dialog box, type in the maximum number of records in the text box. Typing 0 means there is no Limit and all records are retrieved.

   Note  Be careful when removing the limit— it could decrease the performance of your Access project— especially if the recordset of the form or datasheet is large. However, if you are exporting data to another Access project, or running an append or make-table query to copy data, you may want to set the maximum record limit to 0 if you need to retrieve all records.

   Tip

   If you set the maximum record limit to a high value, and browsing your data takes a long time, you can cancel the operation by clicking Cancel Query or pressing CTRL+BREAK.

Notes

• If you are using a form, Microsoft Access saves the setting with the form if you adjust the setting. The default maximum record limit is 10,000 for all new forms and datasheets. To change the default limitation the Tools menu, click Options, click the Advanced Tab, and change the default number in the Default max records text box. Entering a value of 0 means that there is no limit by default. You can also set the default value for an individual form by using the MaxRecords property.
• Access displays the maximum record limit button by default, but you can hide this button by setting the MaxRecButton property of a form or datasheet to False. This property has no effect on the menu command.
• Access loads records from the SQL Server database asynchronously, which means you can interact with Access before all the records are retrieved. Access displays the number of records it is loading from the SQL Server database next to the maximum record limit button.