Moving columns from one table to another 1

[garibal]

I am trying to move entire columns between two tables.
The first attempt:

dbsInWater.Execute _
"UPDATE StreamerTable SET StreamerTable.Weights = (SELECT Weights FROM StreamerVars)"

gives me a run-time error 3073
'Operation must use an updatable query'


Second attempt:

    Dim ASet As Recordset
    
' get the primary key of the 1st record in destination table
    Set ASet = dbsInWater.OpenRecordset("SELECT ID FROM StreamerTable")
    With ASet
        .MoveFirst
        j = !ID
        .Close
    End With
    
    Set ASet = dbsInWater.OpenRecordset("SELECT Weights FROM StreamerVars")  ''' Source table
    With ASet
        .MoveLast
        n = .RecordCount
        .MoveFirst
        For i = 1 To n
            If Not IsNull(!Weights) Then     
            dbsInWater.Execute _
"UPDATE StreamerTable SET [Weights] =" & ![Weights] & "" & _ "WHERE StreamerTable.[ID] = j"   
            End If
            j = j + 1
            .MoveNext
        Next
        .Close
    End With

The 'if Not Isnull' because the update query gives me a syntax error if the SET value is NULL.

The UPDATE stops with run-tim error 3061:
'Too few parameters. Expected 1.'

Where am I wrong?
Thanks for your replies.

 

[Remou]

You are missing a space and j need to be outside the quote:
[tt]"UPDATE StreamerTable SET [Weights] =" & ![Weights] & "" & _ [red]" [/red]WHERE StreamerTable.[ID] ="[red] & j[/red][/tt]

You may find that your original thought will work with DlookUp.

 

[garibal]

Hi Remou,

The missing space was just a typo trying to adapt the code to the post.
Placing the j outside the quote was the solution. Thank you again.

I have tried Dlookup earlier.

dbsInWater.Execute _
"UPDATE StreamerTable SET StreamerTable.Weights = DLookup("[Weights]", "StreamerVars")"

produce a syntax error or an 'Expected end of statement' on [Weights].

I still have more tricks for you, but one by one.

 

[Remou]

Adjust the quotes a little:

dbsInWater.Execute _
"UPDATE StreamerTable SET StreamerTable.Weights = " & DLookup("[Weights]", "StreamerVars")

 

[garibal]

That fills the entire destination table column with the first value in the source table column.

 

[Remou]

What did you want to do? If you wish to limit it, you need a Where:
[tt]dbsInWater.Execute _
"UPDATE StreamerTable SET StreamerTable.Weights = " & DLookup("[Weights]", "StreamerVars") & " Where ID=" & Me.ID[/tt]

 

[garibal]

dbsInWater.Execute _
"UPDATE StreamerTable SET StreamerTable.Weights = " & DLookup("[Weights]", "StreamerVars")

If the first record in the 'Weight' field from 'StreamerVars' is say 5, then the 'Weight' field in 'StreamerTable' is populated with 5s in all the records.
If it is 56, it is 56 all over in the destination.

That is not what I want to do.

Reading the DLookup help, I think it is normal because I have no criteria (and I do not want one, I want to copy the entire row).

 

[Remou]

The usual way to update an entire row is to have a join:
[tt]UPDATE StreamerTable INNER JOIN StreamerVars ON StreamerTable.ID = StreamerVars.ID SET StreamerTable.Weights = [StreamerVars].[Weights];[/tt]

You may wish to look at:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[Remou]

For 'entire row' please read 'entire column'.

 

[garibal]

I will start studying the relationships and joins.
I have worked on the 'for next' loop option and I have a tricky one to submit.
I need to copy two columns.

Code 1[tt]
For i = 1 To n

If Not IsNull(!Weights) Then
dbsInWater.Execute _
"UPDATE StreamerTable SET [Weights] =" & !Weights & " WHERE StreamerTable.[ID] =" & j
End If

If Not IsNull(!Comments) Then
dbsInWater.Execute _
"UPDATE StreamerTable SET [Comments] =" & !Comments & " WHERE StreamerTable.[ID] =" & j
End If

j = j + 1
.MoveNext

Next[/tt]

It stops on 'UPDATE Comment' with 'Too few parameters. Expected 1.

Code 2[tt]
If Not IsNull(!Weights) Then
dbsInWater.Execute _
"UPDATE StreamerTable SET [Weights] =" & !Weights & " WHERE StreamerTable.[ID] =" & j
End If

If Not IsNull(!Comments) Then
dbsInWater.Execute _
"UPDATE StreamerTable SET [Comments] ='[/color red]" & !Comments & "'[/color red] WHERE StreamerTable.[ID] =" & j[/tt]

It works !!??

Is there there a command to print the result of the SQL statement (what is actually sent to the Jet engine). I found 'Resppnse.Write', but that's for ASP.

 

[Remou]

Debug.Print for VBA.

You will find that you need hash marks (#) for dates. You do not need two statements. Try:

[tt]strSQL="UPDATE StreamerTable SET [Comments] ='" & Trim(!Comments & " ") & "', Weights = " & Nz(!Weights,0) & " WHERE StreamerTable.[ID] =" & j
Debug.Print strSQL
dbsInWater.Execute strSQL[/tt]

It is usually a good idea to build queries in the query design window. Relationships (joins) can be created by dragging a field in one table to a field in another.

 

[garibal]

So it was as simple as 'Debug.Print'. Shame on me.
Yes, the Nz function lets me move both columns in one statement (I use it on 'Weights' too because it contains also Null values).
It is worth a star.

Now that I have a neat for-next loop, I will try to harness the power updatable queries!