mysql - VBA代码中的SQL语法

Question

是否有特定的方法可以将 SQL 指令放入 VBA 代码中？我想将 SQL 查询放入 VBA 代码中，但如果我这样做，查询将不起作用。如果我将查询指令放在 Range("A1") 中并在代码中引用它，则相同的查询工作正常。有没有办法在 VBA 代码中构建查询以使其正常工作？当我添加 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

我想补充一点，我已经尝试使用 [] 括号，但它仍然不起作用

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

Answer 1

语句之间缺少空格:

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

会产生:

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

这不是有效的 SQL 查询，只需将其更改为:

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