|
|
by Remas Wojciechowski
|
Identity or Autonumber fields are automatically incrementing fields.
In some cases you want to retrieve the value of such a field directly after inserting a new record.
This article describes how to do that.
There are basically three different solutions. However, if you use an ODBC for Access, only Solution 3 applies.
|
|
This is how the table for the examples to follow is built up:
| ID |
Name |
Email |
| <Identity> |
<Varchar> |
<Varchar> |
|
|
|
Note All examples assume that
- a Connection object (objCon) is instatiated and open,
- a Recordset object (objRS) is instantiated, and
- the adovbs.inc file is included.
The sample code bits are designed to store the value of the new ID in the varNewID variable.
Solution 1: SELECT @@Identity
In this scenario, you execute SELECT @@Identity statement directly after the INSERT statement using the same connection.
The SQL system variable @@Identity is different for every connection.
This solution will work for SQLServer (ODBC and OLEDB) and Access (OLEDB).
|
|
|
strSQL = "INSERT INTO tblSample (Name,Email)"
strSQL = strSQL & "VALUES ('Remas Wojciechowski','remasw@yahoo.com')"
objCon.Execute(strSQL)
strSQL = "SELECT @@Identity FROM tblSample"
Set objRS = objCon.Execute(strSQL)
varNewID = objRS(0)
|
|
|
|
Solution 2: Nifty use of AddNew
The following code uses the AddNew method of the Recordset object to insert the new record.
Once the Update method is called, the adequate field will hold the new Identity value.
This solution will work for SQLServer (ODBC and OLEDB) and Access (OLEDB).
Note The cursor has to be on the client side!
|
|
|
strSQL = "SELECT (ID,Name,Email) FROM tblSample"
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objCon, adOpenStatic, adLockOptimistic, adCmdText
objRS.AddNew
objRS("Name") = "Remas Wojciechowski"
objRS("Email") = "remasw@yahoo.com"
objRS.Update
varNewID = rs(0)
|
|
|
|
Solution 3: Burdensome... but works with Access ODBC
You will have to resort to this solution if you're connecting to Access via ODBC.
It uses the AbsolutePosition property of the Recordset object to "memorize" the
position of the new record, then refreshes the object (w/ Requery method) and
then uses the saved position to access the record.
This solution will work for both SQLServer and Access.
|
|
|
strSQL = "SELECT (ID,Name,Email) FROM tblSample"
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objCon, adOpenStatic, adLockOptimistic, adCmdText
objRS.AddNew
objRS("Name") = "Remas Wojciechowski"
objRS("Email") = "remasw@yahoo.com"
objRS.Update
varBookmark = objRS.AbsolutePosition
objRS.Requery
objRS.absolutePosition = varBookmark
varNewID = rs("ID")
|
|
|
|