是否可以在Microsoft Access更新查询中以编程方式传递参数? [英] Is it possible to pass parameters programmatically in a Microsoft Access update query?

问题描述

我有一个很大的查询，连接了十几个表，我想基于id字段(例如:between nStartID and nEndID)拉回记录.

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?

谢谢.

推荐答案

我刚刚对此进行了测试，并且可以在Access 2010中使用.

I just tested this and it works in Access 2010.

假设您有一个带有参数的SELECT查询:

Say you have a SELECT query with parameters:

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

您以交互方式运行该查询，并提示您输入[startID]和[endID].可行，因此您可以将该查询另存为[MemberSubset].

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

现在，您将基于该查询创建一个UPDATE查询:

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])));

您以交互方式运行该查询，并再次提示您输入[startID]和[endID]，并且效果很好，因此将其保存为[MemberSubsetUpdate].

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]的参数，从VBA代码运行[MemberSubsetUpdate]，即使它们实际上是[MemberSubset]的参数也是如此.这些参数值会滴流"到需要的位置，并且该查询确实可以在没有人工干预的情况下工作:

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

这篇关于是否可以在Microsoft Access更新查询中以编程方式传递参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！