update a single table within a multiple table recordset

[doquigley]

MS Access does it
VBA does it
VB6?

I am linked to a MS SQL Server Database and my vba knowledge is of no use. I try the 2 following methods but neither work

An update recordset which has more than one table linked but only updating a single field does not work

Set objRs = New ADODB.Recordset
sSql = "UPDATE TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId SET TempCollectables.SourceDestTypeId = [SourceDest]![SourceDestCode] WHERE (((SourceDest.SourceDestGroup)='" & "Intercompany" & "'));"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
Set objRs = Nothing


A select recordset based on more than one table with a step through and then update on a single field does not work

Set objRs = New ADODB.Recordset
sSql = "SELECT SourceDestCode, SourceDestTypeId FROM TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId WHERE (SourceDest.SourceDestGroup ='" & "Intercompany" & "');"
objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
If Not objRs.EOF Then
'objRs.MoveFirst
Do While Not objRs.EOF
objRs!SourceDestTypeId = objRs!SourceDestCode
objRs.Update
objRs.MoveNext
Loop
End If
Set objRs = Nothing


How do I get around this or am I missing something

Cheers, a really helpful site

 

[Andrzejek]

How about something like:

    [blue]Dim strUpdate As String[/blue]
    Set objRs = New ADODB.Recordset
    sSql = "SELECT SourceDestCode, SourceDestTypeId FROM TempCollectables INNER JOIN SourceDest ON TempCollectables.SourceId = SourceDest.SourceId WHERE (SourceDest.SourceDestGroup ='" & "Intercompany" & "');"
    objRs.Open sSql, objConn, adOpenStatic, adLockOptimistic
    If Not objRs.EOF Then
        'objRs.MoveFirst
        Do While Not objRs.EOF[blue]
            strUpdate = "Update TempCollectables Set " _
            & " SomeFiled = 'abc', OtherFiled = 123 " _
            & " Where someotherf = 'Whatever'"
            objConn.Execute strUpdate[/blue][green]
            'objRs!SourceDestTypeId = objRs!SourceDestCode
            'objRs.Update[/green]
            objRs.MoveNext
        Loop
    End If
    Set objRs = Nothing

Have fun.

---- Andy

 

[doquigley]

Excellent Andy, Thank you so much for that

Queries within queries, where will it all end?