SQL syntax inside VBA code
Is there any specific way to put SQL statements inside VBA code? I would like the SQL query to fit into VBA code, but if I do this, the query doesn't work. The same query works fine if I put the query statements in Range ("A1") and reference it in the code. Is there a way to build the query inside VBA code to make it work fine? The problem comes up especially when I add a sentence WHERE
.
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 tried with [] parentheses and it still doesn't work
strSQL = "SELECT *"
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01"
strSQL = strSQL & "WHERE [COR_ID] <> '90003'"
+3
source to share
1 answer
You are missing the space between the operators:
strSQL = "SELECT *"
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01"
strSQL = strSQL & "WHERE [COR_ID] <> '90003'"
Let's say:
SELECT *FROM pdb2i.DI_NOS_OST_MVT_01WHERE [COR_ID] <> '90003'
What an invalid SQL query, Just change it to:
strSQL = "SELECT * "
strSQL = strSQL & "FROM pdb2i.DI_NOS_OST_MVT_01 "
strSQL = strSQL & "WHERE [COR_ID] <> '90003' "
+3
source to share