ADO CommandText max length

VB6 and MS Access

When I create a command such as

Dim comUpdate As ADODB.Command
Set comUpdate = New ADODB.Command

With fgdWindow(intWdwNum)
strSQL = "UPDATE Windows SET " & _
"Description = '" & .TextMatrix(1, 1) & "', " & _
"JambDepth = '" & .TextMatrix(1, 4) & "', " & _
"ExtSurround = '" & .TextMatrix(1, 5) & "', " & _
"IntSurround = '" & .TextMatrix(1, 6) & "', " & _
"GlassPanes = '" & .TextMatrix(1, 7) & "', " & _
"GlassOption = '" & .TextMatrix(1, 8) & "', " & _
"GlassTint = '" & .TextMatrix(1, 9) & "', " & _
"Quantity = " & .TextMatrix(1, 10) & ", " & _
"Cost = " & CCur(fgdWindowA(intWdwNum).TextMatrix(1, 11)) & ", " & _
"Price = " & CCur(fgdWindowA(intWdwNum).TextMatrix(1, 12)) & _
" WHERE (QuoteID = " & mintQuoteNum & ") AND (WindowNum = " & _
intWdwNum & ")"
End With

With comUpdate
.ActiveConnection = cnWindowWonders
.CommandText = strSQL
.Execute
End With

Set comUpdate = Nothing

Is there a maximum length CommandText can be?

As I recall, the maximum length varies depending on the provider. For example, IBM DB2 has a limitation of 65535 bytes (ref: https://aurora.vcu.edu/db2help/db2s0/frame3.htm#db2s0417).

I vaguely have the 8000 character limit in my head as a general rule, but that might be getting confused with the 4000/8000 limit on varchar(n) datatypes in Oracle and/or SQL Server.

Your current string query is not too long, I have written much larger for Access. You might have to watch out for the number of line breaks in your string, there IS a limit on those. Whenever I have a massize string declaration that might take up too many line breaks, I split up the string, something like:



sString as string

sString = "LONG STRING WITH LINE BREAKS"
sString = sString & "more info"