vba mysql,mysql-VBA代码中的SQL语法

是否有将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'"

解决方法:

您在语句之间缺少空格：

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' "

标签：sql,mysql,excel,vba,excel-vba

来源： https://codeday.me/bug/20191120/2042699.html