Re: Inserting record with Microsoft Access

Discussion in 'Python' started by Steve Holden, Feb 9, 2006.

  1. Steve Holden

    Steve Holden Guest

    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, Feb 9, 2006
    #1
    1. Advertising

  2. 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 Millman, Feb 11, 2006
    #2
    1. Advertising

  3. Steve Holden

    Chris Smith Guest

    >>>>> "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
     
    Chris Smith, Feb 12, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shapper
    Replies:
    2
    Views:
    589
    Paul Clement
    Jul 7, 2005
  2. kalib70
    Replies:
    0
    Views:
    418
    kalib70
    Mar 28, 2006
  3. jeffhg582003

    Inserting record with Microsoft Access

    jeffhg582003, Feb 9, 2006, in forum: Python
    Replies:
    3
    Views:
    395
    Tim Roberts
    Feb 11, 2006
  4. Albert Leibbrandt

    Re: Inserting record with Microsoft Access

    Albert Leibbrandt, Feb 9, 2006, in forum: Python
    Replies:
    1
    Views:
    504
    Dennis Lee Bieber
    Feb 9, 2006
  5. Navnath Gadakh

    problem in inserting record in ms access.

    Navnath Gadakh, Oct 4, 2012, in forum: Java
    Replies:
    5
    Views:
    410
    Arne Vajhøj
    Oct 6, 2012
Loading...

Share This Page