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 ?
Asked
Active
Viewed 3,632 times
3
-
Why didn't you actually just attempt SELECT @@IDENTITY? – David-W-Fenton Jan 18 '10 at 21:10
1 Answers
3
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)
Community
- 1
- 1
Rubens Farias
- 57,174
- 8
- 131
- 162
-
-
@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 Jan 18 '10 at 21:18
-
Debug.Print CurrentDb.OpenRecordset("select @@identity")(0) works in the debug window.. – Albert D. Kallal Jan 19 '10 at 20:11
-
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 Jan 19 '10 at 20:27
-
@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 Jan 19 '10 at 20:29
-
@David, refer to link document and, please, help me to fix this post, ty! – Rubens Farias Jan 19 '10 at 21:07
-
The cited article is wrong. Try it and see -- SELECT @@IDENTITY works with DAO 3.6 and Jet 4.0. – David-W-Fenton Jan 21 '10 at 04:49
-