Re: Inserting record with Microsoft Access

Discussion in 'Python' started by Albert Leibbrandt, Feb 9, 2006.

  1. 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.
    >
    >Thanks,
    >Jeff
    >
    >
    >
    >
    >

    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.

    cheers
    albert

    --
    Albert Leibbrandt
    Data Manager
    CompuScan Holdings (Pty) Ltd
    Brandwacht Office Park
    Stellenbosch.
    South Africa.
    Tel;+27 (0)21-8886000
    Fax;+27 (0)21-8832336
    National: 0861 51 41 31
    E-Mail :

    www.compuscan.co.za - reducing credit risk and fraud, at the same time protecting the consumer against over-indebtedness and reckless borrowing

    www.techtonic.co.za - a software design and development, information management and internet solutions company

    www.csti.co.za - a skills training and development provider to the Micro Finance sector (BANKSETA accredited)

    Confidentiality:
    "The information contained in this e-mail and any attachments thereto is private and confidential and is intended for the eyes of the addressees only. Access to this e-mail by any other person or any attachment is not authorized and accordingly prohibited. If you are not the authorized and intended recipient, any disclosure, copying, dissemination or distribution, any other action of omission by you with respect to the information in this e-mail and attachments is prohibited and unlawful."
    Albert Leibbrandt, Feb 9, 2006
    #1
    1. Advertising

  2. On Thu, 09 Feb 2006 10:54:45 +0200, Albert Leibbrandt
    <> declaimed the following in comp.lang.python:

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

    Access/JET does have an autonumber field type... Obtaining the last
    inserted value is a different matter -- I don't have time to crawl
    through the half-dozen VB-JET/Access database texts I have lying around,
    but I'm sure an algorithm was given for this (short of locking the
    tables during the insert and then doing something like select
    max(autonumber_field) from insert_table; lock needed to prevent a
    concurrent insert changing the number)

    From O-XP help, mapping between the two (unfortunately, it doesn't go
    into details on the SQL Server types, so what the "identity property"
    defines is unknown:

    JET
    AutoNumber (AutoNumber data type: In a Microsoft Access database, a
    field data type that automatically stores a unique number for each
    record as it's added to a table. Three kinds of numbers can be
    generated: sequential, random, and Replication ID.) (Increment)

    SQL Server
    int (int data type: In an Access project, a data type of 4 bytes (32
    bits) that stores whole numbers in the range of –2^31 (–2,147,483,648)
    through 2^31 – 1 (2,147,483,647).) (with the Identity property defined)

    Help system leads to M$ site, and the best I've seen so far (since
    autonumber don't reuse values) is

    select top 1 autonumber_field from table order by autonumber_field
    descending
    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Home Page: <http://www.dm.net/~wulfraed/> <
    > Overflow Page: <http://wlfraed.home.netcom.com/> <
    Dennis Lee Bieber, Feb 9, 2006
    #2
    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:
    558
    Paul Clement
    Jul 7, 2005
  2. kalib70
    Replies:
    0
    Views:
    388
    kalib70
    Mar 28, 2006
  3. jeffhg582003

    Inserting record with Microsoft Access

    jeffhg582003, Feb 9, 2006, in forum: Python
    Replies:
    3
    Views:
    379
    Tim Roberts
    Feb 11, 2006
  4. Steve Holden
    Replies:
    2
    Views:
    412
    Chris Smith
    Feb 12, 2006
  5. Navnath Gadakh

    problem in inserting record in ms access.

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

Share This Page