VBA - too many line continuations

albertan

Board Regular
Joined
Nov 16, 2014
Messages
66
Office Version
  1. 365
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top