ACC: "Out of Memory" or "Query Too Complex" with Query/Report (103429)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q103429
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
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.
| Modification Type: | Major | Last Reviewed: | 5/6/2003 |
|---|
| Keywords: | kberrmsg kbprb kbusage KB103429 |
|---|
|