String Size Limit

[Always_Learning]

Hi There,

I am creating an Sql select statement using a string variable.

I am receiving an error that there is something wrong with my select statement. When I take a look at the string it's cutting off at 255 characters.
Is there a way to get over that limit?

Thanks for the help.

Best Regards,

 

[MarkK]

How are you doing this?

 

[Minty]

Have you declared the variable as String, and how are you determining that it is cutting off?
I regularly create SQL strings that are 300-400 characters long without any issue. I believe the limit is around 8000 characters.
Have you added a Debug.Print YourSqlString to see what it is generating in the immediate window?

 

[The_Doc_Man]

I believe the limit is around 8000 characters.

Actually, whatever it takes for the string's internal descriptor plus the string and it all has to fit in one disk buffer. For access, that is 8192 bytes. I'm not gonna bet money on it, but probably for Access 32-bit flavors, that would be an 8 byte descriptor leaving 8184 bytes for the longest string. I'm making no bets at all for the 64-bit flavor because I have no clue as to what it does to the descriptor, but it will certainly exceed 8 bytes. It would have to be at least 12 and I wouldn't bet against 16.

 

[sneuberg]

This specification says for strings:

The zero length empty string and all possible character
sequences using characters from the implementation
dependent character set. There MAY be an implementation
defined limit to the length of such sequences but the limit
SHOULD be no smaller than (216 – 1) characters.

Note: (216 – 1) was 2 to the sixteen power minus one or 65535 in the PDF.

This Access 2016 Specification also says:

Number of characters in an SQL statement
Approximately 64,000*

 

[The_Doc_Man]

Sounds like a 16-bit size counter, sneuberg. If so, then the 255-byte cutoff being observed must be going through some intermediate that has this limit. This isn't the first time I recall hearing about truncation, though.

Always_Learning: Can you post a copy of the code snippet that you use to construct this string? If you can do so, also include a copy of the string variable's declaration.

 

[gemma-the-husky]

try breaking the string into multiple statements

strg= strg & newbit
strg= strg & newbit
strg= strg & newbit

etc

 

[The_Doc_Man]

Take a look at this article from Allen Browne, which might be relevant. In it, he lists several possible causes of truncation of longer strings to become 255 bytes. See if any of those reasons apply to what you are doing. You aren't necessarily talking about fields being truncated, but his article contains links to non-field cases.

http://allenbrowne.com/ser-63.html