ansaurus

Question

Get Autonumber from newly inserted record in Access 2007 using Enterprise Library 4.1

Answer 1

+1 A:

With a single connection:

execute the INSERT statement.
then get the result of SELECT @@IDENTITY, which will return the Autonumber value inserted in step 1.

In other words, it's just like SQL Server (and has been since 1999, when Jet 4 was introduced including support for SELECT @@IDENTITY).

David-W-Fenton 2010-04-07 21:08:17

thanks David..but first, you should be using SCOPE_IDENTITY() instead of @@IDENTITY...and second, doing it just like it can be done in SQL server is what I'm looking for. With SQL Server you can just add a SELECT SCOPE_IDENTITY() at the end of the sproc, and you get the new Identity in the same execution of ExecuteScalar(). However, I haven't find a way to do this with Access in a single step, that's why I wanted to know how other people is handling this.

silverCORE 2010-04-08 14:01:57

I honestly didn't know about SCOPE_IDENTITY(), probably because all my Access apps with SQL Server back ends were upsized from Access and don't have any triggers (which so far as I can tell is the only reason to use it in preference to @@IDENTITY). In Jet/ACE, there aren't any triggers (well, there will be in Access 2010, so maybe they have added SCOPE_IDENTITY() to the new version of Jet), so there's no danger here -- the scope is limited to the current connection, so no issue at all as long as you re-use the same connection -- two steps will work just fine.

David-W-Fenton 2010-04-11 00:43:42

+1 David is right. Scope_Identity doesn't apply here since it is only valid in a sproc. In sql it prevents you from getting an invalid id e.g. in case a trigger is running on the target table which inserts another value before you can get the new identity. In jet you specify the table SELECT @@Identity FROM sTableName which limits your scope anyway.

Praesagus 2010-04-12 22:56:44

The FROM clause has no effect at all. I just tested with two inserts into two different tables and tried to specify the table, but got back the same identity number for both, i.e., the one added to the last table that had an insert.

David-W-Fenton 2010-04-14 00:35:12

Answer 2

A:

hi

This how iam doing in SQL Server. This will return the autonumber primary key in output vaiable

CREATE PROCEDURE [dbo].[TEST](

@p_ID NUMERIC(9,0) OUT ,

@p_NAME NVARCHAR(150)

AS BEGIN

   INSERT INTO EMR_INV_MAST_ORDERSET(NAME)
  VALUES (@p_NAME)
  SELECT @p_ID= SCOPE_IDENTITY()

END

Kishore Kumar 2010-04-12 05:16:52

None of this will work with Access/Jet/ACE. First off, you can't execute two SQL statements in one QueryDef, secondly, SCOP_IDENDITY() is SQL-Server-specific.

David-W-Fenton 2010-04-12 19:42:37

Answer 3

A:

Does anybody know how do I get the same data from Visual Basic Code, Im working with a DB in access and trying to do that with VBA.

Thanks. Charlie.

Charlie 2010-10-07 17:42:11

This is a new question, not an answer. However, the fact is, the answer is already given -- it's done exactly the same way as outlined above. If you don't know how to code that in VBA, then you should post a question citing this question and asking for assistance in coding it.

David-W-Fenton 2010-10-08 02:15:41

ansaurus

tags:

views:

answers:

Get Autonumber from newly inserted record in Access 2007 using Enterprise Library 4.1

related questions