Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

re-enter deleted record

Discussion in 'Information Technology' started by CDSCait, Jul 28, 2006.

  1. CDSCait

    CDSCait
    Expand Collapse
    Guest

    Ok, somehow three records were deleted from my database. In the ID column,
    which is an autonumber field and is the primary key, it literally skips three
    numbers -- 1, 30 and 214 -- and the corresponding records are missing. (No
    snide remarks about the "Are yoy sure you want to PERMANENTLY DELETE this
    record?" warning, please.)

    Is there any way I can re-enter the information for these records and --
    here's the tough part -- restore the records' original ID numbers, so they
    appear sequentially in the table, where they belong (with no skipped
    numbers)?
    Meaning, rather than being positioned as, say, 232, 233 and 234, they can be
    1, 30 and 214?

    I tried (1) entering the three records, then (2) sorting my records the way
    I want by sorting according to the values in a different field, then (3)
    deleting the ID field in "design view", then (3) re-creating a new ID field
    in "design view". But when I went back to the table view it still just
    re-sorted the records, and assigned ID numbers to them according to the order
    in which they were entered -- meaning these three were repositioned at the
    end of the table, instead of in rows 1, 30 and 214, (and numbered
    correspondingly) where I want them.

    As you can tell, I could use some help. Thanks in advance.
     
  2. Loading...


  3. UpRider

    UpRider
    Expand Collapse
    Guest

    This happens a lot. The autonumber ID field should not be used for DATA.
    It is meant to be an unique identifier ONLY.
    Here's a solution.
    1. Add a long integer field to your table and call it perhaps MyID.
    2. Create and run a one-time update query to make MyID = ID.
    3. Add your deleted records, and assign the missing numbers to MyID.
    4. Change any existing code in your database to use MyID instead of ID for
    this table.
    5. When you add a new record from the form (I hope you are using a form to
    enter and edit your data), make MyID = dmax("MyID","MyTableName")+1

    hth, UpRider
    "CDSCait" <CDSCait@discussions.microsoft.com> wrote in message
    news:2070C2A7-7ACF-4CC2-B724-45EB2D18FC0A@microsoft.com...
    > Ok, somehow three records were deleted from my database. In the ID column,
    > which is an autonumber field and is the primary key, it literally skips
    > three
    > numbers -- 1, 30 and 214 -- and the corresponding records are missing. (No
    > snide remarks about the "Are yoy sure you want to PERMANENTLY DELETE this
    > record?" warning, please.)
    >
    > Is there any way I can re-enter the information for these records and --
    > here's the tough part -- restore the records' original ID numbers, so they
    > appear sequentially in the table, where they belong (with no skipped
    > numbers)?
    > Meaning, rather than being positioned as, say, 232, 233 and 234, they can
    > be
    > 1, 30 and 214?
    >
    > I tried (1) entering the three records, then (2) sorting my records the
    > way
    > I want by sorting according to the values in a different field, then (3)
    > deleting the ID field in "design view", then (3) re-creating a new ID
    > field
    > in "design view". But when I went back to the table view it still just
    > re-sorted the records, and assigned ID numbers to them according to the
    > order
    > in which they were entered -- meaning these three were repositioned at the
    > end of the table, instead of in rows 1, 30 and 214, (and numbered
    > correspondingly) where I want them.
    >
    > As you can tell, I could use some help. Thanks in advance.
     
  4. CDSCait

    CDSCait
    Expand Collapse
    Guest

    I guess I should have been more specific -- this database was set up so the
    data is entered and edited directly into the table, not into a form. (It was
    set up this way before I got here.)

    Hopefully I can figure out most of those steps anyway. Thanks. Any other
    suggestions would be appreciated.


    "UpRider" wrote:

    > This happens a lot. The autonumber ID field should not be used for DATA.
    > It is meant to be an unique identifier ONLY.
    > Here's a solution.
    > 1. Add a long integer field to your table and call it perhaps MyID.
    > 2. Create and run a one-time update query to make MyID = ID.
    > 3. Add your deleted records, and assign the missing numbers to MyID.
    > 4. Change any existing code in your database to use MyID instead of ID for
    > this table.
    > 5. When you add a new record from the form (I hope you are using a form to
    > enter and edit your data), make MyID = dmax("MyID","MyTableName")+1
    >
    > hth, UpRider
    > "CDSCait" <CDSCait@discussions.microsoft.com> wrote in message
    > news:2070C2A7-7ACF-4CC2-B724-45EB2D18FC0A@microsoft.com...
    > > Ok, somehow three records were deleted from my database. In the ID column,
    > > which is an autonumber field and is the primary key, it literally skips
    > > three
    > > numbers -- 1, 30 and 214 -- and the corresponding records are missing. (No
    > > snide remarks about the "Are yoy sure you want to PERMANENTLY DELETE this
    > > record?" warning, please.)
    > >
    > > Is there any way I can re-enter the information for these records and --
    > > here's the tough part -- restore the records' original ID numbers, so they
    > > appear sequentially in the table, where they belong (with no skipped
    > > numbers)?
    > > Meaning, rather than being positioned as, say, 232, 233 and 234, they can
    > > be
    > > 1, 30 and 214?
    > >
    > > I tried (1) entering the three records, then (2) sorting my records the
    > > way
    > > I want by sorting according to the values in a different field, then (3)
    > > deleting the ID field in "design view", then (3) re-creating a new ID
    > > field
    > > in "design view". But when I went back to the table view it still just
    > > re-sorted the records, and assigned ID numbers to them according to the
    > > order
    > > in which they were entered -- meaning these three were repositioned at the
    > > end of the table, instead of in rows 1, 30 and 214, (and numbered
    > > correspondingly) where I want them.
    > >
    > > As you can tell, I could use some help. Thanks in advance.

    >
    >
    >
     

Share This Page