max size of sql query

[mike]

When I have some vb code create what could be a VERY large sql statement, I
can't get past approximatly 1816 charactors. Access tells me that the query
is too complex, even though its very simple, just long.
Does anyone know what excatly is the maximum length a sql statement can be?
I also have the same problem when passing a where critieria to a report, I
can't get past 400 or so charactors.

- Mike

 

[Joe Fallon]

One trick is to use single letter table aliases to reduce the number of
characters in your string.
e.g. MyTableName T

 

[mike]

What I was doing to was passing a large not equal too (<>) list.
I got around the problem by using the (NOT IN) function and therefore
passing a small and simpler list of excluded items.

Although I'm still wondering how i could max out a reports where crtitieria
and a query criteria with only 8000 charactors. I thought the max was 32k
charactors.

Thanks
Mike

 

[John Spencer (MVP)]

Access 97 (and probably later) Query Specifications (on line help)

The max for the entire SQL statement is 64K.

The max for any clause in a Where statement is 1K (?? 2 K in Access 2K and up??)

If you are building the SQL statement in VBA then you can run into a limit on
the number of characters you can add in one line.

StrSQL = ...whole lot of characters ....

You can get around this by using the technique

StrSQL = StrSQL & " a subset of the characters "
StrSQL = StrSQL & " another subset of the characters "
....