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

Last reviewed: May 7, 1997
Article ID: Q103429

The information in this article applies to:

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
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.