VBA - too many line continuations

[albertan]

I have a very long SQL statement which I'm trying to put into my VBA code.

In my VBA I have the following format for SQL:

With ActiveWorkbook.Connections ("Database").OLEDBConnection
.CommandTest = Array ("Select * bla bla bla")

I looked through some web information and it was suggested to combine SQL in severable variables.
I used VAR1 and VAR2 to split my SQL statement.
However it doesn't work when I put in VBA in a format

.CommandTest = Array (VAR1& VAR2)

I also tried it to concatenate it as per below link but it didn't work
https://stackoverflow.com/questions/1807775/sql-select-error-too-many-line-continuations
https://www.anysitesolutions.com/vba-error-too-many-line-continuations/

Can anyone please help me
thanks

 

[mole999]

if you wrap each line like
varname1 = ""
varname1 = varname1 & "a " & vbCrLf
varname1 = varname1 & "b " & vbCrLf
varname1 = varname1 & "c"

I use > http://www.dpriver.com/pp/sqlformat.htm set for VB

 

[albertan]

Unfortunately, if I put variable in .CommandText = Array (Varname1) the code is not working

I get the Runtime error '1004': "The command text isn't set for the connection Database. To edit the command text, go to the Connections Manager on the Data tab and open the properties for this connection.

Without variable, the code works. The connection is seen clearly as it is established. I'm not sure why I can't split the SQL in variables...

 

[mole999]

never tried it with commandtext
its possible to split the bits out and ultimately reassemble, i build in sql then convert to strings for excel