Unless you open a table-type Recordset object and set its Index property, you can’t be sure records will appear in any specific order. Most of the time, however, you want to retrieve records in some specific order. For example, you may want to view invoices arranged by increasing invoice number or retrieve employee records arranged alphabetically by last name.
To sort non-table Recordset object data, use an ORDER BY clause in the SQL query that constructs the Recordset object, whether that clause is contained in a QueryDef object, a stored query in a database, or in an SQL string passed to the OpenRecordset method.
With any type of Recordset object, use an SQL WHERE clause in a query to filter data (to restrict the result set to records that meet some criteria).
The following example opens a dynaset-type Recordset object, and uses an SQL statement to filter and sort a Recordset object by using the WHERE and ORDER BY clauses. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("SELECT FirstName, " & _
"LastName FROM Employees WHERE Title = ""Sales Representative"" " & _
"ORDER BY LastName")
With rst
Do While Not .EOF
Debug.Print !FirstName & " " & !LastName
.MoveNext
Loop
End With
Microsoft Access Users One drawback of executing an SQL query in an OpenRecordset method is that it has to be compiled every time you run it. If this query is to be used frequently, you can improve performance by creating a stored query that contains the same SQL statement, and then opening a Recordset object against the query. In the following example, Sales Representatives is a saved query:
Dim dbs As Database, rst As RecordsetSet
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Sales Representatives")
For even greater flexibility and control at run time, you can use query parameters to determine the sort order and filter criteria. This is discussed in more detail in the “Using Parameter Queries” section later in this chapter.
A Recordset object opened from a QueryDef object can also be used as a template to re-create the QueryDef object using the CopyQueryDef method. This is useful in situations where a Recordset object variable created from a QueryDef is passed to a function, and the function must re-create the SQL equivalent of the query and possibly modify it.
You can use the Requery method on any Recordset object except a table-type Recordset object when you want to re-execute the underlying query after changing a parameter. This is more convenient than opening a new Recordset object, and the query also executes faster.
The following procedure takes a Recordset object that was opened on a QueryDef object and uses the CopyQueryDef method to extract the underlying query’s SQL string, prompts the user to add an additional WHERE clause to the query, and requeries the Recordset object based on the altered query:
Function AddQueryFilter(rst As Recordset) As Recordset
Dim qdf As QueryDef
Dim strNewFilter As String, strQuery As String
Dim varPosition As Variant, intLength As Integer
Const conOperationNotSupported As Integer = 3251
On Error GoTo Err_AddQueryFilter
Set qdf = rst.CopyQueryDef
strNewFilter = InputBox("Enter new constraint for WHERE clause, " _
& "e.g., ProductName Like 'M*'")
' Create new query string with added restriction.
strQuery = qdf.SQL
' Return string length.
intLength = Len(strQuery)
' Check for WHERE clause in string.
varPosition = InStr(strQuery, "WHERE")
If varPosition > 0 Then
' Append new restriction to existing WHERE clause.
strQuery = Left(strQuery, varPosition + 5) & strNewFilter & " AND " _
& Right(strQuery, intLength - (varPosition + 5))
qdf.SQL = strQuery
Else
' If no WHERE clause, alert user and exit function.
' You could also add code to handle this situation.
MsgBox "The SQL statement you supplied did not include a WHERE clause."
GoTo Exit_AddQueryFilter
End If
' Requery recordset.
rst.Requery qdf
Set AddQueryFilter = rst
Exit_AddQueryFilter:
Exit Function
Err_AddQueryFilter:
If Err = conOperationNotSupported Then
Dim strMsg As String
strMsg = "This recordset was not opened on a QueryDef object. " & _
"You can call this function only with a recordset opened " & _
"on a QueryDef object."
MsgBox strMsg, vbOKOnly
Else
MsgBox Err & ": " & vbCrLf & Err.Description
End If
Set AddQueryFilter = Nothing
Resume Exit_AddQueryFilter
End Function
Note To execute the Requery method, the Recordset object’s Restartable property must be set to True. The Restartable property is always True when the Recordset object is created from a query other than a crosstab query based on tables in a Microsoft Jet database. SQL pass-through queries are not restartable. Queries against linked tables in another database format may or may not be restartable. To determine whether a Recordset object can re-execute its query, check the Restartable property.
Another approach to sorting and filtering recordsets is to set the Sort and Filter properties on an existing Recordset object, and then open a new Recordset object from the existing one. However, this is usually much slower than just including the sort and filter criteria in the original query or changing the query parameters and re-executing the query with the Requery method. The Sort and Filter properties are useful when you want to allow a user to sort or restrict a result set but the original data source is unavailable for a new query — for example, when a Recordset object variable is passed to a function, and the function must reorder records or restrict the records in the set. With this approach, performance is likely to be slow if more than 100 records are in the Recordset object. Using the CopyQueryDef method described in the previous section is preferable.