title: How to get id of newly inserted record using Excel VBA?
body: <p>Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however).</p>
  
  <p>I currently insert my new record then run a select query using (I hope) enough fields to guarantee that only the newly inserted record can be returned.  My databases are rarely accessed by more than one person at a time (negligible risk of another insert happening between queries) and due to the structure of the tables, identifying the new record is normally pretty easy.</p>
  
  <p>I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key.  This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.</p>
  
  <p>What's the best way to insert a record into an Access table then query the new primary key from Excel in this situation?</p>
  
  <p>Thanks for the replies.  I have tried to get <code>@@IDENTITY</code> working, but this always returns 0 using the code below.</p>
  
  <pre><code>Private Sub getIdentityTest()
      Dim myRecordset As New ADODB.Recordset
      Dim SQL As String, SQL2 As String
  
      SQL = "INSERT INTO tblTasks (discipline,task,owner,unit,minutes) VALUES (""testDisc3-3"",""testTask"",""testOwner"",""testUnit"",1);"
      SQL2 = "SELECT @@identity AS NewID FROM tblTasks;"
  
      If databaseConnection Is Nothing Then
          createDBConnection
      End If
  
      With databaseConnection
          .Open dbConnectionString
          .Execute (SQL)
          .Close
      End With
  
      myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly
  
      Debug.Print myRecordset.Fields("NewID")
  
      myRecordset.Close
  
      Set myRecordset = Nothing
  End Sub
  </code></pre>
  
  <p>Anything stand out being responsible?</p>
  
  <p>However, given the caveats helpfully supplied by Renaud (below) there seems nearly as much risk with using <code>@@IDENTITY</code> as with any other method, so I've resorted to using <code>SELECT MAX</code> for now.  For future reference though I would be interested to see what is wrong with my attempt above.</p>