sql - Where oh Where（带变量的 VBA SQL Where 子句）

Question

我是试图帮助 VBA 项目的 SQL 人。尝试运行此 sql 语句时出现 VBA 错误。运行时错误“-2147217913 (80040e07)”自动化错误。

我可以毫无问题地发送一个号码，所以我知道在传递字符串时我做错了什么。谢谢您的帮助！我花了很多时间试图弄清楚。

sSQLSting = "SELECT TOP 10 PctTtlAssets, Port_Code FROM [db_detail$] 
WHERE Port_Code  = ' " & [vbaThisPort] & " ' "

保存到我的变量时看起来像这样。

sSQLSting = "SELECT TOP 10 PctTtlAssets, Port_Code FROM [db_detail$] 
WHERE Port_Code  = 'salmemhs' "

谢谢

附言

这是 vba 家伙正在使用的完整代码。再次，工作正常，直到我发送一个字符串。

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String, vbaThisPort As String


vbaThisPort = Trim(ThisWorkbook.Names("ThisPort").RefersToRange(1, 1))

'MsgBox (vbaThisPort) 'Testing

DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath ="C:\InputData.xlsx"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

' Your SQL Statemnt (Table Name= Sheet Name=[DataSheet$])"

Conn.Open sconnect
    sSQLSting = "SELECT TOP 10 PctTtlAssets, Port_Code FROM [db_detail$] WHERE Port_Code  = '" & [vbaThisPort] & "'"

    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

End Sub

标签： sqlexcelvba