SQL Server 2000 - Get Most Recent Record ID

If you're like me, you've probably wanted to get the record id of the record you just inserted. There are a few different methods of doing so depending on what database you're using, but this one is specific to SQL Server 2000. You'll have to try it yourself to see if it works on other databases. I THINK it works on Access 2000 or higher, but didn't test it. Should also work on SQL Server 2005 and 2005 Express Edition.

When you call a SQL statement, you can call multiple statements separated by a semicolon. The following is an example of a SQL statement that inserts a new record and then gets the ID assigned to that record, be it an autonumber or guid:

INSERT INTO TheTable (Field1, Field2) VALUES ('Value1','Value2'); SELECT @@IDENTITY AS NewestID

An example of how to use this would be (this assumes you've already set up your database connection and named it dbConn):

rID = ""
strSQL = ""

strSQL = "INSERT INTO TheTable (Field1, Field2) VALUES ('Value1','Value2'); SELECT @@IDENTITY AS NewestID"
set rs = dbConn.Execute(strSQL)
rID = rs("NewestID")

Now you have the record ID of your newest record in a variable!