VBA 用查询检索记录
示例
查询可以通过两种方式执行，两种方式都返回一个ADORecordset对象，该对象是返回的行的集合。请注意，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 Notrecords.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.Printrecords("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 Anddatabase.State= adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print"Error " &Err.Number& ": " & Err.Description
Resume CleanExit
End Sub第二种方法是为Command要执行的查询创建ADO对象。这需要更多代码，但对于使用参数化查询是必需的：
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.Printrecords("Item")
records.MoveNext
Loop
End If
CleanExit:
If Not records Is Nothing Then records.Close
If Not database Is Nothing Anddatabase.State= adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print"Error " &Err.Number& ": " & Err.Description
Resume CleanExit
End Sub请注意，发送到数据源的命令容易受到SQL注入的影响，无论是有意还是无意的。通常，不应通过串联任何类型的用户输入来创建查询。而是应对其进行参数化（请参阅创建参数化命令）。