SQL String does not retrieve Primary key newly added record

[ino_mart]

All

I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.



With code at the bottom I create a SQL-string which is stored in variable strSQL.

• If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
• If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.

Does someone know why I can't retrieve the Primary Key with VBA-code below?

Private Sub cmdSave_Click()
dim cn As ADODB.Connection
dim rs As ADODB.Recordset
 
set cn=New ADODB.Connection
  With cn
        .Provider = "sqloledb"
        .Properties("Data Source").Value = [I]"server"[/I]
        .Properties("Initial Catalog").Value = [I]"database"[/I]
        .Properties("user ID").Value = [I]"userid"[/I]
        .Properties("Password").Value = [I]"password"[/I]
        .Open
    End With
 
    Set rs = New ADODB.Recordset
    strSQL = "insert into tblPerson([firstname],[lastname]) values(" & _
        "'" & Me.txtFirstname & "','" & Me.txtLastname & "')" & _
        vbCrLf & " SELECT @@IDENTITY as [ID]"
    Debug.Print strSQL
    Set rs = cn.Execute(strSQL)
    [B]Debug.Print rs![ID][/B]
End Sub

 

[DavidAtWork]

It may be that "SELECT @@IDENTITY as [ID]" although this can be used through the SQL server query window, it does not work through the MS Access Environment.
You could try running the INSERT statement:
insert into tblPerson([firstname],[lastname]) values ....
and then run a separate

strSQL = "SELECT Max([ID]) as ID FROM tblPerson"
Set rs = db.OpenRecordset(strSQL)
Debug.Print rs.Fields(0)