VBA - Create Data Connection when CommandText is Too Long

jintingljw

New Member
Joined
Apr 12, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,604
Members
412,330
Latest member
carlosjw
Top