Richard said:
I'm new to Access and was trying to find out how to retrieve the record
identity after an insert...
Some postings I read suggested using MAX() to retrieve the inserted record
id, but that's not reliable, because another user or session could have
inserted a record in the same table too...
Fo example, in MS SQL after an insert, there are 3 ways to retrieve the
newly generated identity of the inserted record:
1) IDENT_CURRENT - returns the most recently inserted record id in a table
regardless of which process or session inserted it.
2) @@IDENTITY - returns the most recently inserted record id in a table
regardless of which process inserted it, but inserted in the current
session.
3) SCOPE_IDENTITY - returns the most recently inserted record id in a
table
by the current process in the current session.
How can this be done in Access? Has Access an equivalent functionality for
each of these 3 SQL features?
Hi Rick,
I think folowing came from a reply by Michel:
*** retrieve Autonumber of previous record that was inserted (--must be same
connection --)
lngLastInsert = CurrentProject.Connection.Execute("SELECT
@@Identity").Fields(0).Value
///////////////////////////////////////////////////////////
Here be an old response from Steve Arbaugh
that I saved because it seemed to give a great
overview to this problem.
*******quote*****
There's no way to create a "return" value from a jet query to return the
auto number id. There's three approaches that generally are what you need
to consider:
1.) If you are using a simple append query (e.g. by doing an Execute), then
you need to run that query first and then run a select query that uses the
same primary key values from the append to fetch the autonumber.
2.) If you are using DAO and adding the record using .AddNew as part of a
recordset, then, you can use the bookmark and .LastModified methods
properties of the recordset to fetch the ID just added.
3.) If you are using ADO and the .AddNew method on a recordset, there's no
..LastModified property, you can fetch the ID by using the method outlined in
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;221931
However, in this last case, I would think that using the approach in
suggestion 1, would be faster, especially with large recordsets.
Another way to do it if using the .AddNew method on a Jet based ADO
recordset is set a field variable to the auto number field; which is
implicitly updated, even before the record is committed as shown below:
---------begin code-----------
Dim rsTest As ADODB.Recordset
Dim rsField As ADODB.Field
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Set rsTest = New ADODB.Recordset
rsTest.Open "Select * from Table2", conn, adOpenDynamic, adLockOptimistic
Set rsField = rsTest.Fields("ID") 'An auto number field
With rsTest
.AddNew
!Field1 = "New Test"
Debug.Print rsField.Value 'this is before the record is committed
.Update
End With
Debug.Print rsField.Value 'after record committed
Set rsField = Nothing
rsTest.Close
Set rsTest = Nothing
-------
HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
*****unquote*****
/////////////////////////////////////////////////////////////////////
In addition, I saved the following example
(but regretfully did not save the source....
my apologies to someone....)
****quote****
- @@Identity with SQL Server via output parameter from Stored Procedure
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim lRecordsAffected As Long
' Create and Open a new Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=sqloledb;" & _
"Server=(local);" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password=;"
' Create a new Command
Set oCmd = New ADODB.Command
With oCmd
.CommandType = adCmdStoredProc
.CommandText = "jobs_insert"
Set .ActiveConnection = oConn
' Create the Command's Parameters and set their values
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.Parameters.Append .CreateParameter("job_desc", adVarChar, adParamInput,
50, "Programmer")
.Parameters.Append .CreateParameter("min_lvl", adUnsignedTinyInt,
adParamInput, 0, 125)
.Parameters.Append .CreateParameter("max_lvl", adUnsignedTinyInt,
adParamInput, 0, 250)
.Parameters.Append .CreateParameter("job_id", adSmallInt,
adParamInputOutput, 0, 0)
' Run the command
.Execute lRecordsAffected, , adExecuteNoRecords ' Do not return a
recordset
' Print new identity value
Debug.Print .Parameters("job_id").Value ' New Identity
End With
Here is the corresponding Stored Procedure
CREATE Procedure jobs_insert
@job_desc varchar(50),
@min_lvl tinyint,
@max_lvl tinyint,
@job_id smallint OUTPUT
AS
INSERT INTO jobs
(
job_desc,
min_lvl,
max_lvl
)
VALUES
(
@job_desc,
@min_lvl,
@max_lvl
)
SELECT @job_id = @@IDENTITY
Go
Warning: if there is an "Insert" Trigger on your table, then @@Identity will
contain the Identity value for that Insert rather than for the one your
trying to insert via ADO code.
*****unquote****
/////////////////////
brief help from
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp
(this link may not work any longer)
*****quote****
The @@IDENTITY variable
The @@IDENTITY variable is a global SQL variable that you can use to
retrieve the last value used in a COUNTER data type column. You can't
specify a table name when retrieving the @@IDENTITY variable. The value
returned is always from the last table with a COUNTER field that had a new
record added to it from code. Use the SELECT statement to retrieve the
@@IDENTITY value.
SELECT @@IDENTITY
To add a value to the @@IDENTITY value, enclose the variable in square
brackets.
SELECT [@@IDENTITY] + 1
Note The @@IDENTITY variable listed in the previous SQL statements can be
executed only through the Jet OLE DB provider and ADO; it will result in a
value of 0 if used through the Access SQL View user interface. In addition,
the variable is set only when records are inserted through programming code.
If a record is inserted through the user interface, either with datasheets,
forms, or SQL statements in the Access SQL View window, the @@IDENTITY
variable will return 0. For this reason,
the value of @@IDENTITY is only accurate immediately after adding a record
from code.
*******unquote****
//////////////////////////// //////
(further responses that I have gathered)
///////////////////////////////
Use the following Visual Basic code
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("People")
rs.AddNew
MsgBox rs!person_id '<<<< after the AddNew, new pk is available?
rs!Name = "a person"
rs.Update
Fabian
///////////////////////////////
I use the following DAO code. It's my understanding that ADO doesn't
support this.
Frequently you have to check to see if a record in a table which
matches certain critieria exists and get its ID number. If it doesn't
exist you need to add it and then get the ID number. Use the
following code.
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("Customers", DB_OPEN_TABLE)
MyTable.AddNew
MyTable("Company Name") = "Finnegan's Foods"
MyTable.Update
CustomerID = MyTable("CustomerID")
MyTable.Close
MyDB.Close
Set MyTable = Nothing
Set MyDB = Nothing
Tony
----
Tony Toews, Microsoft Access MVP
////////////////////////////////////////
Hi Tony,
FWIW, this actually works fine in ADO, as long as you use a fairly
recent version and a server-side cursor, which is the default. I think
it may have been added in version 2.1. In both ADO and DAO, the
autonumber is available as soon as the record is dirtied (just as it
is on an Access form), so it can be obtained before the .Update,
without requiring the extra .Move.
Here's sample ADO code:
Dim rst As ADODB.Recordset
Dim lngEmpID as Long
Set rst = New ADODB.Recordset
With rst
.Source = "tblEmployee"
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51; " & _
"Data Source=" & App.Path &
"\Test.MDB"
.Open Options:=adCmdTable
.AddNew
.Fields("LastName") = "Smith"
lngEmpID = rst("EmployeeID")
.Update
.Close
End With
-- Andy
//////////////////////////////
Newsgroups: microsoft.public.access.formscoding
From: "Access Developer" <
[email protected]>
Date: Thu, 24 May 2001 09:05:06 -0700
Local: Thurs, May 24 2001 9:05 am
Subject: How do I get the auto-generated number of a record I just made in a
multi-user read/write environment?
You might try using a recordset object's AddNew method to insert a record
into
the parent table. After the .Update event the pointer is still on the newly
inserted record and you can then access the autonumber ID.
eg.
Dim rs as object
Dim lngID as Long
Set rs = CurrentDB.OpenRecordset("table")
rs.AddNew
rs("field1")=value
....
rs.Update
'Allen Browne
on't forget the new record may not automatically become the
current record. After rs.Update you need:
rs.Bookmark = rs.LastModified
lngID = rs("AutonumberID")
//////////////////////////////////////////
Newsgroups:
microsoft.public.access.forms,microsoft.public.access.queries,microsoft.public.access.gettingstarted
From: "Albert D. Kallal" <
[email protected]>
Date: Sun, 2 May 2004 13:38:49 -0600
the JET file share engine that comes with ms-access does not have an
identity feature.
It is NOT clear which data engine you are using? (there are two engines
that ship on the office cd..and one data engine is 100% compatible
with sql server..and does have identity..and also does support stored
procedures).
So...it is not clear which data engine you are using. If you are using the
file share JET engine...then you have to do a insert via code..and THEN
retrieve the id.
So, you can use the following:
Dim rstRecords As DAO.Recordset
Dim lngNext As Long
Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew
' code can go here to add data, or set values to the reocord...or, you
could just use this code to "grab"
' the id of the record added..and then use a update query (not an insert
query) to update your values.
rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext
So, you could even wrap the above in some public function like
"GetMyIdentity" and that would always give you the id of a newly added
record.
Note that if you are using JET..then right after the following command..you
can use/grab the id:
rstRecords.AddNew
However, if using odbc to sql server (linked table).then the record must be
written to disk first (so, you can not get the id right after addnew in that
case). However, the LastModifed code example will work for both jet, and sql
server (so, as a habit..you might as well adopted a coding standards that
works for both data engines).
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
/////////////////////////////////////////
maybe the above will help you...
good luck,
gary
