sql - 在 Excel VBA 中使用长 Access SQL 查询

Question

我有一个很长的 Access SQL 查询:

SELECT Avg(Data_transmission.Forward_app_throughput) AS AvgOfForward_app_throughput

FROM (Data_transmission LEFT JOIN Data_task_statistics ON Data_transmission.Record_index = Data_task_statistics.Record_index) LEFT JOIN Bin_data ON Data_transmission.Record_index = Bin_data.Record_index

WHERE (((Data_task_statistics.Task_type_ID)=4) AND ((Data_task_statistics.Received_Application_byte)>50000) AND ((([Data_transmission].[Forward_app_throughput]))>-1) AND ((Bin_data.Protocol_id)=61));

我正在尝试在 Excel VBA 中使用它，但我设法做到这一点的唯一方法是使用 Excel 中的“从 Microsoft Query 获取数据”功能，该功能会生成此字符串

CommandText = Array( _
        "SELECT Avg(Data_transmission.Forward_app_throughput) AS AvgOfForward_app_throughput" & Chr(13) & "" & Chr(10) & "FROM (Data_transmission LEFT JOIN Data_task_statistics ON Data_transmission.Record_index = Data_task_statistics.Rec" _
        , _
        "ord_index) LEFT JOIN Bin_data ON Data_transmission.Record_index = Bin_data.Record_index" & Chr(13) & "" & Chr(10) & "WHERE (((Data_task_statistics.Task_type_ID)=4) AND ((Data_task_statistics.Received_Application_byte)>50000) AND" _
        , _
        " ((([Data_transmission].[Forward_app_throughput]))>-1) AND ((Bin_data.Protocol_id)=61));" & Chr(13) & "" & Chr(10) & "" _
        )

如何在不使用“从 Microsoft Query 获取数据”功能的情况下获取此字符串，因为它需要很长时间并且我有几个查询

Answer 1

根据我的评论添加答案。不要尝试将数组与 CommandText 一起使用，只需传递一个 SQL 字符串即可。