Creating a Recordset Object from a Query

You can also create a Recordset object based on a stored SELECT query. In the following example, Current Product List is an existing SELECT query stored in the NorthwindTables database, and strDbPath is the path to the database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Current Product List")

See Also For more information about SQL statements, see Chapter 4, “Queries,” and Appendix B, “SQL Reference.”

The OpenRecordset method also accepts an SQL string instead of the name of an existing query. The previous example can be rewritten as follows:

Dim dbs As Database, rst As Recordset
Dim strSQL As String

Set dbs = OpenDatabase(strDbPath)
strSQL = "SELECT ProductID, ProductName FROM Products " & _
	"WHERE Discontinued=False ORDER BY ProductName;"
Set rst = dbs.OpenRecordset(strSQL)

The disadvantage of this approach is that the query must be compiled each time it’s executed, whereas the stored query is compiled when it’s saved, which usually results in slightly better performance.

Note When you create a Recordset object by using an SQL string or a stored query, your code doesn’t continue running until the query returns the first record in the Recordset object. You may want to consider displaying a message in the status bar while the query is running.