Results 1 to 16 of 16

Thread: Retrieve Last Inserted ID in MS Access

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2014
    Posts
    27

    Retrieve Last Inserted ID in MS Access

    I'm using the following method to insert a record in MS Access 2007 through VB6

    vrs.addnew
    vrs("col1") = "Col1"
    vrs("Col2") = "col2" , etc

    The first column of the table is an autoincrement column. How to retrieve the ID of the newly inserted record.

    Note :
    In vb.net we use "Inserted.ID" method to retrieve just after insertion.

    vSqlText = "INSERT INTO Table1(Col1, Col2) OUTPUT INSERTED.ID " & _
    "VALUES (" & Col1) & _ etc

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Retrieve Last Inserted ID in MS Access

    have you tried "max id"?

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Retrieve Last Inserted ID in MS Access

    like:

    Code:
    select max(id) as maxid from tablename

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Retrieve Last Inserted ID in MS Access

    What you showed that you use in VB.Net is just a SQL statement and that same SQL statement should work in VB6 since VB is not what is processing the query.
    In the case of the VB6 code you are using a much different method.

    You could also try

    Code:
    "Select @@Identity"
    after your update

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2014
    Posts
    27

    Re: Retrieve Last Inserted ID in MS Access

    Datamiser : This is the method i came to know. But how to use it ? Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?

  6. #6
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Retrieve Last Inserted ID in MS Access

    akshobhya
    i suppose, what you really want to know is
    what autonumber is the system going to give to that new record
    have a look here it does what i think you really want
    http://www.vbforums.com/showthread.p...ber-(too-slow)
    edit:
    it is in post #16
    Last edited by IkkeEnGij; Aug 26th, 2014 at 03:29 PM. Reason: forgot to mention the post

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Retrieve Last Inserted ID in MS Access

    Quote Originally Posted by Akshobhya View Post
    Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?
    As far as I can recall the documentation suggests this value is cached within the Provider, separately for every Connection. I can't recall a case where assuming that has ever failed me.

    I can't provide a quote though because I can't find it right now.

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Retrieve Last Inserted ID in MS Access

    What will happen if two different people try to insert record into the same table from two different machines at the same time ?
    your code will have to account for that
    info:
    http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

    to remember:
    never and never and absolutely never give a user the possibility to start an addnew/edit and then go on a coffee break
    (and even more times never give him/her the possibility to do so if you choice to use pessimictic locking)

    the code i gave may seem to contradict that
    but remember i did not issue an update,so there never was a record added
    and to make absolutely sure there would be no lock, i explicitely used optimistic locking
    Last edited by IkkeEnGij; Aug 26th, 2014 at 04:43 PM. Reason: added /edit

  9. #9
    Addicted Member
    Join Date
    Jul 2014
    Posts
    176

    Re: Retrieve Last Inserted ID in MS Access

    Quote Originally Posted by Akshobhya View Post
    Datamiser : This is the method i came to know. But how to use it ? Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?
    IN addition to DataMisers Query:

    You can also use IDENT_CURRENT limited to the table specified.
    Code:
    SELECT IDENT_CURRENT(<TABLE NAME>) AS Identity
    If I had helped you...

    Don't forget to mark your Inquiry as RESOLVED...

    I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...

    Happy VB Coding Everyone!

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Apr 2014
    Posts
    27

    Re: Retrieve Last Inserted ID in MS Access

    Courtesy : http://www.vbforums.com/showthread.p...ord-just-added

    This way it seem to be so easy.

    Dim NewID As Long

    With vRs
    .AddNew
    .Fields("Col1").Value = "cols1"
    .Fields("Col2").Value = "cols2"
    .Update
    NewID = .Fields("ID").Value
    End With

    MsgBox NewID

    Any drawback by this method ? Experts please share with me....

  11. #11
    Addicted Member
    Join Date
    Jul 2014
    Posts
    176

    Re: Retrieve Last Inserted ID in MS Access

    Quote Originally Posted by Akshobhya View Post
    Courtesy : http://www.vbforums.com/showthread.p...ord-just-added

    This way it seem to be so easy.

    Dim NewID As Long

    With vRs
    .AddNew
    .Fields("Col1").Value = "cols1"
    .Fields("Col2").Value = "cols2"
    .Update
    NewID = .Fields("ID").Value
    End With

    MsgBox NewID

    Any drawback by this method ? Experts please share with me....
    Yes, you can use that code. ^_^

    Since it is an autonumber, there is no problem even if you delete the last identity.

    for e.g.
    ID
    1
    2
    3
    4
    5 (I DELETED THIS RECORD)

    next update:
    1
    2
    3
    4
    6
    If I had helped you...

    Don't forget to mark your Inquiry as RESOLVED...

    I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...

    Happy VB Coding Everyone!

  12. #12
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Retrieve Last Inserted ID in MS Access

    Any drawback by this method ?
    the drawback is,that code assumes it will succeed in adding a new record
    in a multiuser environment you can never assume an addnew/edit will succeed
    you should always have an error trap active (yes,i know you'r just giving an example)
    what is the difference between what you are doing, and what i did propose:
    that system will give you the number after the record was added
    my system will give you the number that the new record will get before it actually is added

    but the sad news is:
    nobody can really help you out,simply because nobody knows what you are actually doing
    are you using ado or dao,what kind of recordsets are you using,what kind of locking are you using
    Last edited by IkkeEnGij; Aug 31st, 2014 at 07:48 AM. Reason: deleted part that was not exactly correct (depended on a specific version)

  13. #13
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Retrieve Last Inserted ID in MS Access

    a word about autonumbers in access
    in the following i assume the autonumber is a long integer
    (so,it is not a replication-id,that are unique by definition)

    an autonumber is nothing more than a long integer
    as such it is only restricted to the range of a long integer
    so if you are adding new records you can give the autonumber field any value you want
    as long as it is in the range of a long integer
    you can give the same number to any number of records you want

    Except if there is a constraint on that autonumber field
    then it has to be in the range of an integer and comply to the constraint

    so if a record with autonumber 5 is deleted
    that number 5 can be reused
    Except if there is a constraint on that field that says the number 5 can not be used
    (but then,of course,it could not have been deleted,since it could never have been added to the table)

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Apr 2014
    Posts
    27

    Re: Retrieve Last Inserted ID in MS Access

    Here is my code (simple...)

    Dim vSql As String
    Dim vRs As New ADODB.Recordset

    vSql = "Select * from TableA where 1=2"
    Set vRs = New Recordset
    vRs.Open vSql, ConnStr, adOpenDynamic, adLockOptimistic

    with vRs
    'Fld updation codes
    end with
    Last edited by Akshobhya; Aug 27th, 2014 at 05:38 AM.

  15. #15
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Retrieve Last Inserted ID in MS Access

    Quote Originally Posted by Akshobhya View Post
    vSql = "Select * from TableA where 1=2"
    Is this thread resolved? Also, that query will never work because 1 will never equal 2.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Retrieve Last Inserted ID in MS Access

    Quote Originally Posted by Nightwalker83 View Post
    Is this thread resolved? Also, that query will never work because 1 will never equal 2.
    If you look at the other posts he is adding a record so there is nothing wrong with the query. It is intentionally returning an empty recordset which is fine even preferred if you are just wanting to add a record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width