PDA

View Full Version : Trying to capture a very long SQL string in a VBA string



tcarnahan
11-25-2013, 08:25 PM
I am trying to pass a very long SQL query to a VBA string, but the SQL seems to be getting truncated. Is there a long version of a VBA string?

SamT
11-26-2013, 05:34 AM
Break the string into logical parts and concatenate them at the end


SQLPart1 = "ABC"
SQLPart2 = "DEF"

SQLString = SQLPart1 & SQLPart2

Giving the Part variables meaningful names will help understanding the code at a later date.


sqlConnection = ?
sqlFrom = ?
sqlNamefield = ?
sqlParam1 = ?

SQLString = sqlConnection & sqlFrom & sqlNameField & sqlParam1

tcarnahan
11-26-2013, 08:20 AM
Thanks Sam!

I am trying to store the SQL in a "template" PT query that I have saved because it has so many parts (unions, pivot, etc.), so what I would have to do is use:



strSql1 = LEFT(qdf.SQL, x) 'where x is the number of characters
strSql2 = MID(qdf.SQL, y,z) ' where y is "x+1" and z is so many characters
strSql3 = Right(qdf.SQL, LEN(qdf.SQL) - y + z)

strSqlNEW = strSql1 & strSql2 & strSql3



I thought a "string" data type could take 2 billion characters. Was I mistaken? If it is only 255, then even the code (above) would not work when I concatenate the 3 parts. My SQL is about 3,000 characters.

SamT
11-28-2013, 09:06 AM
My main computer is down, so I'm pulling this out of my.. Well you get the picture.

I think that the issue is that a variable Assignment string in VBA has a smaller limit than the String Type. :dunno:

So strResult = str1 & str2 & strEtc would have a smaller limit in the number of characters in "str1 & str2 & strEtc" than the length of the actual concatenated String in strResult. Again :dunno:

IOW, if str1, str2, and strEtc were each assigned 250 Characters, strResult could hold 750 characters. but the assignment itself, "str1 & str2 & strEtc," is still limited to 255? characters including spaces, ampersands, and Function names and parameters. & Left(str1, 3) = 15 characters. :dunno:

I have never run into the problem myself, because I follow the KISASE principle, (Keep It SSimple And Self explanatory Stupid.) :)

Short String Assignment

strResult = "From ABC _
Where XYZ <3 _
sqlEtc"

Long Assignment

Dim sqlWhere As String
Dim sqlFrom As String
Dim sqlParam As String
Dim sqlEtc as String
'assign values

sqlResult = sqlFrom & sqlWhere & sqlParam & sqlEtc