@@IDENTITY query?

[djc]

I saw on a newsgroup somewhere to use the Select @@identiy query to retrieve
the value of the last autonumber ID field added in a table...

I tried SELECT @@IDENTITY FROM Tablename
and several records returned with on field.. all values were 0?

can this be used in access? if so, what is the correct syntax? (I'm using
Access 2002)

any info is appreciated, thanks.

 

[Allen Browne]

Here is an example:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[Lynn Trapp]

I tried SELECT @@IDENTITY FROM Tablename

and several records returned with on field.. all values were 0?

can this be used in access? if so, what is the correct syntax? (I'm using
Access 2002)

That select statement will always return 0 in a select query. However, if
you write a function like the one below from MVP Allen Browne you can
retrieve the last AutoNumber value inserted.

Function ShowIdentity()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO Company ( [CompanyName] ) SELECT 'MyCompany' AS
Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[Lynn Trapp]

Ah, you beat me to it, Allen. Actually, that's really cool stuff.

 

[djc]

thank you.

Here is an example:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The @@IDENTITY is a function in MS SQL Server, only. It is not
available in Access/JET dbs. If you are using a recordset to
save/update records you can use the .LastModified property of the
recordset. E.g.:

dim rs as dao.recordset
.... assign rs & open ...

with rs
.addnew
' ... assign column values
.update
' Move current record pointer to the most recently
' changed or added record.
.Bookmark = .LastModified
debug.print !autonumber_column
end with

!autonumber_column is a reference to the AutoNumber column in the JET
table, it can have any name you want (as long as it conforms to the
Access naming conventions). AutoNumbers are, basically, the same thing
as an Identity column in a SQL server table.

For more info, read the Access VBA Help on LastModified and AutoNumbers.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTZPAoechKqOuFEgEQIXOACfQVF5e4arGD9k36KZrj+oqDuZ5IAAoO4X
3CUT0CP2mYWt594ezyrrNQS9
=9sRC
-----END PGP SIGNATURE-----

 

[Allen Browne]

"MGFoster" replied

The @@IDENTITY is a function in MS SQL Server, only.

I only recently learned that @@IDENTITY does work in JET 4 also. Try the
example in another reply to this thread.

 

[djc]

this post was actually a 'sub-post' to my bigger issue... maybe one of you
could shed some more light on this for me. It is ado.net related.

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

the above link is the only answer I have so far. This is my response after
reading it:
I must say I am shocked at how complex a task this seems. It should be so
simple, and was so simple in Access. I can only assume the difference is due
to ado.net being 'disconnected'. What I need to do is so common: insert a
record in one table, store the ID that was just used, use that ID to create
related records in a different table.

I am very frustrated and disappointed. With all the power offered by
asp.net/ado.net why do I have to write pages and pages of code for what
should not even be one page worth?

-------and this is the original post with my issue/questions:
2 tables. The ID field in table1 is primary key in table1 and foriegn key in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
..Update
ID = .Fields("ImageID")
..Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in the
sample code will work anymore. I have also never used the @@identity method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.

 

[Brendan Reynolds]

Assuming that the table on the one side of the relationship has a unique
index on one or more fields other than the AutoNumber or Identity field, I'd
just store the values of those fields and then use them as the criteria to
retrieve the AutoNumber or Identity field. I've been away from any .NET
stuff for a while, and I'm a bit rusty, so I'll give an example using VBA,
but the technique is basically the same, only the syntax changes. Of course,
if the AutoNumber or Identify field is the only unique index, then things
are going to get a lot more complicated, but that's a consequence of the
database design.

Public Sub GetTheAutoNumber()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngTheAutoNumber As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Products")
With rst
.AddNew
.Fields("ProductName") = "My Test Product"
.Update
.Close
End With
Set rst = db.OpenRecordset("SELECT ProductID FROM Products WHERE
ProductName = 'My Test Product'")
lngTheAutoNumber = rst.Fields("ProductID")
rst.Close

MsgBox "The value to use in the foreign key field is: " &
CStr(lngTheAutoNumber)

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

this post was actually a 'sub-post' to my bigger issue... maybe one of you
could shed some more light on this for me. It is ado.net related.

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

the above link is the only answer I have so far. This is my response after
reading it:
I must say I am shocked at how complex a task this seems. It should be so
simple, and was so simple in Access. I can only assume the difference is due
to ado.net being 'disconnected'. What I need to do is so common: insert a
record in one table, store the ID that was just used, use that ID to create
related records in a different table.

I am very frustrated and disappointed. With all the power offered by
asp.net/ado.net why do I have to write pages and pages of code for what
should not even be one page worth?

-------and this is the original post with my issue/questions:
2 tables. The ID field in table1 is primary key in table1 and foriegn key in
table2. In older MS access apps I used something like the following for
handling the need to have the autonumber field automatically generated by
Access to create the next set of records in a related table.
-----------------------------------------------------------
With rst 'table one
.AddNew
.Fields("Name") = UCase(Trim(Me.txtImageName))
.Fields("Sysprepped") = Trim(Me.cboSysprepped)
.Fields("MachineType") = UCase(Trim(Me.txtMachineType))
.Fields("TechID") = UCase(Trim(Me.cboTechID))
.Fields("OSID") = Trim(Me.cboOSID)
.Fields("SPLevel") = Trim(Me.txtSPLevel)
.Fields("DateCreated") = Trim(Me.txtDateCreated)
.Fields("Notes") = Trim(Me.txtNotes)
.Update
ID = .Fields("ImageID") 'here is where I snag the auto ID
number field
.Close
End With

sql = "SELECT * FROM [tblImage-Software] WHERE
[tblImage-Software.ImageID] = 0"
rst.Open sql, options:=adCmdText

i = 0 'reinitialize
With rst 'table two
For i = 0 To lstInstalledSoftware.ListCount - 1 'clear selections
from source lst box
.AddNew
.Fields("ImageID") = ID 'Here is where I use the previously
snagged ID
.Fields("SoftwareID") = lstInstalledSoftware.ItemData(i)
.Update
Next i
.Close
End With
----------------------------------------------------------------------

This part:
.Update
ID = .Fields("ImageID")
.Close

is where I stored the autonumber id field from that record to use later in
this code to enter several records into a seperate related table.

1) can this be done with ado.net in the same way? is so, is it ok
(recommended) to do it this way?
1a) could someone give me a short example using an ado.net dataset?

2) is using the select @@identity query a better way?

I know ado.net is 'disconnected' so I don't know if the method shown in the
sample code will work anymore. I have also never used the @@identity method
and don't know if it is recommended/reliable since several users could be
adding records at the same time... how do you know you got yours? Aside from
really needing answers to 1) and 2) above, I am wondering if the same
applies to sql server identity columns?

any info is appreciated. Thanks.

------------------
any enlightement is greatly appreciated. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The @@IDENTITY is a function in MS SQL Server, only. It is not
available in Access/JET dbs. If you are using a recordset to
save/update records you can use the .LastModified property of the
recordset. E.g.:

dim rs as dao.recordset
... assign rs & open ...

with rs
.addnew
' ... assign column values
.update
' Move current record pointer to the most recently
' changed or added record.
.Bookmark = .LastModified
debug.print !autonumber_column
end with

!autonumber_column is a reference to the AutoNumber column in the JET
table, it can have any name you want (as long as it conforms to the
Access naming conventions). AutoNumbers are, basically, the same thing
as an Identity column in a SQL server table.

For more info, read the Access VBA Help on LastModified and AutoNumbers.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTZPAoechKqOuFEgEQIXOACfQVF5e4arGD9k36KZrj+oqDuZ5IAAoO4X
3CUT0CP2mYWt594ezyrrNQS9
=9sRC
-----END PGP SIGNATURE-----

 

[Joan Wild]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The @@IDENTITY is a function in MS SQL Server, only. It is not
available in Access/JET dbs.

As Allen said, it is now available in Jet 4.

But using your code, you don't have to move to the last modified record to
get the autonumber. It's available as soon as you issue the .addnew

with rs
.addnew
' ... assign column values
debug.print .autonumber_column
.update
end with

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've always used the Help article example on using .LastModified to get
the AutoNumber. I thought the AutoNumber was only assigned when the
..Update executed.

I've not used JET 4 much & hadn't known that the @@IDENTITY function was
available. Should have known from the Option dialog box for
Tables/Queries. The check box title is "SQL Server Compatible Syntax
(ANSI 92)," which should indicate that the JET syntax will "be like" SQL
Server's syntax. But it didn't occur to me that JET would have some of
SQLr's functions.

I've found that in SQL'r it is better to use the SCOPE_IDENTITY function
to get the last assigned IDENTITY value, 'cuz it will only get the value
assigned in the same scope as your routine. IOW, it isn't getting the
IDENTITY value of another user's operation (in a multi-user, high-use
environment).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTdzX4echKqOuFEgEQJPFwCg79kzfyf+4VyvoEFfFy4es3UTfGAAoMum
l6686F63Yc4csf18pdrds0Ep
=AsBx
-----END PGP SIGNATURE-----

 

[Michel Walsh]

Hi,


@@Identity is maintained by session and so, you are quite unlikely to
see the @@Identity that sees another user.


"One big difference between @@DBTS and @@IDENTITY is that @@IDENTITY is
connection specific whereas @@DBTS is database specific. The value returned
by @@IDENTITY will rarely be the same for multiple users, but @@DBTS will
often be identical for all users connected to a given database." ( The
Guru's Guide to Transact-SQL, Addison-Wesley, Ken Henderson,
http://www.amazon.com/gp/reader/0201615762/ref=sib_dp_pt/103-8606383-3207831#reader-page )



Vanderghast, Access MVP