使用ADODB记录集执行合并的更新查询 [英] Using an ADODB record set to perform a joined update query

问题描述

在下面的代码中，我想将ADODB记录集"rs3"连接到表"tblValueChain10"，并基于在ADODB记录集"rs3"中提取的值来更新3个不同的列.目前，更新查询未返回任何内容.

In the following code, I would like to join ADODB record set 'rs3' to table 'tblValueChain10' and update 3 different columns based on the values extracted in the ADODB record set 'rs3'. Currently, the update query is not returning anything.

Dim st_Sql3 As String
Dim rs3 As ADODB.Recordset
Set rs3 = New ADODB.Recordset
Dim Max3 As Integer

rs3.Open "SELECT tblRisk05Holding.IDMacroProcesso01, tblRisk05Holding.Level01Risk, Max(tblRisk05Holding.ManualityStatus) AS MaxDiManualityStatus, Max(tblRisk05Holding.RiskProbabilityStatus) AS MaxDiRiskProbabilityStatus, Max(tblRisk05Holding.RiskExposureStatus) AS MaxDiRiskExposureStatus FROM tblRisk05Holding GROUP BY tblRisk05Holding.IDMacroProcesso01, tblRisk05Holding.Level01Risk", CurrentProject.Connection


st_Sql3 = "UPDATE tblValueChain10 INNER JOIN rs3 ON (tblValueChain10.IDMacroProcesso01 = tblRisk05Holding.IDMacroProcesso01) SET L1RiskManuality = " & rs3.Fields(2) & ", L1RiskProbability = " & rs3.Fields(3) & ", L1RiskGravity = " & rs3.Fields(4) & ""
Application.DoCmd.RunSQL (st_Sql2)

rs3.Close
Set rs3 = Nothing

推荐答案

Access永远不允许您将记录集对象用作另一个查询中的数据源.有ADO记录集还是DAO记录集都没有关系.你做不到.而且查询类型(SELECT，UPDATE，INSERT等)也无关紧要；您不能将记录集对象用作任何查询类型的数据源.

Access never allows you to use a recordset object as a data source in another query. It doesn't matter whether you have an ADO or DAO recordset; you can't do it. And the query type (SELECT, UPDATE, INSERT, etc.) also doesn't matter; you can't use a recordset object as a data source in any query type.

首先将SELECT语句保存为命名查询 qryRS3 ，您可能会得到可行的UPDATE.然后将UPDATE修改为INNER JOIN tblValueChain10 修改为 qryRS3 .但是我不确定Access是否会认为该查询是可更新的. GROUP BY可能会导致Access将其视为不可更新.您必须进行测试才能看到.

You might get a workable UPDATE by first saving your SELECT statement as a named query, qryRS3. Then revise the UPDATE to INNER JOIN tblValueChain10 to qryRS3. But I'm uncertain whether Access would consider that query to be updatable; the GROUP BY might cause Access to treat it as not updatable. You'll have to test to see.

这篇关于使用ADODB记录集执行合并的更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！