I was previously getting the next available autonumber used in Access by doing a simple query like so:
SELECT RecordNumber, Info
FROM myTABLE
WHERE 0 = 1
This way I could create a variable to hold the currentRecord and it will use the same autonumber that Access was going to use when I was updating the row
Example
rs.AddNew
currentRecord = rs("RecordNumber")
rsInfo = "SomeFormData" & currentRecord
rs.Update
rs.Close
Now this use to work on MS Access, but in SQL Server 2005, I am not getting back the Identity created by the new record. "SomeFormData" is been inserted correctly, the RecordNumber field in SQL is been populated by the new auto number but I don't have the RecordNumber in my variables and I need it to continue filling related forms, that save data to related tables and need to save the currentRecord number.
Question: is there a way to get this unique number back when doing a new insert?
IDENT_CURRENT('tableName') (include the single quotes) returns the current value of the identity for the given table. This value should be the last-assigned identity value used in the table. In other words, you will have a row with this identity value already in the table, unless that row has been deleted. The identity value that will be assigned on the next INSERT will be IDENT_CURRENT('tableName') + IDENT_INCR('tableName').
I don't recommend relying on this, though. If you pre-determine the next identity value this way, you're bound to end up in a situation where another process makes the insert that actually gets that ID before yours does, so your process ends up using the wrong ID value.
It's much better to make your insert first (even if you don't have all the data yet), and use SCOPE_IDENTITY() to get the actual ID assigned.
You might wonder why SCOPE_IDENTITY() is better than IDENT_CURRENT('tableName'). As the name implies, the former will give you that most recent identity value assigned within your current scope (your batch, your stored proc, whatever), whereas the latter will give you the most recent identity assigned on the table, by anyone. Even though you might call IDENT_CURRENT right after 'INSERT, it's still possible that someone else's INSERT occurs in between, and IDENT_CURRENT will give you the identity value that resulted from their insert instead of yours, whereas SCOPE_IDENTITY will always give you yours.
EDIT:
It's also worth mentioning that SCOPE_IDENTITY() is favored over the similarly-functioning @@IDENTITY. While both return the latest identity value assigned within the current batch, @@IDENTITY is affected by inserts that occur within triggers; SCOPE_IDENTITY() is not.