SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Get Last Record Entered in Access

    Hello,

    I am looking to get the last record into a MS Access Database, immediately after it is entered. I need the primary key of that record to be passed along as a url variable. Is there a good way to do this besides querying on ever other attribute but the primary key (there is a date/time field that will also be fairly unique).

    Thanks

    Eric Paisley
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    Bangarang! Karloff's Avatar
    Join Date
    Mar 2003
    Location
    Manchester, United Kingdom
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use the @@IDENTITY function supported by MS Access and SQL Server. Along the lines of: lastRec = con.Execute("SELECT @@IDENTITY" )


    Edit: also have a look at How do I get the ID number of a just-inserted record? and Using IDENTITY.

    Hope that helps,
    Karl


    I'm desperately trying to figure out why Kamikaze pilots wore helmets. - George Carlin

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I'm assuming by primary key, you are talking about a autonumber which you're using for the primary key. I have to assume you have more than that to identify the table records.

    What you want to do is try to identify what would make that record unique w/o the autonumber field, and select based on that.
    Code:
    SELECT AutonumberField
       FROM TableName
     WHERE FieldName1 = 'xxx'
    	AND  FieldName2 = 111
    Now if you've got something like a transaction log where you'll have a lot of information with the same information except for a date, then you would do it somewhat different:

    Say you have a table like this:
    TransactionLog
    -----------
    TransactionID - PK/Autonumber
    CustomerID
    TransactionDate
    TransactionDesc

    Now in this case, you'd have a lot of records with the same customerID and you want the last transaction for that customer.
    Code:
    SELECT Max(TransactionID)
       FROM TransactionLog
     WHERE CustomerID = 1111
    Hope this sets you off in the right direction. Good luck!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Karloff
    Use the @@IDENTITY function supported by MS Access and SQL Server. Along the lines of: lastRec = con.Execute("SELECT @@IDENTITY" )

    Hope that helps,
    Does @@IDENTITY work with Access? Thought that was a stored proc SQL command anyways. Hmmmph. Didn't know that. I hate missing little tidbits of info like that.

    If this works, then definitely use this method. Much more effective than what I suggested.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    Bangarang! Karloff's Avatar
    Join Date
    Mar 2003
    Location
    Manchester, United Kingdom
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, Jet 4.0 introduced support for @@IDENTITY with Access.
    Karl


    I'm desperately trying to figure out why Kamikaze pilots wore helmets. - George Carlin


Bookmarks

Posting Permissions

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