Access: Using query in VBA for recordset

Question

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.

Have a look at the first example where you can use Parameters in code msdn.microsoft.com/en-us/library/ff820966.aspx — Adriaan Stander
– Adriaan Stander, Commented May 10, 2012 at 18:19

Conrad Frix · Accepted Answer · 2012-05-10 19:07:44Z

15

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

edited May 10, 2012 at 19:07

answered May 10, 2012 at 18:21

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rick Over a year ago

Using the second example had to add a statement to set another recordset initialized from the filterd recordset and use that, per the example you provided. Thanks

Conrad Frix Over a year ago

@Rick nice. I've updated my answer to include the correction.

Timo Over a year ago

@ConradFrix I get "Syntaxfehler(fehlender Operator) in Abfrageausdruck 'massnahme=bankett'. My code:

abfrage = "SELECT * FROM tab1" ;Set rs = db.OpenRecordset(abfrage)      rs.Filter = "15massnahme=bankett"          rsf = rs.OpenRecordset

Timo Over a year ago

@ConradFrix checking your first solution: I get "Error 3265, Element not found in this list" if I write abfrage = "SELECT * FROM tab1"; Set qdf = db.QueryDefs(abfrage)

Collectives™ on Stack Overflow

Access: Using query in VBA for recordset

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related