Geek asks and answers

Access: Using a query in VBA for a recordset

I'm used to recording in the following format:

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "Select field1, field2 from myTable where field1 > 30"

Set rs = CurrentDb.OpenRecordset(strSQL)

'... Do wahtever using rs.

Is it possible to use an already created query instead of text and provide it with a where clause?

This is a linked table with a SQL Server 2008 database. I like to keep simple queries in Access.

+3
source share
1 answer

You can either

  • Use a query with parameters and set parameter values, provided that the query uses parameters.

    Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("qry_SomeQueryWithParameters")

qdf.Parameters("SomeParam").Value = "whatever"

Set rst = qdf.OpenRecordset

or

  • Specify the request name as a command and use the Filter Property in the recordset

    Dim rs As DAO.Recordset
    Dim rsFiltered As DAO.Recordset


    Set rs = CurrentDb.OpenRecordset(qry_SomeQueryWithoutParameters)

    rs.Filter = "field1 > 30"
    set rsFiltered  = rs.OpenRecordset
+7
source

More articles:


All Articles