Access query limitations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pjgalloway - 23 Jul 2007 23:50 GMT

I have a parent table that contains information about a sample (when, where,
why taken, etc) and a related table that has records of measurements
performed on the sample (pH, temperature, etc). My form currently displays
the parent table info with the related table info in a subform. This all
works nicely.

Now I have created a popup form to let users create their own filter to view
a subset of the samples. This popup form has a listbox that displays all of
the possible measurements that could be made and then a checkbox to specify
whether to use AND logic or OR logic between the items selected. So they can
say something like give me all of the samples where pH OR temperature are
measured or give me all of the samples where pH AND temperature are measured.
I build a string with a where clause from these selections, pass this as an
openarg to the form to be filtered, and then in the open event of that form I
set the recordsource to "Select * from qrySamples " & Me.OpenArgs.

As I read the Access specifications, I see two things I have to trap for.
First the recordsource string can't be more than 32,750 characters and second
the where clause can't have more than 99 ANDs. Are there other concerns?

The reason I ask is that when I set the recordsource to a string with 82
ANDs and approximately 27,000 characters, I get "error 3048. Can't open any
more databases". The exact same string can be used to create and run a query
just fine. When I test it as a query rather than a recordsource, I can go up
to the 99 ANDs and then I get the "query too complex" error when I try 100
ANDs.

What else should I be trapping for?

Signature

- Paula

Reply to this Message

Allen Browne - 24 Jul 2007 02:55 GMT

The most common limitation is that the number of characters returned in all
fields of a record cannot exceed 4000 or so (excluding BLOB fields such as
memos, hyperlinks, OLE objects, attachments.)

You may be able to work around the 99 phrases in a WHERE clause limit by
using the IN operator. Example:
WHERE MyField IN (1,2,3)
instead of:
WHERE (MyField = 1) OR (MyField = 2) OR (MyField = 3)

The "too many databases open" limit occurs with linked tables where you use
lots of connections. Domain aggregate functions are an obvious target; also
the number of forms and subforms open, particularly if they have heaps of
combos/list boxes as well (i.e. lots of RowSources/RecordSources being
fetched.) Code that opens recordsets and does not explicitly close them can
also contribute.

"Query too complex" is a very generic message that just means Access can't
make sense of the query. That can occur if the data types are wrong or need
typecasting, if the bracketing is wrong, if reserved names are used for
fields/parameters/tables/aliases, or if the query exceeds the specifications
(e.g. on ANDs, UNIONs, or nested levels.)

There is no way to test for every possible thing that could go wrong in a
query. There are just too many considerations, e.g.:
- using linked tables that are no longer present
- using VBA functions that are buggy, missing, or passing the wrong data
type (e.g. passing null when the argument is Integer)
- attached tables that are not JET (could be just Text, so lack any primary
keys/constraints, and may not even be consistent data types)
- pass-through queries that are evaluated by other data engines
- features that are present in one version of JET but not another.
- whether ANSI-92 mode is operational.
- whether bad field names are being handled by Name AutoCorrect.
- parameter handling.
- query properties (such as Filter and OrderBy) that are not even part of
the SQL statement yet affect the outcome.
- nested queries (dependence on lower level queries)
- embedded subquery statements that could error if they return more than 1
result.
- bugs in JET.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a parent table that contains information about a sample (when,
>where,
[quoted text clipped - 37 lines]
>
> What else should I be trapping for?

Reply to this Message

pjgalloway - 24 Jul 2007 23:38 GMT

Thanks for the comments, Allen. My issue does seem to be related to the
number of forms open with combo/listboxes. All of my code that opens
recordsets/databases always closes them. If I test with a couple fewer forms
open, and these forms are pretty heavy in combo/listboxes/subforms, I avoid
the error. At this time I can't change what is open so I will just trap for
the too many databases open error.

I guess one of the things I was wanting to know is what the limit is to the
number of characters that you can use in vba when you set a form's filter
(me.filter=strFilter). I know there is some sort of limit to what you can
set in the form properties in design view (2048 characters?), but is it the
same when you set it with code and is 2048 the correct number?

As far as your comment about using the IN operator, I do use that when the
user selects OR logic but it isn't applicable when the user selects AND logic.

Again, thanks for your time.

Signature

- Paula

> The most common limitation is that the number of characters returned in all
> fields of a record cannot exceed 4000 or so (excluding BLOB fields such as
[quoted text clipped - 79 lines]
> >
> > What else should I be trapping for?

Reply to this Message

Allen Browne - 26 Jul 2007 06:00 GMT

When you set the form's Filter property via VBA, any valid WHERE clause will
do.

I don't know of a limit, other than the 64k chars in the SQL statement.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I guess one of the things I was wanting to know is what the limit is to
> the
[quoted text clipped - 3 lines]
> the
> same when you set it with code and is 2048 the correct number?

Reply to this Message