Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoNumber with Oracle (Access 2000)

    I am trying to make a field update itself just as the autonumber function does in access. When you link to an Oracle table, you can not create a field type that is autonumber that you can use with access. I need this because there is no primary key on my main table that someone will enter. Any help on this? VBA would be fine, also. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber with Oracle (Access 2000)

    You can't change the structure of an Oracle table from Access and all a linked table represents is a window into the Oracle table. Is the Oracle table your main table? I would be very surprised to find an Oracle table that didn't have a unique key of some kind, but if you don't have one, no one will be able to enter data into it from Access anyhow.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber with Oracle (Access 2000)

    The structure is fine and i have the unique field. However, when I go to enter a new "order" on the form, it does not automatically give me a new number. If the table is in Access it auto populates that unique field. I want to be able to do the same with my oracle table so that the user does not have to enter (or keep track) of the unique field. Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoNumber with Oracle (Access 2000)

    You can use the BeforeInsert event of the form to assign a new number. Create a table with only one field (numeric) and one record. This table holds the maximum number assigned up to now. In the BeforeUpdate event of the form, get the number from this auxiliary table, add 1 to it and update the record with the new value. Then assign the new value to the number field in the form.

    Post back if this is not clear enough.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoNumber with Oracle (Access 2000)

    If you're looking for a true autonumber, it would have to be done from Oracle, not from Access. If you just need a unique value inserted, use Hans suggestion.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumber with Oracle (Access 2000)

    This sample illustrates a number of ways to simulate your own Autonumber primary key.

    You might be able to make use of this db provided by Roger...

    Don't forget to pay him his Dollar <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •