Geek Answers Handbook

Using an ADODB Record Set to Run a Consolidated Update Request

In the following code, I would like to join the ADODB record set 'rs3' in the table 'tblValueChain10' and update 3 different columns based on the values ​​retrieved in the ADODB record set 'rs3'. Currently, the update request does not return 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
+4
source share
4 answers

. , ADO DAO; . (SELECT, UPDATE, INSERT ..) ; .

UPDATE, SELECT , qryRS3. UPDATE INNER JOIN tblValueChain10 qryRS3. , Access ; GROUP BY Access . , .

+6

, ADO Recordset , ( @HansUp), tblValueChain10 , rs3 .

, :

UPDATE 
  tblValueChain10 
  INNER JOIN 
  (
    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
  ) AS qry_Risk05Holding
  ON (tblValueChain10.IDMacroProcesso01 = qry_Risk05Holding.IDMacroProcesso01) 
SET 
  tblValueChain10.L1RiskManuality = qry_Risk05Holding.MaxDiManualityStatus, 
  tblValueChain10.L1RiskProbability = qry_Risk05Holding.MaxDiRiskProbabilityStatus, 
  tblValueChain10.L1RiskGravity = qry_Risk05Holding.MaxDiRiskExposureStatus

.

SQL :

Dim st_Sql3 As String

st_Sql3 = " UPDATE tblValueChain10 INNER JOIN ( "

st_Sql3 = st_Sql3 & " SELECT  " _
    & " tblRisk05Holding.IDMacroProcesso01,  " _
    & " tblRisk05Holding.Level01Risk,  " _
    & " Max(tblRisk05Holding.ManualityStatus) AS MaxDiManualityStatus,  " _
    & " Max(tblRisk05Holding.RiskProbabilityStatus) AS MaxDiRiskProbabilityStatus,  " _
    & " Max(tblRisk05Holding.RiskExposureStatus) AS MaxDiRiskExposureStatus  "

st_Sql3 = st_Sql3 & " FROM  " _
    & " tblRisk05Holding  " _
    & " GROUP BY  " _
    & " tblRisk05Holding.IDMacroProcesso01,  " _
    & " tblRisk05Holding.Level01Risk "

st_Sql3 = st_Sql3 & " ) AS qry_Risk05Holding " _
  & " ON (tblValueChain10.IDMacroProcesso01 = qry_Risk05Holding.IDMacroProcesso01) " _
  & " SET " _
  & " tblValueChain10.L1RiskManuality = qry_Risk05Holding.MaxDiManualityStatus,  " _
  & " tblValueChain10.L1RiskProbability = qry_Risk05Holding.MaxDiRiskProbabilityStatus, " _
  & " tblValueChain10.L1RiskGravity = qry_Risk05Holding.MaxDiRiskExposureStatus "

Application.DoCmd.RunSQL (st_Sql3)

, , , VBA , _.

, JOIN, , , IDMacroProcesso01 , .

Acces , , , - , , . , .

+3

, :

Do While NOT rs3.EOF

       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) & "" 
       DoCmd.RunSQL (st_Sql3)

rs3.MoveNext
Loop

, RunSQL() SQL.

+2

, :

Set rs3 = New ADODB.Recordset

form_load, ,

private sub Form_load()
Set rs3 = New ADODB.Recordset
End Sub

note: vb6.0. . ,

+2

More articles:


All Articles