Excel VBA: OLEDB Connection.CommandText Error

Question

Hello: I've had success with a long SQL (Oracle) as the Command Text in an OLEDB Data Connection that I entered manually. I then had success entering the SQL via VBA (because I need it to update based on a changing Item List), and only running the first part of the union query as a test.

However, when I made this last change adding a 2nd piece to the union query and making the strQuery command include three separate query strings, it's now throwing me an error at this line of the code below: .CommandText = StrQueryAll

StrQueryAll = StrQueryBegin & StrQueryAZ & StrQueryCO & StrQueryEnd

    With ActiveWorkbook.Connections("connection_name").OLEDBConnection
        .CommandText = StrQueryAll
        .Refresh
    End With

Below is the entire code with the actual sql removed. Is there an issue with the code for the sql too long? Or maybe another issue, but it's indirectly saying there's an error? Maybe it doesn't like strQueryAll command? I can do one big sql string with adding on strings with the continuation limitation, but thought it might be cleaner breaking up the sqls.

Thanks for your help!

Private Sub Refresh_Data()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQueryAll As String
Dim StrQueryBegin As String
Dim StrQueryAZ As String
Dim StrQueryCO As String
Dim StrQueryCA As String
Dim StrQueryEnd As String
Dim Item_List As String
Dim wksItemList As Worksheet
Dim wksDataTable As Worksheet
Dim rngItems As Range
Dim rngDatatbl As Range

Dim myMSG As String
'Dim pt As PivotTable

myString = "Refreshing Tables - Please Wait"
Application.StatusBar = myString

'With Application
    '.EnableEvents = False
    '.ScreenUpdating = False
'End With

    Set wksItemList = Worksheets("Items")
    Set rngItems = wksItemList.Range("E4")
    Set wksDataTable = Worksheets("data")
    Set rngDatatbl = wksDataTable.Range("A3")

Item_List = rngItems.Value

StrQueryBegin = "SELECT " & Chr(13) & "" & Chr(10) & _
   ..... more sql....

   .... next sql string ....
            StrQueryAZ = "    -- ****  AZ  ****" & Chr(13) & "" & Chr(10) & _
            "  select" & Chr(13) & "" & Chr(10) & _
   ..... more sql....

   .... next sql string ....
StrQueryCO = Chr(13) & "" & Chr(10) & " UNION " & Chr(13) & "" & Chr(10) & _
            "    -- ****  CO SYS  ****" & Chr(13) & "" & Chr(10) & _
            "  select " & Chr(13) & "" & Chr(10) & _
   ..... more sql....

   .... next sql string ....
StrQueryEnd = "            ) " & Chr(13) & "" & Chr(10) & _
            " ORDER BY  " & Chr(13) & "" & Chr(10) & _
            "   ITEM_NBR, WHS  " & Chr(13) & "" & Chr(10)

Debug.Print StrQueryBegin & StrQueryAZ & StrQueryCO & StrQueryEnd
StrQueryAll = StrQueryBegin & StrQueryAZ & StrQueryCO & StrQueryEnd


    With ActiveWorkbook.Connections("connection_name").OLEDBConnection
        .CommandText = StrQueryAll
        .Refresh
    End With

Answer 1

After doing more searching and tests, the problem is that the total CommandText characters has exceeded the allowable 32,767 chars.

user1274820: In a way, you were right where you needed to see the whole code. The sql is so long because of the way our tables are setup and the sql length is a necessary evil. I'll be looking into other options to run this.