CODEWITHSUNDEEP
ms-accessvba
Rick
Rick

Reputation: 2308

Access: Using query in VBA for recordset

I have been accustomed to do recordssets 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 giving it the where clause?

This is a linked table to a SQL Server 2008 Database. I like to save simple queries in Access.

Upvotes: 7

Views: 89182

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

You can either

  • Use a query that has parameters and specify values for parameters 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 a query name as the command and use the Filter property on 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

Upvotes: 15

Related Questions