SQL Get Last Record Entered in Access

[epaisley]

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

[Karloff]

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,

[DaveMaxwell]

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.

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.

SELECT Max(TransactionID)
   FROM TransactionLog
 WHERE CustomerID = 1111

Hope this sets you off in the right direction. Good luck!

[DaveMaxwell]

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.

[Karloff]

Yep, Jet 4.0 introduced support for @@IDENTITY with Access.