使用变量设置rst

Question

我正在尝试使用SQL字符串打开一个记录集。我得到运行时错误3061“参数太少”。任何帮助都将不胜感激。

Dim stAppName As String
Dim stURL As String
Dim rst As Recordset
Dim dbs As Database
Dim stringToSearch As Integer
Dim strSQL As String

Set dbs = CurrentDb


stringToSearch = InputBox("What is your route #?", "Enter route #: ")


strSQL = "SELECT ESRP.* FROM ESRP WHERE ESRP.Route=stringToSearch"

Set rst = dbs.OpenRecordset(strSQL)

Answer 1

请按照Fionnuala的建议更改strSQL的代码行，您需要在引号之外使用变量。

假设Route字段是文本数据类型，我们需要为字符串加上单引号，如果它的数字没有单引号，那么对于dates put #而不是单引号

strSQL = "SELECT ESRP.* FROM ESRP WHERE ESRP.Route='" & stringToSearch & "'"

Answer 2

这是一个小样本，也许它能帮助你

Public Function fn_SQL_dbOpenRecordset(Optional vsql As String = "") As Recordset

    Dim dbs       As DAO.Database 

    Dim rs As Recordset
    On Error GoTo END_CODE

    'Set the database
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset(vsql, dbOpenForwardOnly) 'you can use: dbOpenDynamic; dbOpenSnapshot; dbOpenTable

    Set fn_SQL_dbOpenRecordset = rs

    Exit Function

END_CODE:
    Set fn_SQL_dbOpenRecordset = Nothing
End Function


Public Sub Program_Test()

    On Error GoTo ERROR_SUB
    Dim rs As Recordset
    Dim sName As String


    sName = "Joe"
    sName = "'" & sName & "'" 'WARNING: BE CAREFUL WITH SQL INJECTION !!! Google it

    Set rs = fn_SQL_dbOpenRecordset("select * from  table1 d where PersonName = " & sName)

    Dim i As Long
    i = 0

    While Not rs.EOF
      Debug.Print rs(0).Value & " - " & rs(1).Value
      rs.MoveNext
    Wend

ERROR_SUB:

     On Error Resume Next
     If Not rs Is Nothing Then rs.Close
     Set rs = Nothing
End Sub