Timing of update of auto-number fields

James Martin

unread,

Mar 12, 2010, 11:42:58 AM3/12/10

to

I have some code which adds a record to a table whose key is an auto-number
field. I need to retain the value of the auto-number field of the new record
to use for subsequent code. Everything was working fine in Access, but when
I migrated the back-end to SQL Server, the code started failing.

Here's the code I'm using (simplified). (TransactionID is the auto-number
field.)

rs.AddNew
lngTransactionID = rs.TransactionID
...
...
...
rs.Update

The code is using lngTransactionID to retain the value of the auto-number
field once the update is done. This is subsequently used to populate a child
table with records linked to that transaction ID.

As far as I can tell, the reason is that, in Access, when a new record is
started, the auto-number field is populated right away, so it's possible to
read its value. But with the back-end in SQL, the auto-number field stays
null until the record is committed.

If I move the lngTransactionID = rs.TransactionID statement to after the
rs.Update, it ends up returning the transaction ID of the first record in
the table, not the record that was just added.

Is there an easy way to force SQL Server to populate the TransactionID field
before the record is updated? Or to retain the value of the new transaction
ID after the record is update?

Thanks in advance.

James

Jeff Boyce

unread,

Mar 12, 2010, 2:48:34 PM3/12/10

to

James

Your assessment is correct. Access "reserves" the Autonumber once you've
dirtied the record, and before it is saved. SQL Server assigns an
"autonumber" as part of the actual saving.

Take a look at SQL HELP re: @@Identity for more information about retrieving
that assigned value.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"James Martin" <nor...@noreply.com> wrote in message
news:FfSdnUxx0Jp78wfW...@posted.vianet...

Armen Stein

unread,

Mar 13, 2010, 10:25:56 AM3/13/10

to

On Fri, 12 Mar 2010 11:48:34 -0800, "Jeff Boyce"
<nons...@nonsense.com> wrote:

>Your assessment is correct. Access "reserves" the Autonumber once you've
>dirtied the record, and before it is saved. SQL Server assigns an
>"autonumber" as part of the actual saving.
>
>Take a look at SQL HELP re: @@Identity for more information about retrieving
>that assigned value.

Jeff is correct. And you can use another method (.LastModified)
within Access instead of the @@Identity technique. The nice thing is
that it also works with Access tables, so you end up with a consistent
approach that works in both scenarios. We use it in all of our Access
applications regardless of back-end database.

I describe this code in a PowerPoint presentation on techniques for
using Access as a client-server front-end to SQL Server databases.
It's called "Best of Both Worlds" at our free J Street Downloads page:
http://ow.ly/M2WI. It also includes some thoughts on when to use SQL
Server, performance and security considerations, concurrency
approaches, and techniques to help everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

James Martin

unread,

Mar 15, 2010, 11:28:08 AM3/15/10

to

Thanks! This is perfect.

"Armen Stein" <Armen...@removethisgmail.com> wrote in message
news:1cbnp5ph5drl8jg91...@4ax.com...

Jeff Boyce

unread,

Mar 15, 2010, 12:42:31 PM3/15/10

to

Thanks, Armen. I believe I'll be able to use this too!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Armen Stein" <Armen...@removethisgmail.com> wrote in message
news:1cbnp5ph5drl8jg91...@4ax.com...

add

unread,

May 4, 2010, 9:12:57 PM5/4/10

to

蕙锟斤拷锟斤拷锟揭癸拷锟斤拷锟斤拷锟斤拷煤锟斤拷锟斤拷占锟斤拷锟斤拷锟斤拷锟街伙拷锟斤拷糯锟斤拷为锟斤拷蕙锟斤拷锟斤拷锟斤拷蕙锟斤拷指锟叫癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥★拷蕙锟侥★拷锟斤拷锟斤拷锟斤拷伞锟斤拷锟斤拷锟斤拷坪锟斤拷锟轿拷锟睫ワ拷啤锟斤拷锟睫ワ拷锟斤拷锟斤拷锟斤拷夜锟斤拷锟较★拷锟斤拷郑锟轿拷锟揭讹拷锟斤拷锟截点保锟斤拷野锟斤拷锟斤拷锟街★拷蕙锟斤拷原锟街诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟较★拷锟斤拷锟斤拷锟皆憋拷锟斤拷锟斤拷锟较癸拷锟斤拷锟斤拷锟角比斤拷锟酵猴拷锟斤拷锟斤拷锟斤拷品锟斤拷之一锟斤拷
蕙锟斤拷锟侥达拷统锟斤拷品锟斤拷锟较帮拷锟街ｏ拷锟斤拷一品锟斤拷锟斤拷梅锟斤拷锟较猴拷梅锟斤拷锟截讹拷锟斤拷元锟街★拷染锟街★拷锟斤拷锟教★拷锟斤拷锟街★拷
"James Martin" <nor...@noreply.com> 写锟斤拷锟斤拷息锟斤拷锟斤拷:FfSdnUxx0Jp78wfW...@posted.vianet...

Stefan Hoffmann

unread,

May 5, 2010, 3:57:22 AM5/5/10

to

hi James,

On 12.03.2010 20:48, Jeff Boyce wrote:
> Take a look at SQL HELP re: @@Identity for more information about retrieving
> that assigned value.

Caveat:

SCOPE_IDENTITY, IDENT_CURRENT are @@IDENTITY three functions to get the
value, but they differ in scope and the table of which they may return
the value.

So read the help carefully, if you're heading this way.

mfG
--> stefan <--