none
Retrieve Primary Key of Inserted Record into SQL Server DB Using DAO

    Question

  • Along time ago, I use code similar to this to insert a record and to retrieve the primary key of the newly create record. However, its coming back Null. I am now using SQL Server, do I have to use ADO to inser the record? I need the value of the primary key returned which is why I'm not using a INSERT statement.   

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
       
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblTrailerUtilizationDetails", dbOpenDynaset, dbSeeChanges)
       
        With rs
            .AddNew
            .Fields("lngTrailerActivityHeaderId") = lngTrailerActivityHeaderId
            .Fields("txtShowNumber") = txtShowNumber
            .Fields("lngTrailerLoadTypeId") = lngTrailerLoadTypeId
            createChildRecord = .Fields("lngTrailerUtilizationDetailsId")
            .Update
        End With
       
        rs.Close
       
        Set rs = Nothing
        Set db = Nothing


    David H
    Thursday, July 08, 2010 6:55 PM

Answers

  • Yes, the above is a reliable and safe way to get that last ID. If you look close, that last modified is a BOOKMARK property, not some time based var. It is not going to return a record updated by another user, or in fact even return another ID due to a different recordset opened in your code even to the SAME table. So, it only pertains to the active recordset in question

     

    And, more important, the same code as posted works for both JET and JET + sql server.

     

    I mean, the following code both produces the last ID in JET or JET + sql server

     

       rstRecords.AddNew

       rstRecords!FAXNAME = "test"

       rstRecords.Update

      

       Debug.Print "Last autonumber id = " & _

                   CurrentDb.OpenRecordset("select @@identity from DBO_faxbook")(0)

      

       rstRecords.Bookmark = rstRecords.LastModified

       lngNext = rstRecords!ID

       Debug.Print "last autonumber id = " & lngNext

     

     

    So, if you ran the above code, the above would run just fine even when not using sql server.  As mentioned, it is simple matter that the record update must occur for sql server to generate the autonumber ID (this is also why when focues moves from main form to sub form, the reocrd is saved).

     

    The quirk or issue here is that WHEN ADDING records, DAO does move the record pointer off the record you just added when you do the update. ADO does not move the record pointer, so using ADO would mean that the above code would work, but you could remove the one line of code that sets/moves the record pointer back via the bookmark code.

     

    Note how in the above the select @@idenitity is not even using the same reocrdset and same instance of the database connection to get the last ID (note again, the @@identity works for JET (without sql server) or with JET + sql server).

     

    You could use the @@identity, but I think you better off to use the bookmark, and the reason being that once you set the record pointer back using the bookmark, then EXISTING code that follows and possible uses values from the reocrdset, or even references the recordSet!ID will work as before. So, I think from a compatibility point of view, the LastModified methoed of the recordset is a better choice then the @@identity.

     

    Note that the quirk of the record beign moved "off" the record just created applies to DAO. However, we never were forced in JET only code to execute the update and THEN perhaps use values from that reocrdset. So, LastModified is really good choice since it will result in less of your code breaking if there was/will be any code that follows the DAO.UPDATE that still wants to grab and use values from that reocrdset.

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by David Holley Friday, July 09, 2010 12:42 PM
    Friday, July 09, 2010 4:32 AM

All replies

  • You have to execute the update to allow sql server to generate the autonumber.

     

    So, the code becomes like:

     

       rstRecords.Update

       rstRecords.Bookmark = rstRecords.LastModified

       lngNewIDJustcreated = rstRecords!ID

     

       Now code goes here that can use above ID.

     

    Note that you don't need the bookmark to "move" the record pointer back to a just updated record, you ONLY need do above when creating a NEW record.

    So, the DAO Update does not move the record pointer for existing records, but for new record it does, and you have to re-position the record back as per above.

     

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    Thursday, July 08, 2010 8:28 PM
  • But is .LastModified a reliable means of getting the last record? I thought about .MoveFirst/.MoveLast and then realized that they would only work if you have the table locked. Otherwise what's to prevent a new record from being inserted in the time between executing the .Update and moving to .LastModified. Slim to nill that it would happen but entirely possible.

    (Which means stored procedure comes to mind...)


    David H

    Thursday, July 08, 2010 8:32 PM
  • Yes, the above is a reliable and safe way to get that last ID. If you look close, that last modified is a BOOKMARK property, not some time based var. It is not going to return a record updated by another user, or in fact even return another ID due to a different recordset opened in your code even to the SAME table. So, it only pertains to the active recordset in question

     

    And, more important, the same code as posted works for both JET and JET + sql server.

     

    I mean, the following code both produces the last ID in JET or JET + sql server

     

       rstRecords.AddNew

       rstRecords!FAXNAME = "test"

       rstRecords.Update

      

       Debug.Print "Last autonumber id = " & _

                   CurrentDb.OpenRecordset("select @@identity from DBO_faxbook")(0)

      

       rstRecords.Bookmark = rstRecords.LastModified

       lngNext = rstRecords!ID

       Debug.Print "last autonumber id = " & lngNext

     

     

    So, if you ran the above code, the above would run just fine even when not using sql server.  As mentioned, it is simple matter that the record update must occur for sql server to generate the autonumber ID (this is also why when focues moves from main form to sub form, the reocrd is saved).

     

    The quirk or issue here is that WHEN ADDING records, DAO does move the record pointer off the record you just added when you do the update. ADO does not move the record pointer, so using ADO would mean that the above code would work, but you could remove the one line of code that sets/moves the record pointer back via the bookmark code.

     

    Note how in the above the select @@idenitity is not even using the same reocrdset and same instance of the database connection to get the last ID (note again, the @@identity works for JET (without sql server) or with JET + sql server).

     

    You could use the @@identity, but I think you better off to use the bookmark, and the reason being that once you set the record pointer back using the bookmark, then EXISTING code that follows and possible uses values from the reocrdset, or even references the recordSet!ID will work as before. So, I think from a compatibility point of view, the LastModified methoed of the recordset is a better choice then the @@identity.

     

    Note that the quirk of the record beign moved "off" the record just created applies to DAO. However, we never were forced in JET only code to execute the update and THEN perhaps use values from that reocrdset. So, LastModified is really good choice since it will result in less of your code breaking if there was/will be any code that follows the DAO.UPDATE that still wants to grab and use values from that reocrdset.

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by David Holley Friday, July 09, 2010 12:42 PM
    Friday, July 09, 2010 4:32 AM
  • Right because the bookmark is pointing to a specific record.

    Thx


    David H
    Friday, July 09, 2010 12:42 PM