title: ADODB/Access: Update Multiple Fields from Record in Same Table
body: <p>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.</p>
  
  <p>Assume the following table, Table1:</p>
  
  <pre><code>+--------------------------------------------+
  | 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    |
  +--------------------------------------------+
  </code></pre>
  
  <p>I want to copy Field2, Field3, and Field4 from Row 2 to Row 5 so the table after the update will be:</p>
  
  <pre><code>+--------------------------------------------+
  | 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    |
  +--------------------------------------------+
  </code></pre>
  
  <p>I've tried these two queries without success. They both run without error, but the row does not get updated.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><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, " _ 
          &amp; "A.Field3=B.Field3, A.Field4=B.Field4 " _
          &amp; "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
  </code></pre>