Why Two row SQL Statement getting error in VBA

Question

When i write two row SQL query in VBA, i get an error. But when wrote one row query, it is running true. Example

Sub Run_Report()
Dim Server_Name As String

Server_Name = "SRV2"

Call Connect_To_SQLServer(Server_Name, "Kn_v3", "SELECT TOP (1000) *  FROM [d].[dbo].[AllBanks]")
End Sub


IT is running but below code is getting error;

Sub Run_Report()
Dim Server_Name As String

Server_Name = "SRRP2"

Call Connect_To_SQLServer(Server_Name, **"Kn_v3", "SELECT TOP (1000) *
FROM [ ].[dbo].[AllBanks]"**)
End Sub

Reason of this question, my query is bigger than one row. So i must use it on 2 row.

Use underscore "_" as line continuation. BTW there is no "real" need to have newline in an SQL query. You can write it all on one line as well, so having multiple lines is only about readability. — Geert Bellekens
– Geert Bellekens, Commented Jan 23, 2020 at 8:06
I couldn't find a duplicate with a usable answer so I wrote one myself. — Geert Bellekens
– Geert Bellekens, Commented Jan 23, 2020 at 8:26

Geert Bellekens · Accepted Answer · 2020-01-23 08:25:48Z

In the VBA syntax a newline is significant. Each statement is ended by a newline.
Therefore a statement such as

myString = "first line of the string
second line of the string"

Will return a syntax error because the first line contains a string definition that is missing the closing double quotes.

So you should make sure that you either put the complete statement on one line

myString = "first line of the string second line of the string"

Or you do it in two statements

myString = "first line of the string" 
myString = MyString & "second line of the string"

But now you are still missing your newline. You can add that into the mix like this

myString = "first line of the string" & vbNewLine &  "second line of the string"

Or in the multiple line variant

myString = "first line of the string" & vbNewLine
myString = MyString  & "second line of the string"

Now the first variant become hard to read if there are many line, and the second variant has each time has the part myString = MyString & which is annoying.

You can avoid all that with the line continuation character _ (underscore)

Then you end up with this way of defining multiple line strings in your code

myString = "first line of the string" & vbNewLine & _
           "second line of the string" & vbNewLine & _
           "third line of the string" & vbNewLine & _
           "fourth line of the string"

Which is my favorite variant

Collectives™ on Stack Overflow

Why Two row SQL Statement getting error in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related