Select @@Identity

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

Hallo everybody,
I have an Access 2000 database, when I execute the query "Select @@IDENTITY"
it return always 0.
What did I do wrong ?

thanks in advance
 
@@Identity is a SQL Server statement, which is useless in Access (unless
your back-end is SQL Server).

Do you execute this in conjunction with an insert statement?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
I believe this was one of a handful of new features of JET 4 that are only
available via ADO.

Public Function TestIdentity() As Long

Dim rst As ADODB.Recordset

CurrentProject.Connection.Execute "INSERT INTO Table1 (TestDec) VALUES
(1.2)"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT @@IDENTITY AS TheID"
.Open
TestIdentity = .Fields(0)
.Close
End With

End Function
 
Thanks!

Reprinted without even bothering to ask for permission:

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.
For more information about data types, type Jet SQL data types in the Office
Assistant or on the Answer Wizard tab in the Microsoft Access Help window,
and then click Search.

Hopefully this will assist the original poster as well....


--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Back
Top