VBA - too many line continuations

albertan

New Member
Joined
Nov 16, 2014
Messages
34
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
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
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

Moderator
Joined
Oct 23, 2004
Messages
9,879
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
 

Forum statistics

Threads
1,078,451
Messages
5,340,367
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top