ACC: "Out of Memory" or "Query Too Complex" with Query/Report

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run a query, a form, or a report based on a query, you may receive an "Out of Memory" or "Query too Complex" error message.

CAUSE

In Microsoft Access 1.x and 2.0, queries must compile within a 64 kilobyte (K) segment. If you generate a query that is greater than 64K, you may see either the "Query too Complex," or the "Out of Memory" error message. In Microsoft Access 7.0 and 97, the 64K limit is replaced by a dynamic limit which offers much more room; however, it is still possible to make a query too complex.

Reports create temporary queries for each section of the report, including the report header, page header, group header, detail section, group footer, page footer, and report footer. All of the temporary queries for each report are combined into a segmented virtual table (SVT). The final output must be compiled within the 64K segment limit.

Similar 64K limits are used to compile and store all of the expressions from page to page when the report is being processed or to store the unbound controls or label information. If any of these segments exceed the limit, controls on the report may start displaying the "#Name?" error message.

RESOLUTION

These error messages are related to the complexity of the underlying query or the report itself. To reduce the complexity of your queries or report, try the following suggestions:

• Shorten table names, column names, and control names. Reducing a 30-character name to the minimum length may help.



• Reduce the number of text fields on the report.



• Avoid extra overhead by removing any fields in the underlying query that are not used in the final output for the report.



• Reduce expressions in underlying queries. Reducing space used for expressions in the select list helps to avoid these errors. If possible, place the expressions directly in the report.



• Move complex expressions to a user-defined function that does all the evaluating.



• Avoid stacked query objects, such as situations in which Query1 is used to pull data from Table2 and Query2 filters the data. Pulling information together in one query is preferable to having multiple queries, each doing portions of the task.



• Avoid basing main reports and their subreports on the same queries. Look for stacked query objects and for tables that are unnecessary to the subset.



• Use subreports to break up a complex report into several less complicated reports.



• If the report is based on a query, build a temporary table from the query to base the report on, instead of basing it on the query. Create a make-table query that includes all the fields from the original query to build the temporary table.



• Shorten SQL statements by using the Alias property for the field list. To do so, on the View menu, click Properties. Click on any field list and change the Alias property to something shorter to shorten the SQL statement.

Keywords          : kberrmsg kbusage RptOthr 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: March 26, 1999