title: sql server: is there a way to get last record inserted without stored procedure?
body: <p>is there a way to get last record inserted without stored procedure in a multi-user environment?</p>
  
  <p>i am connected to sql server through vba. i insert some records and call:</p>
  
  <pre><code>SELECT SCOPE_IDENTITY()
  </code></pre>
  
  <p>this is not working. it is returning null every time. </p>
  
  <p>here is the entire code:</p>
  
  <pre><code>Dim sqlstring1 As String
  sqlstring1 = "delete from batchinfo where datapath='" &amp; dpath &amp; "' and analystname='" &amp; aname &amp; "' and reportname='" &amp; rname &amp; "' and batchstate='" &amp; bstate &amp; "'"
  
  
  
  Dim rowid_batchinfo As String
  rs.Filter = "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"
  If Not rs.EOF Then
      rowid_batchinfo = rs.Fields("rowid")
      cn.Execute "delete from batchinfo where rowid=" + rowid_batchinfo
      cn.Execute "delete from calibration where rowid='" + rowid_batchinfo + "'"
      cn.Execute "delete from qvalues where rowid='" + rowid_batchinfo + "'"
  End If
  
  
  With rs
      .AddNew ' create a new record
      ' add values to each field in the record
      .Fields("datapath") = dpath
      .Fields("analysistime") = atime
      .Fields("reporttime") = rtime
      .Fields("lastcalib") = lcalib
      .Fields("analystname") = aname
      .Fields("reportname") = rname
      .Fields("batchstate") = bstate
      .Fields("instrument") = instrument
      .Update ' stores the new record
  
  
  End With
  
  
  
  Set rs = cn.Execute("SELECT SCOPE_IDENTITY()", , adCmdText)
  
  Set rs = rs.NextRecordset
  </code></pre>
  
  <p><strong>the question is how do i get the last record inserted in a multi-user environment?</strong></p>