cant get next number

[timasmith]

I guess more the fool me for attempting with MS Access but if I execute

select max(nbr) from mytable

nextnbr = maxnbr + 1

insert into mytable (nextnbr...)

multiple times - it appears the first insert is not picked up by the
next select max() - even using the same connection.

Is this due to not having committed i.e. Access query using the same
connection does not show modified data - or is it simply access cannot
register an insert fast enough for the next select?

thanks

Tim

[Rich P]

If I read you correctly, it sounds like you want to get the next
autonumber. If this is the case, you can't rely on autonum + 1 because
if there is a deletion in the table, that will throw off the
autonumbers. I think that you can only get Identity/Autonumbers from an
enterprise system like sql server, Oracle, ... where they have a
specific object called @@Identity for retrieving the next
Identity/autonumber value. I don't think Access has this capability
since it is not an enterprise system. Maybe I am wrong here - not sure.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Terry Kreft]

Sounds to me like you're doing something wrong.

Try posting real code not meaningless snippets and we mght be able to tell
you where you're going wrong.

--

Terry Kreft
<ti*******@hotmail.com> wrote in message
news:11*********************@h76g2000cwa.googlegro ups.com...

I guess more the fool me for attempting with MS Access but if I execute

select max(nbr) from mytable

nextnbr = maxnbr + 1

insert into mytable (nextnbr...)

multiple times - it appears the first insert is not picked up by the
next select max() - even using the same connection.

Is this due to not having committed i.e. Access query using the same
connection does not show modified data - or is it simply access cannot
register an insert fast enough for the next select?

thanks

Tim

[RoyVidar]

Rich P wrote in message <%o****************@news.uswest.net> :

If I read you correctly, it sounds like you want to get the next
autonumber. If this is the case, you can't rely on autonum + 1
because if there is a deletion in the table, that will throw off the
autonumbers. I think that you can only get Identity/Autonumbers from
an enterprise system like sql server, Oracle, ... where they have a
specific object called @@Identity for retrieving the next
Identity/autonumber value. I don't think Access has this capability
since it is not an enterprise system. Maybe I am wrong here - not
sure.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

With regards to Jet 4.0 and @@identity, have a look here;
http://support.microsoft.com/kb/232144/en-us

--
Roy-Vidar

[Rich P]

Thanks. That was a great article. Very informative.

Rich

*** Sent via Developersdex http://www.developersdex.com ***