vba 是否可以在 Microsoft Access 更新查询中以编程方式传递参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16568461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to pass parameters programmatically in a Microsoft Access update query?
提问by Jav
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).
我有一个查询这是相当大的，在加盟十几张桌子，我想拉回来基于一个id字段记录（如：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.
谢谢。
回答by Gord Thompson
I just tested this and it works in Access 2010.
我刚刚对此进行了测试，它在 Access 2010 中有效。
Say you have a SELECT query with parameters:
假设您有一个带参数的 SELECT 查询：
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].
您以交互方式运行该查询，它会提示您输入 [startID] 和 [endID]。这是有效的，因此您将该查询保存为 [MemberSubset]。
Now you create an UPDATE query based on that query:
现在您根据该查询创建一个 UPDATE 查询：
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].
您以交互方式运行该查询，并再次提示您输入 [startID] 和 [endID]，它运行良好，因此您将其另存为 [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:
您可以通过将 [startID] 和 [endID] 值指定为 [MemberSubsetUpdate] 的参数，从 VBA 代码运行 [MemberSubsetUpdate]，即使它们实际上是 [MemberSubset] 的参数。这些参数值“滴入”到需要它们的地方，并且查询确实可以在没有人工干预的情况下工作：
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
回答by Jessica
Try using the QueryDefs. Create the query with parameters. Then use something like this:
尝试使用 QueryDefs。使用参数创建查询。然后使用这样的东西：
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
回答by Dougie
You can also use TempVars - note '!' syntax is essential
您也可以使用 TempVars - 注意 '!' 语法是必不可少的
回答by Nigel
Many thanks for the information about using the QueryDefs collection! I have been wondering about this for a while.
非常感谢有关使用 QueryDefs 集合的信息！我一直想知道这个问题有一段时间了。
I did it a different way, without using VBA, by using a table containing the query parameters.
我通过使用包含查询参数的表，在不使用 VBA 的情况下以不同的方式做到了这一点。
E.g:
例如：
SELECT a_table.a_field
FROM QueryParameters, a_table
WHERE a_table.a_field BETWEEN QueryParameters.a_field_min
AND QueryParameters.a_field_max
Where QueryParametersis a table with two fields, a_field_minand a_field_max
QueryParameters有两个字段的表在哪里，a_field_min以及a_field_max
It can even be used with GROUP BY, if you include the query parameter fields in the GROUP BYclause, and the FIRSToperator on the parameter fields in the HAVINGclause.
GROUP BY如果在GROUP BY子句中包含查询参数字段，并且在子句中包含参数字段上的FIRST运算符，则它甚至可以与 一起使用HAVING。
回答by Patrick Honorez
Plenty of responses already, but you can use this:
已经有很多回复，但您可以使用这个：
Sub runQry(qDefName)
Dim db As DAO.Database, qd As QueryDef, par As Parameter
Set db = CurrentDb
Set qd = db.QueryDefs(qDefName)
On Error Resume Next
For Each par In qd.Parameters
Err.Clear
par.Value = Eval(par.Name) 'try evaluating param
If Err.Number <> 0 Then 'failed ?
par.Value = InputBox(par.Name) 'ask for value
End If
Next par
On Error GoTo 0
qd.Execute dbFailOnError
End Sub
Sub runQry_test()
runQry "test" 'qryDef name
End Sub
