Excel VBA: OLEDB Connection.CommandText Error

Question

你好：我手动输入的OLEDB数据连接中的命令文本很成功。然后，我成功地通过VBA进入SQL（因为我需要根据不断变化的项目列表进行更新），并且仅运行联合查询的第一部分作为测试。

但是，当我进行最后一次更改时，在联合查询中添加了第二件，并制作Strquery命令包括三个单独的查询字符串，现在在下面的代码的这一行中给我一个错误：.commandText = Strqueryall

StrQueryAll = StrQueryBegin & StrQueryAZ & StrQueryCO & StrQueryEnd
    With ActiveWorkbook.Connections("connection_name").OLEDBConnection
        .CommandText = StrQueryAll
        .Refresh
    End With

以下是整个代码，实际上已删除了实际的SQL。SQL的代码是否有问题？还是另一个问题，但是间接地说有错误？也许它不喜欢Strqueryall命令？我可以使用一个大SQL字符串，并在延续限制下添加字符串，但认为可以更干净地分解SQL。

感谢您的帮助！

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

进行更多搜索和测试后，问题在于，总命令字符超过了允许的32,767个字符。

user1274820：在某种程度上，您是需要查看整个代码的地方。SQL太长了，因为我们的桌子是设置的方式，而SQL长度是必要的邪恶。我将研究其他选项来运行此操作。