max size of sql query

  • Thread starter Thread starter mike
  • Start date Start date
M

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
 
One trick is to use single letter table aliases to reduce the number of
characters in your string.
e.g. MyTableName T
 
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
 
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 "
....
 
Back
Top