Welcome to SPN

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

Sign Up Now!

My AutoNumber is messed up!! Starting from 1 after splitting database

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

  1. Sierras

    Sierras
    Expand Collapse
    Guest

    I have a table with an Auto Number as the primary key.
    All was fine but at some point, the autonumber started giving numbers
    starting from 1 again and is trying to increment with an addition of
    a record. . Now every time you try to create a record, I get the
    conflict as my primary key can not have duplicates. I'm using a split
    database with a FE and BE which has the problem. The original unsplit
    database is working fine.

    Any suggestions?
     
  2. Sierras

    Sierras
    Expand Collapse
    Guest

    Well it's late and instead of trying to figure out what went wrong, I
    just took the easy way out and created a new database from scratch.
    Then imported all the tables from the BE, renamed the new BE and then
    re-linked the tables to the FE.

    All is working now but I'd like to try and figure out what happened so
    as to prevent it from happening again.

    If anyone else ever had this happen, please let me know what you
    found.

    Thanks...


    On Wed, 07 Jun 2006 21:00:27 -0400, Sierras <danick5000@hotmail.com>
    wrote:

    >I have a table with an Auto Number as the primary key.
    >All was fine but at some point, the autonumber started giving numbers
    >starting from 1 again and is trying to increment with an addition of
    >a record. . Now every time you try to create a record, I get the
    >conflict as my primary key can not have duplicates. I'm using a split
    >database with a FE and BE which has the problem. The original unsplit
    >database is working fine.
    >
    >Any suggestions?
     
  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Sierras wrote:
    > Well it's late and instead of trying to figure out what went wrong, I
    > just took the easy way out and created a new database from scratch.
    > Then imported all the tables from the BE, renamed the new BE and then
    > re-linked the tables to the FE.
    >
    > All is working now but I'd like to try and figure out what happened so
    > as to prevent it from happening again.
    >
    > If anyone else ever had this happen, please let me know what you
    > found.


    It's a form of corruption, known to happen occassionally, and I believe
    supposed to be fixed by one or more Jet service packs that were issued for
    Jet 4.0.


    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Re: My AutoNumber is messed up!! Starting from 1 after splitting d

    Hi Rick,

    Yes, this was a problem with Jet version 4.0.2927.4 (Service Pack 3) and
    earlier:

    http://support.microsoft.com/?id=291162

    However, one can still have a duplicate autonumber problem even with SP-8 IF
    the autonumber field was created with JET SP3 or earlier. Having JET SP-8
    will not prevent the problem if the field was created on another PC that was
    not patched properly. The solution is to delete the autonumber field and
    create a new one in it's place, with SP-8 already installed.

    Sierras:
    If you need to replace an autonumber field in an existing table, where there
    are likely gaps in the numbers due to previously deleted records, then create
    a copy of the structure of the entire table, without data. Then run an
    append query, using all of the fields from the table with the bad autonumber
    field (including the autonumber field) serving as the source of records.
    Finally, delete the source table and rename your new table with the same name
    that the source table used to have. Re-establish relationships with
    referential integrity between your new table and the other table(s).


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________


    "Rick Brandt" wrote:

    > Sierras wrote:
    > > Well it's late and instead of trying to figure out what went wrong, I
    > > just took the easy way out and created a new database from scratch.
    > > Then imported all the tables from the BE, renamed the new BE and then
    > > re-linked the tables to the FE.
    > >
    > > All is working now but I'd like to try and figure out what happened so
    > > as to prevent it from happening again.
    > >
    > > If anyone else ever had this happen, please let me know what you
    > > found.

    >
    > It's a form of corruption, known to happen occassionally, and I believe
    > supposed to be fixed by one or more Jet service packs that were issued for
    > Jet 4.0.
    >
    >
    > --
    > Rick Brandt, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
     

Share This Page