sql - 在 Excel VBA 中选择 SQL 语句

Question

Sub LogCheck()
    Dim cn As Object
    Dim rs As Object
    Dim StrSql As String
    Dim strConnection As String
    Dim AppPath As String
    Set cn = CreateObject("ADODB.Connection")
    AppPath = Application.ActiveWorkbook.Path
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\ceo.accdb;"
    cn.Open strConnection
    S_ID = Sheets("My").Range("A1").Value
    StrSql = "SELECT * FROM EDO Where ID = ' " & S_ID & " '"
    rs.Open StrSql, cn
    If rs = Null Then
        MsgBox "Record Not found"
    Else
        MsgBox "Record Found"
    End If
End Sub

我无法运行此代码。它的显示错误。请帮帮我。谢谢!
这里 S_ID是我想从表中搜索的数据，ID 是 EDO 表中的主键。

Answer 1

在这种情况下，您可以检测记录集是否为空，检查 .EOF property :

Sub TestIfRecordFound()

    Dim strConnection As String
    Dim strID As String
    Dim strQuery As String
    Dim objConnection As Object
    Dim objRecordSet As Object

    strConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source='C:\ceo.accdb';"
    strID = Sheets("My").Range("A1").Value
    strQuery = _
        "SELECT * FROM EDO WHERE ID = '" & strID & "';"

    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open strConnection
    Set objRecordSet = objConnection.Execute(strQuery)

    If objRecordSet.EOF Then
        MsgBox "Record Not found"
    Else
        MsgBox "Record Found"
    End If

    objConnection.Close

End Sub