«

Albert Leibbrandt wrote:
>
> jeffhg582003 wrote:
>
>
>>Hi,
>>
>>I am developing a python script which add records to
>>a microsoft access tables. All my tables have autogenerated number
>>fields. I am trying to capture the number generated from the insert but
>>I am not exactly sure how to do that after an insert.
>>
> I had to do something similiar in sql server and the experts back then
> told me that the only way is to write a stored procedure. aparently sql
> server does not have sequences and I am guessing that access is the same.
>
Well the experts were wrong, I suspect. Try

SELECT @@IDENTITY

to return the autonumber key created by the most recent INSERT.

I believe this works for both SQL Server and Access (doesn't anyone use
Google any more?).

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

Steve Holden wrote:
> Albert Leibbrandt wrote:
> >
> > jeffhg582003 wrote:
> >
> >
> >>Hi,
> >>
> >>I am developing a python script which add records to
> >>a microsoft access tables. All my tables have autogenerated number
> >>fields. I am trying to capture the number generated from the insert but
> >>I am not exactly sure how to do that after an insert.
> >>
> > I had to do something similiar in sql server and the experts back then
> > told me that the only way is to write a stored procedure. aparently sql
> > server does not have sequences and I am guessing that access is the same.
> >
> Well the experts were wrong, I suspect. Try
>
> SELECT @@IDENTITY
>
> to return the autonumber key created by the most recent INSERT.
>
> I believe this works for both SQL Server and Access (doesn't anyone use
> Google any more?).
>
> regards
> Steve
>

I use SELECT IDENT_CURRENT('tablename').

SELECT @@IDENTITY returns the most recent of all inserts. If you have a
complex transaction which triggers inserts into other tables, it may
not return the one you want.

This one allows you to specify the tablename, and it will return the
most recent key inserted into that table.

I got this from the built-in help for SQLServer. I cannot say whether
it works for Access as well.

Frank

>>>>> "Frank" == Frank Millman <> writes:

Frank> SELECT @@IDENTITY returns the most recent of all
Frank> inserts. If you have a complex transaction which triggers
Frank> inserts into other tables, it may not return the one you
Frank> want.

Frank> This one allows you to specify the tablename, and it will
Frank> return the most recent key inserted into that table.

Frank> I got this from the built-in help for SQLServer. I cannot
Frank> say whether it works for Access as well.

Frank> Frank

I've used SELECT @@IDENTITY to good effect, but I agree that there is
no telling what the scope of the variable holding @@IDENTITY is, and
it could turn out to be a kick in the naughty bits.

Depending on the criteria, you might effect the INSERT for a parent
record, and contiue any child record INSERTs by means of the DLookup()
function (Access-only syntactic sugar around nested SELECTs) without
ever having to know the key you would have discovered through
SELECT @@IDENTITY.

R,
Chris