CODEWITHSUNDEEP
sqlexcelms-accessadodbvba
Jimbo052
Jimbo052

Reputation: 33

ADODB/Access: Update Multiple Fields from Record in Same Table

I'm using ADODB in Excel to query an Access database. I'd like to use a row in a table as a template for another row (that already exists) in the same table. I've tried several UPDATE queries, some without syntax errors, but I can't ever get the update to go through.

Assume the following table, Table1:

+--------------------------------------------+
| ID | Field 1 | Field 2 | Field 3 | Field 4 |
| 1  |    A    |    1    |    9    |    Z    |
| 2  |    B    |    2    |    8    |    Y    |
| 3  |    C    |    3    |    9    |    X    |
| 4  |    D    |    4    |    6    |    W    |
| 5  |    E    |    5    |    5    |    V    |
+--------------------------------------------+

I want to copy Field2, Field3, and Field4 from Row 2 to Row 5 so the table after the update will be:

+--------------------------------------------+
| ID | Field 1 | Field 2 | Field 3 | Field 4 |
| 1  |    A    |    1    |    9    |    Z    |
| 2  |    B    |    2    |    8    |    Y    |
| 3  |    C    |    3    |    9    |    X    |
| 4  |    D    |    4    |    6    |    W    |
| 5  |    E    |    2    |    8    |    Y    |
+--------------------------------------------+

I've tried these two queries without success. They both run without error, but the row does not get updated.

UPDATE Table1 A, Table1 B SET A.Field2=B.Field2, A.Field3=B.Field3, A.Field4=B.Field4 
WHERE A.ID=B.ID AND A.ID=5 AND B.ID=2

UPDATE Table1 A INNER JOIN Table1 B ON A.ID=B.ID
SET A.Field2=B.Field2, A.Field3=B.Field3, A.Field4=B.Field4
WHERE A.ID=5 AND B.ID=2

Of course, I could SELECT the values that I want and then do an UPDATE, but I would think there would be an easy way to do this in one query.

UPDATE: The problem was that I was using an ADODB command for multiple queries and I forgot to set it to a NEW ADODB.Command before doing this Update. I noticed this when I put the values in directly and specified Prepared=False and it worked. Below is the working code:

Dim NewCommand As New ADODB.Command
Set NewCommand = New ADODB.Command
With NewCommand
    Set .ActiveConnection = ExistingConnection
    .CommandText = "UPDATE Table1 A, Table1 B SET A.Field2=B.Field2, " _ 
        & "A.Field3=B.Field3, A.Field4=B.Field4 " _
        & "WHERE A.ID=B.ID AND A.ID=? AND B.ID=?"
    .Prepared = True: .CommandType = adCmdText
    .Parameters.Append .CreateParameter("IDA", adInteger, adParamInput, 4, 2)
    .Parameters.Append .CreateParameter("IDB", adInteger, adParamInput, 4, 5)
    .Execute
End With

Upvotes: 3

Views: 2569

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

Here is one way to do it:

UPDATE Table1, Table1 AS Table1_2 SET Table1_2.Field2 = [Table1]![Field2],
Table1_2.Field3= [Table1]![Field3], Table1_2.Field4= [Table1]![Field4]
WHERE (((Table1.ID)=2) AND ((Table1_2.ID)=5));

Upvotes: 3

Related Questions