cant get next number

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

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 ***

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


<timasmith@hotmail.com> wrote in message
news:1150146149.181072.93410@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
>

Rich P wrote in message <%omjg.34$0n1.38910@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

Thanks. That was a great article. Very informative.

Rich

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