Welcome to SPN

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

Sign Up Now!

how to empty a database to use "as new"; reset autonumbering

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

  1. Philip Leduc

    Philip Leduc
    Expand Collapse
    Guest

    I feel almost ashamed to ask this after years of database development but is
    there an easy way of cleaning a new database?
    Using autonumbering, you always will have some records because of testing,
    and even if the database is split, if you have relationships you cannot
    delete the linked key and recreate?
    I know the user does not have to see the linked id's but in case of customer
    nbr or order nbr it is jsut so easy to expose those.
     
  2. Loading...

    Similar Threads Forum Date
    Elijah's Empty Chair of Freedom in Oslo Interfaith Dialogues Dec 13, 2010
    Journeying Inward: The beauty of the empty vessel Spiritual Articles Nov 29, 2010
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Philip Leduc wrote:
    > I feel almost ashamed to ask this after years of database development
    > but is there an easy way of cleaning a new database?
    > Using autonumbering, you always will have some records because of
    > testing, and even if the database is split, if you have relationships
    > you cannot delete the linked key and recreate?
    > I know the user does not have to see the linked id's but in case of
    > customer nbr or order nbr it is jsut so easy to expose those.


    Make a copy of the existing table (structure only no data). It is now
    ready. If you have records to add from the old table, use an append query
    to add them (and renumber them) to the new table, don't append the number
    field.

    I suggest you may not want to use Autonumber for that use. Autonumbers
    are designed to provide unique numbers. It in not designed to provide
    numbers in order and for a number of reasons may not do so. As a result
    using them in any application where the user sees the numbers is likely to
    end up with confusion.

    There are other ways of providing the numbers you want depending on the
    particual application.



    --
    Joseph Meehan

    Dia duit
     
  4. fredg

    fredg
    Expand Collapse
    Guest

    On Wed, 17 May 2006 15:05:32 -0400, Philip Leduc wrote:

    > I feel almost ashamed to ask this after years of database development but is
    > there an easy way of cleaning a new database?
    > Using autonumbering, you always will have some records because of testing,
    > and even if the database is split, if you have relationships you cannot
    > delete the linked key and recreate?
    > I know the user does not have to see the linked id's but in case of customer
    > nbr or order nbr it is jsut so easy to expose those.


    Either..

    1) Create a new database and import all the forms, queries, ,modules
    etc., into it.
    Also import the Tables and Relationships, BUT first click the option
    button on the import dialog and select Table Structure Only.

    OR ...

    2) Delete all of the records in the current database, starting with
    the Child records if any.
    Then Compact the database.

    NOTE: It really doesn't matter if your AutoNumbers don't start at 1.

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Joseph Meehan wrote:
    > Philip Leduc wrote:
    >> I feel almost ashamed to ask this after years of database development
    >> but is there an easy way of cleaning a new database?
    >> Using autonumbering, you always will have some records because of
    >> testing, and even if the database is split, if you have relationships
    >> you cannot delete the linked key and recreate?
    >> I know the user does not have to see the linked id's but in case of
    >> customer nbr or order nbr it is jsut so easy to expose those.

    >
    > Make a copy of the existing table (structure only no data). It is
    > now ready. If you have records to add from the old table, use an
    > append query to add them (and renumber them) to the new table, don't
    > append the number field.
    >
    > I suggest you may not want to use Autonumber for that use. Autonumbers
    > are designed to provide unique numbers. It in not
    > designed to provide numbers in order and for a number of reasons may
    > not do so. As a result using them in any application where the user
    > sees the numbers is likely to end up with confusion.
    >
    > There are other ways of providing the numbers you want depending
    > on the particual application.


    Sorry, I did not read the part about your relationships. Please ignore
    the part about copying the table business. I would think that you could use
    much the same thing by adding a new autonumber field while using the
    technique I suggested, but I have not tried it. I seldom used autonumber
    and gave up letting users see it long ago.

    --
    Joseph Meehan

    Dia duit
     

Share This Page