VBA - Create Data Connection when CommandText is Too Long

[jintingljw]

Hi,

Usually, I use below VBA to create Data Connection with SQL database to extract data through MS Query. And it works perfectly fine.

As defined, SQL_CommandText is a string where you can set your SQL.

However, I get a huge SQL that is around 16,000 rows (40,000 characters). This VBA code doesn't works.

Assuming I don't want to modify the SQL, is there any way I can Create Data Connection when CommandText is Too Long?

Thank you for your help.

Jason



Sub ExtractData()


Dim SQL_NAME as String
Dim SQL_CONNECTION as String
Dim SQL_CommandText as String


'Refresh Connection
ConnectionName = SQL_NAME


ActiveWorkbook.Connections(ConnectionName).ODBCConnection.Connection = SQL_CONNECTION
ActiveWorkbook.Connections(ConnectionName).ODBCConnection.CommandText = SQL_CommandText
ActiveWorkbook.Connections(ConnectionName).REFRESH

End Sub