String is to short for SQL sentence

[braka]

I tried to execute SQL that was built thru some code.
SQL text is over 256 characters and this is a limit for String variable.
SQL sentence is cut on 256 character and the rest is lost.
Is there another varable that hold more then 256 that I can use.
Everything works fine if SQL is less then 256 so it can fit into sqlSQL
string (see bellow).

Dim strSQL As String
Set rstTemp = dbsTemp.OpenRecordset(strSQL, dbOpenSnapshot)

 

[Douglas J. Steele]

String variables do not have a 255 character restriction: they can hold at
least 2**16 (~64,000) characters.

How are you assigning a value to strSQL? That could be your problem.

 

[Brendan Reynolds]

I tried to execute SQL that was built thru some code.
SQL text is over 256 characters and this is a limit for String variable.
SQL sentence is cut on 256 character and the rest is lost.
Is there another varable that hold more then 256 that I can use.
Everything works fine if SQL is less then 256 so it can fit into sqlSQL
string (see bellow).

Dim strSQL As String
Set rstTemp = dbsTemp.OpenRecordset(strSQL, dbOpenSnapshot)

A variable length string variable can hold approximately 2 billion
characters, a fixed length string variable can hold approximately 64
thousand characters. See the following on-line help topic ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HV012019151033&ns=MSACCESS.DEV&lcid=2057

If you post some more details of the code that is failing, someone may be
able to see what the problem is. I'm guessing maybe there is a problem
character at the 257th position, but without more information that's just a
guess that could easily be wrong.