Use long Access SQL query in Excel VBA

Question

I have a long Access SQL query:

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));

I am trying to use it within Excel VBA, but I the only way I managed to do that is by using the "Get Data from Microsoft Query" feature in Excel, which produces this string

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) & "" _
        )

How can I get this string without using "Get Data from Microsoft Query" feature, because it takes a long time and I have several queries

Are you asking how to query a database using sql from VBA or is there some other issue? — ChipsLetten
I know how to do that, I am asking how to divide the long SQL query above to be used as an array for the CommandText — amamdouh
Why do you need it as an array? Shouldn't it just be a string of SQL? — ChipsLetten

ChipsLetten ChipsLetten · Accepted Answer · 2015-06-14T12:23:01

Adding an answer based on my comment. Don't try to use an array with CommandText, just pass it a string of SQL.

Use long Access SQL query in Excel VBA

1 Answers