title: Access SQL: Get the identity value of the affected record from an insert statement Or From a DAO.QueryDef
body: <p>I have to convert some SQL queries to access SQL. Some of them use @@identity to insert two linked records into two different tables. Unfortunately, an access query can only contain one statement(i.e. I can not select @@identity after the insert statement). </p>
  
  <p>This is not possible:</p>
  
  <pre><code>insert into table1 (values)
    select values from table1 where id=@id;
  select @@identity;
  </code></pre>
  
  <p>This example statement is supposed to Copy a record from Table1 to a new record in the said table and then do the same in Table2, referencing the new record from Table1.</p>
  
  <p>Here's what the sql query looks like:</p>
  
  <pre><code>        INSERT INTO Table1 ( some_value, the_other_values)
          SELECT  @new_value as some_value, the_other_values
          FROM    Table1
          WHERE   id = @oldID
  
          SELECT @New_id = @@identity
  
          INSERT INTO     Table2 ( idP, other_values)
              SELECT  @New_id AS idP, other_values
              FROM    Table2
              WHERE   (idP = @Old_id)
  </code></pre>
  
  <p>I tried to do this in two statements, using vba, but I have found no way to access @@identity from the other query. </p>
  
  <p>Here's the vba - I omitted the declarations, the actual code is a loop but this is the important part:</p>
  
  <pre><code>Set qdf = CurrentDb.QueryDefs("first_part_of_query")
          qdf!New_id = userInput_newID
          qdf!oldID = userInput_oldID
          qdf.Execute
          Set qdf = CurrentDb.QueryDefs("second_part_of_query")
          qdf!New_id = the_identityvalue 'WHAT DO I SET THIS TO?
          qdf.Execute
  </code></pre>