dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Retrieving last record inserted into dB

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Retrieving last record inserted into dB

    [Originally posted by Joanna]

    Is there a way of retrieving the last record inserted into a database?

    I use the usual sqlstatement INSERT INTO myDeliveries .......
    and then use the Execute statement to process the query.

    Some background:
    When a new record is created in myDeliveries, MS Access auto allocates a unique random +ve or -ve integer for the ID field.

    Unfortunately there are instances where a number of records have all the same field values (other than their ID).

    I need to distinguish between these records so that I can retrieve the ID of the record that I last created.

    eg: I have just created 5 records to which Access allocated the following IDs: -32, 46, 23, 11, -15.

    The ID, -15, is the one I want to retrieve since it belongs to the last record I created.

    Is there something I can do (other than a schema change)?


  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Retrieving last record inserted into dB

    [Originally posted by PraveenMenon]

    If u r using Access2000 and u have not so old provider, i've heard u can use "SELECT @@identity as AutoID" to retrive the info uare looking for..

    If the db is not that large, and the transaction are not that frequent, i wud suggest add another autonumber field, that will increment each time a row is inserted. Now once u have such a field, u can take max(AutoNumFld) to get the last row inserted.

    Pls let me know if it helped..
    All the best
    Praveen Menon

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Retrieving last record inserted into dB

    [Originally posted by Ed Ardzinski]

    @@Identity will help if this is the only table inserted. As I recall this variable can be troublesome.

    Autonumbering is a better solution, so at least the value of the ID field can give a rough idea of where/when the record was added. Another field with the date/time of the insertion can further help identify specific records.

    If multiple users can be running this process then you could even try to capture the user ID (from the computer logon or maybe an internal table of users) to give even more detail to the record...

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Retrieving last record inserted into dB

    [Originally posted by kenneth G]

    You could also try to give your db an date/time field and thereby order the information by this parameter.. I belive "select top 1 *" also could maybe help you..

    Just thougths anyway.. :))

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Retrieving last record inserted into dB

    [Originally posted by PraveenMenon]

    Absolutely Right Ed, @@identity is a bit troublesome when u r lookin in to a multiple user and a large database app.. that is exactly why i also provided information on creating a field with the autonumbering stuff.

    This can also turn out to a bit kinda like a bad idea sometimes, cos when multiple users are trying to insert into the database in a single time, max(fieldnumber) can be wrong. Also, since u cant give an autonumber field to have anything other than numeric data, u have to look in to other methods of locking mechanisms available with the ADO, to implement such an idea.

    But the scope of discussion is broadening...

    Nice post Ed..
    Praveen Menon

  6. #6
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Retrieving last record inserted into dB

    [Originally posted by PraveenMenon]

    But there are other ways of doing this in SQL Server.. I wonder whether they are available in the latest version of MS Access??

    IDENT_CURRENT which returns the last identity value generated for a specific table in any session and any scope.
    Usage : IDENT_CURRENT('table_name')

    SCOPE_IDENTITY which returns the last identity value generated for any table in the current session and the current scope.

  7. #7
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Retrieving last record inserted into dB

    [Originally posted by Joanna]

    Thanks guys. As my access dB is an older version I am unable to use @@identity. However, a solution has been found to my problem...

    With AddNew I can bookmark the record last modified for later access in my program.
    eg: rs.Bookmark = rs.LastModified

    Thanks for your all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center