使用查詢檢索記錄

查詢可以通過兩種方式執行，這兩種方式都返回 ADO Recordset 物件，該物件是返回行的集合。請注意，下面的兩個示例都使用了 “連線到資料來源 ”示例中的 OpenDatabaseConnection 函式，以簡潔起見。請記住，傳遞給資料來源的 SQL 的語法是特定於提供者的。

第一種方法是將 SQL 語句直接傳遞給 Connection 物件，這是執行簡單查詢的最簡單方法：

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim records As ADODB.Recordset
        Set records = database.Execute("SELECT DISTINCT Item FROM Table")
        'Loop through the returned Recordset.
        Do While Not records.EOF      'EOF is false when there are more records.
            'Individual fields are indexed either by name or 0 based ordinal.
            'Note that this is using the default .Fields member of the Recordset.
            Debug.Print records("Item")
            'Move to the next record.
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

第二種方法是為要執行的查詢建立 ADO Command 物件。這需要更多程式碼，但是為了使用引數化查詢是必要的：

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim query As ADODB.Command
        Set query = New ADODB.Command
        'Build the command to pass to the data source.
        With query
            .ActiveConnection = database
            .CommandText = "SELECT DISTINCT Item FROM Table"
            .CommandType = adCmdText
        End With
        Dim records As ADODB.Recordset
        'Execute the command to retrieve the recordset.
        Set records = query.Execute()

        Do While Not records.EOF
            Debug.Print records("Item")
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

請注意，傳送到資料來源的命令容易受到 SQL 注入的攻擊，無論是有意還是無意。通常，不應通過連線任何型別的使用者輸入來建立查詢。相反，它們應該引數化（請參閱建立引數化命令 ）。