SELECT @@IDENTITY in A2010 with Triggers

[David W. Fenton]

Over on StackOverflow someone was asking about how to get the last
Autonumber value, and I suggested SELECT @@IDENTITY in the same
connection as the insert is made, and said "just like SQL Server."

Someone responded that in SQL Server you should use the
SCOPE_IDENTITY() function instead, so I looked it up:

http://msdn.microsoft.com/en-us/library/ms190315.aspx

....and that made me think that in A2010 with table-level data
macros, SELECT @@IDENTITY might end up not being reliable if the
insert causes a trigger to insert a record in another table (as
described in the MSDN article).

Does anyone working with A2010 have any idea what MS has implemented
to address this?

 

[Banana]

Does anyone working with A2010 have any idea what MS has implemented
to address this?

Did you see this blog post about ReturnVar?

http://blogs.msdn.com/access/archive/2010/03/19/access-2010-returnvars-in-data-macros.aspx

Seem to me that would be analogous to opening an AppendOnly DAO
Recordset, adding a new record and getting the inserted value via
..LastModified bookmark. This should then be pretty reliable and very
close to SCOPE_IDENTITY() which I personally use over @@IDENTITY when
using SQL Server anyway.

 

[David W. Fenton]

Did you see this blog post about ReturnVar?

http://blogs.msdn.com/access/archive/2010/03/19/access-2010-returnv
ars-in-data-macros.aspx

Seem to me that would be analogous to opening an AppendOnly DAO
Recordset, adding a new record and getting the inserted value via
.LastModified bookmark. This should then be pretty reliable and
very close to SCOPE_IDENTITY() which I personally use over
@@IDENTITY when using SQL Server anyway.

I've never before seen anyone recommend SCOP_IDENTITY() before, but
it seems to me that it ought to be the default, since you never know
when a trigger might get added.

Seems to me that the ReturnVars in a data macro is moving backwards.
I just don't see it as a good thing to be locking the record that
long. Maybe it doesn't matter in reality, but I can't help but think
that table-level data macros are going to slow things down a lot.

We'll see.

Your suggestion is a workaround to avoid the issue, but it doesn't
really address the actual problem in my question.

 

[Banana]

I've never before seen anyone recommend SCOP_IDENTITY() before, but
it seems to me that it ought to be the default, since you never know
when a trigger might get added.

I _think_ it was relatively recent addition (2005?) but yes, I agree
that SCOPE_IDENTITY() ought to be the first thing to reach for cases
where we need last inserted key.

Seems to me that the ReturnVars in a data macro is moving backwards.
I just don't see it as a good thing to be locking the record that
long.

I realized I had forgotten to point out that when you run, for instance,
After Insert macro, you still can access the modified values via the Old
object and <name of table modified> (It doesn't seem it has a New object
which it should to make clear we are actually referring to same row but
using the modified table's name seems to have the desired effect
anyway). So you can grab the newly inserted value by setting a ReturnVar
variable to <Name of table>.<Name of primary key column>. So that
wouldn't require going back to the record to find out the key and you'd
be at least to run other operations that may need the new key. So not
really analogous to doing a .LastModified & retrieving the value from
recordset field after all.

Also, I don't know for a fact whether After <whatever> macros actually
lock the table or not. I would expect this to be the case in Before
<whatever> macros but when you use Before <whatever>, the list of
allowed actions is very restricted - no inserts in other tables are

Maybe it doesn't matter in reality, but I can't help but think
that table-level data macros are going to slow things down a lot.

We'll see.

I should also point out that there will be two applications using the
same thing - Access itself and Sharepoint. I wouldn't be very surprised
if we were to find that the performance were quite different between a
regular Access database running some data macros vs. a published
database running the same macros (which is now transformed into
Sharepoint workflow, I believe but I may be wrong on that point.)

Your suggestion is a workaround to avoid the issue, but it doesn't
really address the actual problem in my question.

The implication here is that there's really no SCOPE_IDENTITY() built in
to Jet. This is probably the closest you can get, I'd believe. Would
love to be shown wrong.

 

[David W. Fenton]

I _think_ it was relatively recent addition (2005?)

I thought so, too, but found a Knowledge Base article about it for
SQL Server 2000, so not exactly new.

but yes, I agree
that SCOPE_IDENTITY() ought to be the first thing to reach for
cases where we need last inserted key.

See, I didn't know this, and I've seen lots of people recommend
@@IDENTITY, and people who I see as SQL Server gurus.

I realized I had forgotten to point out that when you run, for
instance, After Insert macro, you still can access the modified
values via the Old object and <name of table modified> (It doesn't
seem it has a New object which it should to make clear we are
actually referring to same row but using the modified table's name
seems to have the desired effect anyway). So you can grab the
newly inserted value by setting a ReturnVar variable to <Name of
table>.<Name of primary key column>. So that wouldn't require
going back to the record to find out the key and you'd be at least
to run other operations that may need the new key. So not really
analogous to doing a .LastModified & retrieving the value from
recordset field after all.

But, as you said, it's basically equivalent to the old DAO .AddNew
method of inserting records.

Also, I don't know for a fact whether After <whatever> macros
actually lock the table or not. I would expect this to be the case
in Before
<whatever> macros but when you use Before <whatever>, the list of
allowed actions is very restricted - no inserts in other tables
are allowed for example. You can only do this in After <whatever>.
I'd be surprised if locking behavior were identical before &
after, FWIW.

I guess I'm prejudiced in favor of SQL inserts, and handling them
myself in code. Obviously if I'm writing an app that won't have VBA
code, the macro would be the way I'd do it, anyway, but I'm still
foggy on whether or not I'm going to be avoiding VBA in new
development or not.

How will we decide whether or not future web deployability is worth
avoiding VBA?

I should also point out that there will be two applications using
the same thing - Access itself and Sharepoint. I wouldn't be very
surprised if we were to find that the performance were quite
different between a regular Access database running some data
macros vs. a published database running the same macros (which is
now transformed into Sharepoint workflow, I believe but I may be
wrong on that point.)

Oh, I expect Sharepoint to be quite fast. I'm planning to use
Sharepoint for some projects, but not by any means a majority of
them. But table-level data macros are a win for all pure-Access
apps, so I'm interested in knowing what they do to performance.
Relative performance is not important to me at all, since I won't be
using them as a way of developing for Sharepoint except in a few
cases.

The implication here is that there's really no SCOPE_IDENTITY()
built in to Jet. This is probably the closest you can get, I'd
believe. Would love to be shown wrong.

I think Jet/ACE needs it now that there are the equivalent of
triggers. Can anyone ask that question of the powers that be at
Microsoft?

 

[Tony Toews [MVP]]

Over on StackOverflow someone was asking about how to get the last
Autonumber value, and I suggested SELECT @@IDENTITY in the same
connection as the insert is made, and said "just like SQL Server."

David

You want to write up an article on this and I'll post it as a blog?
Or should I just write up a paragraph mentioning this thread?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

You want to write up an article on this and I'll post it as a
blog? Or should I just write up a paragraph mentioning this
thread?

The post you replied and the StackOverflow discussion is all I have
to say about it (I don't blog about Access!). I'd like someone on
the Access team to be asked about this, to be honest. It seems to me
to be a significant issue, what with A2010 having the equivalent of
triggers now.