Create a DAO Recordset from a query
You can create a Recordset object based on a stored select query. In the following code example, Current Product List
is an existing select query stored in the current database.
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstProducts = dbsNorthwind.OpenRecordset("Current Product List")
If a stored select query does not already exist, the OpenRecordset method also accepts an SQL string instead of the name of a query. The previous example can be rewritten as follows.
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Dim strSQL As String
Set dbsNorthwind = CurrentDb
strSQL = "SELECT * FROM Products WHERE Discontinued = No " & _
"ORDER BY ProductName"
Set rstProducts = dbsNorthwind.OpenRecordset(strSQL)
The disadvantage of this approach is that the query string must be compiled each time it runs, whereas the stored query is compiled the first time it is saved, which usually results in slightly better performance.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for