locked
Get Autonumber After .addnew Using Dao RRS feed

  • Question

  • I have a code in DAO that connects to a linked table in SQL Server 2008. I need to get the newly created auto number on .AddNew.
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("AuditTrail") 
     
    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    rs.Update 
     
    rs.Close
    

     If I use rs("AuditTrailID") before rs.close, it returns 1 (the first entry).

    • Edited by Rick131 Wednesday, January 18, 2012 11:08 AM
    Tuesday, January 17, 2012 8:08 PM

Answers

  • I have a code in DAO that connects to a linked table in SQL Server 2008. I need to get the newly created auto number on .AddNew.
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("AuditTrail") 
     
    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    rs.Update 
     
    rs.Close
    
     If I use rs("AuditTrailID") before rs.close, it returns 1 (the first entry).

    I can't test at the moment, but does this work?

    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    rs.Update 
    
    rs.Bookmark = rs.LastModified
    lngID = rs("AuditTrailID")
    
    


     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Rick131 Wednesday, January 18, 2012 1:00 PM
    Tuesday, January 17, 2012 8:48 PM

All replies

  • Tye getting the AutoNumber field just before .Update:

     

     

    Dim lngPK As Long
    
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("AuditTrail") 
     
    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    lngPK = rs("AuditTrailID")
    rs.Update 
     
    rs.Close

     

    Oops, sorry - I didn't read carefully. The above will work with Access tables but not with SQL Server tables. My apologies!


    Regards, Hans Vogelaar
    Tuesday, January 17, 2012 8:13 PM
  • Returns Null

    Tye getting the AutoNumber field just before .Update:

     

     

    Dim lngPK As Long
    
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("AuditTrail") 
     
    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    lngPK = rs("AuditTrailID")
    rs.Update 
     
    rs.Close

     


    Regards, Hans Vogelaar

    Tuesday, January 17, 2012 8:21 PM
  • I have a code in DAO that connects to a linked table in SQL Server 2008. I need to get the newly created auto number on .AddNew.
    Set db = CurrentDb 
    Set rs = db.OpenRecordset("AuditTrail") 
     
    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    rs.Update 
     
    rs.Close
    
     If I use rs("AuditTrailID") before rs.close, it returns 1 (the first entry).

    I can't test at the moment, but does this work?

    rs.AddNew 
    rs("ActionID") = actionAdd 
    rs("dtDateTime") = Now() 
    rs("Comment") = Nz(comment, "") 
    rs("UserID") = UserIDName 
    rs.Update 
    
    rs.Bookmark = rs.LastModified
    lngID = rs("AuditTrailID")
    
    


     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Rick131 Wednesday, January 18, 2012 1:00 PM
    Tuesday, January 17, 2012 8:48 PM
  • For some reason I get the error: "Record is deleted".
    Tuesday, January 17, 2012 11:16 PM
  • With DAO, after you update the new record, you are back to where you were before inserting the record. Like Hans mentionned it, you can get the autonumber generated value, though, after the AddNew  (and before the Update).
    Tuesday, January 17, 2012 11:50 PM
  • You can insert the value of the autonumber column yourself with Access tables using DAO, though I'm not sure about SQL Server.  If not you may be able to do so by executing an INSERT INTO statement.  With DAO you'd first get the next value:

    Dim lngAuditTrailID As Long

    lngAuditTrailID = Nz(DMax("AuditTrail", "Contacts"), 0) + 1

    You can then insert the value with:

    rs("AuditTrailID") = lngAuditTrailID

    So the lngAuditTrailID variable will hold the newly inserted value.  There is the slight risk of a conflict if two or more users are inserting a row simultaneously, so it would be advisable to trap the error and increment the value of the variable by 1 again, then Resume at the rs.AddNew line.

    Ken Sheridan, Stafford, England
    Wednesday, January 18, 2012 12:16 AM
  • For some reason I get the error: "Record is deleted".

    Does the table have a primary key, and does Access know about it?  Does the table have a timestamp/rowversion column?
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, January 18, 2012 1:11 AM
  • Like Hans mentionned it, you can get the autonumber generated value, though, after the AddNew  (and before the Update).

    Not, I think, with a SQL Server back-end.
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, January 18, 2012 1:12 AM
  • Does the table have a primary key, and does Access know about it?  Does the table have a timestamp/rowversion column?

    I'm afraid it doesn't because if there is a primary key the OP should get an error at this line:

    Set rs = db.OpenRecordset("AuditTrail") 
    

    just because dbSeeChanges is missed. And I think it should be

    Set rs = CurrentDb.OpenRecordset("AuditTrail", dbOpenDynaset, dbSeeChanges)
    

    After these changes your rs.Bookmark trick, Dirk, works as expected.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, January 18, 2012 6:44 AM
  • Found the problem. I had set the Primary key as bigint datatype in SQL Server. Apparently Access can't interpret big int correctly. I changed it back to int.

    Thanks!

    Wednesday, January 18, 2012 1:02 PM
  • You might might be able to get it by a bit of code like this:

     intAutomberIWant= !AutoNumberFieldName ....

    You can  use the @@Identity feature in SQLServer.    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/912e4485-683c-41c2-97b3-8831c0289ee4.htm

    You should create a SQL Server stored procedure or function that runs an insert statement and returns the key you want using @@Identity. 

     

     

     

     


    PG A bit of experimentation by trial and error often helps.
    Wednesday, January 18, 2012 1:34 PM
  • Found the problem. I had set the Primary key as bigint datatype in SQL Server. Apparently Access can't interpret big int correctly. I changed it back to int.


    Ah.  Yes, that's known problem with the bigint datatype.  I haven't had to deal with it yet, fortunately, but have read about it.
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, January 18, 2012 5:07 PM
  • That work (for me) with MS Access tables - thank you
    Tuesday, December 22, 2020 1:33 PM
  • +1!!

    And as a FYI to all readers?

    The one qurik, the ONE wart in DAO?

    When you do a addnew (insert) using DAO, and THEN execute the UPDATE?

    The record pointer is moved. This ONLY occurs for a DAO insert + update. If you "edit" a existing record, then the update does NOT blow out the record position.

    The above holds true for access back ends, and for sql server back ends. Of course the other difference is you can't pick up the new autonumber ID inside the code like we can with access BE's, but have to execute the update, and then move the record pointer back. But this "moving" of the record pointer ALSO occurs for even Access BE, and this ONLY occurs when adding. ADO does not have this qurik (wart).

    So as a caution and FYI? A DAO insert then update will blow out the recordpointer position - you HAVE to move it back - and do so for SQL server or even Access back ends. And in this case, since we can't pick up the autonumber ID for sql server (or MySQL or any server based system), then we need that all important update, and as noted, in ALL cases, DAO blows up the record position on inserts - so we not only need to update to get the autonumber in this case, but we also have to move the record pointer back to the record before we pick up the autonumber id, or ANY colum for that matter!!

    Again, this quirk occurs even when not using sql server.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Wednesday, December 23, 2020 10:18 PM