Is it possible to pass parameters programmatically in a Microsoft Access update query?

Question

I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID).

I created two parameters and tested them as criteria and they work fine.

The issue is, I need to run an insert query from this main query, and need the parameters where they are, in the main query. So, I need to pass parameters to it programmatically.

Anyone have a clue as to how this can be done?

Thanks.

Answer 1

I just tested this and it works in Access 2010.

Say you have a SELECT query with parameters:

PARAMETERS startID Long, endID Long; SELECT Members.* FROM Members WHERE (((Members.memberID) Between [startID] And [endID])); 

You run that query interactively and it prompts you for [startID] and [endID]. That works, so you save that query as [MemberSubset].

Now you create an UPDATE query based on that query:

UPDATE Members SET Members.age = [age]+1 WHERE (((Members.memberID) In (SELECT memberID FROM [MemberSubset]))); 

You run that query interactively and again you are prompted for [startID] and [endID] and it works well, so you save it as [MemberSubsetUpdate].

You can run [MemberSubsetUpdate] from VBA code by specifying [startID] and [endID] values as parameters to [MemberSubsetUpdate], even though they are actually parameters of [MemberSubset]. Those parameter values "trickle down" to where they are needed, and the query does work without human intervention:

Sub paramTest()     Dim qdf As DAO.QueryDef     Set qdf = CurrentDb.QueryDefs("MemberSubsetUpdate")     qdf!startID = 1  ' specify     qdf!endID = 2    '     parameters     qdf.Execute     Set qdf = Nothing End Sub 

Answer 2

Try using the QueryDefs. Create the query with parameters. Then use something like this:

Dim dbs As DAO.Database Dim qdf As DAO.QueryDef  Set dbs = CurrentDb Set qdf = dbs.QueryDefs("Your Query Name")  qdf.Parameters("Parameter 1").Value = "Parameter Value" qdf.Parameters("Parameter 2").Value = "Parameter Value" qdf.Execute qdf.Close  Set qdf = Nothing Set dbs = Nothing