The mdb is considerable size 70 +- mb. A complex union query was
working well, but when an additional union select... was added into
the query, selecting identical fields from a different source, it
began to have problems.
Failure was observed only in a few PC's at first. For example, in an
NT 4.0 SP6 PC, it continued to work OK. But in my Win 2k laptop, it
failed. As the union query was gradually simiplified in testing, the
failure mode first changed from "data type mismatch in criteria
expression" to a quiet failure with no output data, then to correct
function. Importing all objects into a new clean container did not
help. The workaround was to use a series of append queries into a
temporary table in lieu of union queries.
I found a couple of useful references in my search of NG's when
diagnosing the problem, and wish to thank members of this group for
many helpful postings. In this case, maybe I can benefit other
programmers by pointing a finger at an untrapped error due to resource
problems with large queries (perhaps just union queries) in Access
2000.
Please do not ask me to quantify "large." Embarrassingly large. But,
it may, in fact, be slightly PC or OS dependent.
I am not a db expert, but have several years experience programming
ms-access.