formatting SQL in VBA

Question

I am using VBA to open an ODBC connection and retrieve data. I have some SQL queries that populate what I need:

SELECT
  tbl.a AS test1
  tbl.b AS test2
  tbl.c AS test3
FROM
  db.tbl AS tbl
INNER JOIN db.more AS more
  ON more.a = tbl.a

Is there a way I can input this query in VBA so it retains its structure and looks readable? I did this:

Dim sql AS string
sql = ""
sql = sql & "SELECT"
sql = sql & "  tbl.a AS test1"
sql = sql & "  tbl.b AS test2"
sql = sql & "  tbl.c AS test3"
sql = sql & "FROM"
sql = sql & "  db.tbl AS tbl"
sql = sql & "INNER JOIN db.more AS more"
sql = sql & "  ON more.a = tbl.a"

This doesn't really look good to me, buf it's the best I can do then I'll manage. I just wanted to see if there was a better way.

Also discussed at stackoverflow.com/questions/3671308/…

MattClarke
– MattClarke

2014-02-04 23:55:39 +00:00
Commented Feb 4, 2014 at 23:55 — MattClarke
– MattClarke, Commented Feb 4, 2014 at 23:55

user2140261 · Accepted Answer · 2014-02-04 21:04:43Z

2

It is only preference but maybe this?

sql = "SELECT" + _
      "  tbl.a AS test1" + _
      "  tbl.b AS test2" + _
      "  tbl.c AS test3" + _
      "FROM"             + _
      "  db.tbl AS tbl"  + _
      "INNER JOIN db.more AS more" + _
      "  ON more.a = tbl.a"

Another option is to enter the SQL into a cell in a worksheet, Then HIDE that worksheet, Then in VBA simply use:

sql = Worksheet("NameOfHiddenWorkSheet").Range("A1").Value

In my needs I usually name the sheets as Settings and Name the Ranges (Range Names)

Then you could call it like:

sql = Sheets("Settings").Range("Sql").Value

edited Feb 4, 2014 at 21:04

answered Feb 4, 2014 at 20:54

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeffrey Kramer Over a year ago

Thank you, this is very helpful. I had considered putting the sql on a worksheet like you said, but there are some fields that are actually dynamic based off some range values so it would be equally choppy across multiple rows so the basic approach is probably easier. I do like that idea if it was static SQL code though!

MattClarke Over a year ago

One problem with using line continuation for the assignment of an SQL string is that you can run up against the limit of 25 lines pretty easily.

Jeffrey Kramer Over a year ago

Just ran up against this. I may have to store it after all, or just go with the original method. How annoying!

user2140261 Over a year ago

@JeffreyKramer There is also always the option of storing the commands in a file, either online or in the secure location, or in the registry these options also allow you the ability to modify the connections, and SQL statements without the need to open excel and VBA Editor also. There are dozens of ways to improve on your situation, it really all depends on exactly what you have and want to do.

Dmitry Pavliv · Accepted Answer · 2014-02-04 20:54:06Z

2

Does this helps?

Sub test()
    Dim sql As String

    sql = "SELECT " & _
      "tbl.a AS test1 " & _
      "tbl.b AS test2 " & _
      "tbl.c AS test3 " & _
    "FROM " & _
      "db.tbl AS tbl" & _
    "INNER JOIN db.more AS more" & _
      "ON more.a = tbl.a"

End Sub

answered Feb 4, 2014 at 20:54

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Collectives™ on Stack Overflow

formatting SQL in VBA

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related