update query

[dollygg]

Why the following code written in VB6 didn't work:



Set rst = New ADODB.Recordset
'
With rst
On Error Resume Next
.Open "UPDATE Collateral, [Custodian Collateral 3rd PartySumBySM] SET [Collateral].[SetType] = [Custodian Collateral 3rd PartySumBySM].[SetType] WHERE " & _
"((Collateral.TUnits = [Custodian Collateral 3rd PartySumBySM].[Collateral Units]) AND (Collateral.Fund = [Custodian Collateral 3rd PartySumBySM].[Fund]) AND (Collateral.SM = [Custodian Collateral 3rd PartySumBySM].[SM]))", sDBcn, 3, 3
On Error GoTo 0
End With
Set rst = Nothing

Please note that [Custodian Collateral 3rd PartySumBySM] is an query result from another query, Collateral is a table. The backend is an MS Access Database(mdb file)

[Fre]

You need to join before setting

UPDATE Collateral INNER JOIN [Custodian Collateral 3rd PartySumBySM] ON Collateral.TUnits = [Custodian Collateral 3rd PartySumBySM].[Collateral Units] AND Collateral.Fund = [Custodian Collateral 3rd PartySumBySM].[Fund] AND Collateral.SM = [Custodian Collateral 3rd PartySumBySM].[SM]
SET [Collateral].[SetType] = [Custodian Collateral 3rd PartySumBySM].[SetType]