Timing of update of auto-number fields

[James Martin]

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]

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.

Post by James Martin
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

[Armen Stein]

On Fri, 12 Mar 2010 11:48:34 -0800, "Jeff Boyce"

Post by Jeff Boyce
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.
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]

Thanks! This is perfect.

Post by Armen Stein
On Fri, 12 Mar 2010 11:48:34 -0800, "Jeff Boyce"

Post by Jeff Boyce
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.
that assigned value.

Jeff is correct. And you can use another method (.LastModified)
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.
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

[Jeff Boyce]

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.

Post by Armen Stein
On Fri, 12 Mar 2010 11:48:34 -0800, "Jeff Boyce"

Post by Jeff Boyce
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.
that assigned value.

Jeff is correct. And you can use another method (.LastModified)
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.
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

[Stefan Hoffmann]

hi James,

Post by Jeff Boyce
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 <--

[add]

Þ¥ÀŒÊÇÎÒ¹úÔÔÅà×îŸÃºÍ×îÆÕŒ°µÄÀŒ»šÖ®Ò»£¬¹ÅŽú³£³ÆΪ¡°Þ¥¡±£¬¡°Þ¥¡±ÖžÖйúÀŒ»šµÄÖÐÐÄ¡°Þ¥ÐÄ¡±£¬³£ÓëÉ¡¿ÆÀà°×ÜƺÏÃûΪ¡°Þ¥ÜÆ¡±¡£Þ¥ÀŒ»šÊÇÎÒ¹úÕäÏ¡ÎïÖÖ£¬Îª¹úŒÒ¶þŒ¶Öص㱣»€Ò°ÉúÎïÖÖ¡£Þ¥ÀŒÔ­·Ö²ŒÓÚÇØÁëÒÔÄÏ¡¢ÄÏÁëÒÔ±±Œ°Î÷ÄϹãŽóµØÇø£¬ÊDZȜÏÄͺ®µÄÀŒ»šÆ·ÖÖÖ®Ò»¡£
Þ¥ÀŒµÄŽ«Í³ÃûÆ·ÓÐÀÏ°ËÖÖ£ºŽóһƷ¡¢³Ì÷¡¢ÉϺ£Ã·¡¢¹Ø¶¥¡¢Ôª×Ö¡¢ÈŸ×Ö¡¢ÅËÂÌ¡¢µŽ×Ö¡£

Post by James Martin
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