1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 - Slight Problem - Autonubers

Discussion in 'Business Applications' started by firestormer, Jan 23, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Slight problem.

    Ive just finnished creating an access database and during the testing i entered in some false details ect and records wer created. Now ive realised that after i deleted the false records it must archive the primary key and that when you delete a record it dosnt delete the archived primary key value.

    Hence when i create a new recorde it is given an autonumber as the primary key but now the autonumber is starting at 0000004 rather then 0000001.

    Is the someway to clear or reset the autonumber primary key?
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    I asked this once upon a time and was given the (now correct, in my opinion) answer, "why does it matter?" Autonumbers should not contain any sort of pertinent data whatsoever. The primary function of an Autonumber is to facilitate sorting records in order of entry, because conceptually, records in a table have no order unless you sort them somehow. So then, as long as the most recently stored record has a higher Autonumber than the previously stored record, that's all you should care about.

    Technically speaking, if you have no previous records in the table, delete unwanted records, and perform a Compact and Repair Database... prior to adding additional records to the table, the Autonumbers will be tared. This might also be true if there are previously stored records, but I haven't paid too much attention.

    Other than that, the only way to reset the Autonumber would be to create a new table and copy the records across, which should rebuild the Autonumber list. You may see why this is undesirable. I suspect you were using the Autonumber as a significant datum (since you had an augmented amount of leading zeros)?

    chris.
     
  3. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    The thing is i want to give the database to my final user complete and cleane.

    Isnt their someway to edit the archive?
     
  4. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Like I said, the only way is to duplicate the table and copy the records from one to the other, excluding the Autonumber attribute. It should be repopulated automatically (enumerated incrementally).

    If you're clearing the entire table, do so and perform a Compact/Repair, which should do the trick.

    HTH

    chris.
     
  5. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Me again hope ur still listening.

    Is it psooible to 'Save As' the entire database or export all parts of the database minus record data?

    What exactly does compact and repair do?
     
  6. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Compact & repair, takes out all the interim changes that you have made to tables/queries/forms/reports which it has logged and just keeps the current design. The logging of changes is why the database grows so fast during the design stage even though you aren't entering much data. It also resets all record keys and autonumbers where tables have been emptied.
    No Access doesn't do a save as.
    To copy the database just use Explorer and copy and paste with a new name.
    To do want you want you can create a new Database and import The Structure or the Structure & Data.
    Import is under the Main Menu Files>Get External Data.
     
  7. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Ok hers an idea ive been swatting up on databases. Please tell me if im worng.

    Databases are split into two halves: Software and Data.

    These are stored as SQL. I know in some parts of access such as queries you can write in SQL.

    so in theory if i can view the SQL i can drill throuh the database untill i find the bit concerning the data for the table and delete and edit as appropriot.

    So how can i view my database as SQL and how?
     
  8. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    I think you have a misunderstanding of SQL. SQL is a language for accessing and arranging data not storing data. In other words, there's no such thing as data "stored as SQL." So I don't quite understand what you're trying to do. Are you still trying to tare the Autonumbers? If they're used as a primary key and are involved in relationships, you won't be able to delete them anyway without breaking the relationship or cascading deletes/updates. Even then you can't enter your own value into an Autonumber attribute. If you use Autonumbers as primary keys in relationships, the smartest (and maybe only) option is to clear every table, Compact and Repair, and re-enter all the data.

    I still don't know why it's necessary. Autonumbers should always always be transparent to the user. It doesn't matter that there are gaps in the sequence. If it's critical that no gaps exist, you shouldn't be using Autonumbers.

    chris.
     
  9. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    the thing is i need to test the database and make corrections as i go along. I want to be able to hand over the database blank with out any flase test data in and set up so that they start at 1 and not 23 because i know these people and i know theyll make a lot of fus if its not perfect.

    im preety certain you can build queries is SQL view why not tables?
     
  10. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    All you need to do is clear the tables and perform a Compact and Repair before you distribute the database. If you delete every record in a table and Compact/Repair, it tares the Autonumber. (This has been mentioned in posts #2, #4, and #6.) Since the tables only contain test data, you'll be deleting all the records anyway. Compact/Repair. Every table's Autonumber will be reset to 1.

    You can design a CREATE TABLE query in SQL view, that is correct. That isn't the same as

    chris.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/436510

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice