I want to retrieve identity column value in ms access from an autoincremented column. Basically i m running a transaction in which i have to write two insert queries. 2nd query will be containing autoincremented value that was generated from query 1. how can i do that ?
views:
178answers:
1
+3
A:
However, Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT
Source: Retrieving Identity or Autonumber Values
EDIT: As noted by @David-W-Fenton, you'll also need to use Jet 4.0 OLE DB Provider (this is also described into that previous document)
Rubens Farias
2010-01-17 20:14:50
It's Jet 4, not Access 2000, that supports SELECT @@IDENTITY.
David-W-Fenton
2010-01-18 21:09:53
@David,that referenced document talks about a combination of Access 2000 and Jet 4.0; I'd update this answer to empathize this aspect.
Rubens Farias
2010-01-18 21:18:19
Debug.Print CurrentDb.OpenRecordset("select @@identity")(0) works in the debug window..
Albert D. Kallal
2010-01-19 20:11:00
Not sure what's meant by needing to use Jet 4 OLE DB Provider -- it works just fine with DAO against Jet 4 and forward. I use it all the time, and I've never written more than a couple hundred lines of ADO/OLEDB code in my entire Access programming career.
David-W-Fenton
2010-01-19 20:27:07
@Rubens Farias: "Combination of A2000 and Jet 4.0" -- er, A2000 used Jet 4 by default, so that would be expected. Not sure what you're trying to say, but the assertion that SELECT @@IDENTITY in Jet is supported only if you use ADO/OLEDB (or SQL 92 mode, which you don't mention) is simply incorrect. Please correct your post.
David-W-Fenton
2010-01-19 20:29:46
@David, refer to link document and, please, help me to fix this post, ty!
Rubens Farias
2010-01-19 21:07:45
The cited article is wrong. Try it and see -- SELECT @@IDENTITY works with DAO 3.6 and Jet 4.0.
David-W-Fenton
2010-01-21 04:49:57