I have a string right now which needs to hold about 1000 characters or so. But, VBA limits it to only 255 characters. What can I do to make it hold more? (the string holds a SQL string which then gets passed to: db.openrecordset(sqlstr, dbopensnapshot)
Strictly speaking, I don't recall that VBA limits string size to 255. I thought it was 65535 or something like that. But some of the interfaces that pass strings DO limit string size. If you could create an SQL query separately and THEN open it by name, that might be one workaround. I think you can build a long query, you just can't pass one.
Can you post your entire code, including whatever code calls the code in question, if pertinent to the problem? I'd really love to see this resolved for you.
As mentioned earlier, some methods may truncate to first 255 characters, but the VBA string is definitely capable of holding much more than 255. This says that string can hold up to 2 billion characters (e.g. limited by a Long Integer, presumably.)
But! Text data type you used in table design only can hold up to 255 character. Using Memo will allow up to what I assume is limited by Integer (65,535 characters). Thanks, Allen..
Therefore, the effective limit is 65,535 characters per memo column, though VBA is perfectly capable of holding much more than that.
Also, I suspect that Immediate Windows will also truncate string if one used debug.print, but will have to test this.
You would be better off by testing something like this:
Pseduocode:
Code:
Dim stringer As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset ("SomefancypantqueryReturningAColumnOfMemo")
If Not rst.BOF And Not rst.EOF Then
stringer=rst.Fields(0)
Debug.Print Len(stringer) 'How long is this string?
Debug.print stringer 'See if we can read the whole string
End If
Even though the MsgBox will also truncate real long strings, it should be good enough to view most SQL Statements.
.[/quote]
I always use message boxes to view my SQL strings, very useful and I haven't had any problems yet.
[COLOR=Red][B]Good Tip:[/B][/COLOR]
Set up a module (I usually name it basConstants) and add the following constant:
[code]Public Const conShowMessage As Boolean = False
'''' Usage ''' If conShowMessage Then MsgBox " >>> " & strBuildSQL
Now whenever you want to use a message box to show information about how your programming is working, so you can see SQL strings and the like, present the message box in the "If" statement as shown above:
Code:
If conShowMessage Then MsgBox " >>> " & strBuildNumb
then all you have to do is opened the module, change the false to true or vice versa and this will switch on, or off, the message boxes you have strategically placed in your code for debugging.
If I recall (and don't quote me on it) the MsgBox can display 1024 Characters before things get truncated. I have had extensive SQL strings truncated within the MsgBox but for the most part....it's fine.
In Excel VBA, the immediate window is truncating a string variable containing more than 255 characters, to the first 255 characters. A Msgbox however, shows the entire string (which is more than 255 characters).
