SQL syntax inside VBA Code

Question

Is there a specific way to put SQL instructions inside VBA code? I would like to have SQL query put into VBA code but if I do so query does not work. The same query works fine if I put query instructions in Range("A1") and refer to it in a code. Is there a way to build a query inside VBA code so that it works fine? Problem especially arises when I add the WHERE clause.

Sub CreateQueryTableWithParameters()
    Dim qryTable As QueryTable
    Dim rngDestination As Range
    Dim strConnection As String
    Dim strSQL As String

    With Sheets("Sheet1")
        .Activate
        .Range("A:BY").Clear

    End With


' Define the connection string and destination range.
strConnection = "ODBC;DSN=RDBWC;UID=;PWD=;DBALIAS=RDBWC;"

Set rngDestination = Sheet1.Range("A1")
' Create a parameter query.
strSQL = "SELECT *"
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01"
strSQL = strSQL & "WHERE COR_ID <> '90003'"

' Create the QueryTable.
Set qryTable = Sheet1.QueryTables.Add(strConnection, rngDestination)

' Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.Refresh False

    With Columns("D:D")
        .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
        .AutoFit
    End With

    With Columns("H:J")
        .AutoFit
    End With

    Rows("1:1").Select
    Selection.AutoFilter

    Columns("H:H").ColumnWidth = 5
    Columns("I:I").ColumnWidth = 5
    Columns("J:J").ColumnWidth = 5
    Columns("M:M").ColumnWidth = 5.14
    Columns("N:N").ColumnWidth = 4

End Sub

I would like to add that I've tried with [] parentheses and it still does not work

strSQL = "SELECT *"
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01"
strSQL = strSQL & "WHERE [COR_ID] <> '90003'"

give some space strSQL = strSQL & " FROM pdb2i.DI_NOS_OST_MVT_01" and strSQL = strSQL & " WHERE COR_ID <> '90003'" — Abhik Chakraborty
– Abhik Chakraborty, Commented May 24, 2015 at 7:38
I've tried query on the other excel file (as I have no access to the real database at home) and adding spaces works fine. The WHERE clasue works perfectly. — Tommeck37
– Tommeck37, Commented May 24, 2015 at 16:57

Uri Goren · Accepted Answer · 2015-05-24 09:12:53Z

3

You are missing a space between statements:

strSQL = "SELECT *"
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01"
strSQL = strSQL & "WHERE [COR_ID] <> '90003'"

Would yield:

SELECT *FROM pdb2i.DI_NOS_OST_MVT_01WHERE [COR_ID] <> '90003'

Which isn't a valid SQL query, Simply change it to:

strSQL = "SELECT * "
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01 "
strSQL = strSQL & "WHERE [COR_ID] <> '90003' "

answered May 24, 2015 at 9:12

Uri Goren

13.8k8 gold badges62 silver badges113 bronze badges

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

2 Comments

Tommeck37 Over a year ago

thank you very much, can I give you some points or something?

Uri Goren Over a year ago

If my answer was helpful please accept it or at least vote up

Collectives™ on Stack Overflow

SQL syntax inside VBA Code

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related