Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Unanswered: reset autonumber

    hello
    is there anyway to reset an autonumber field in Access.
    ie. I delete everything in my table, then I add new records.
    I want the first record to start at 1 and not 3000.
    can i do this?

    thanks in Advance
    ActionAnt

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    delete rows, compact database, tada
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    how do I code that?

  4. #4
    Join Date
    Apr 2004
    Posts
    182
    Do you have to delete all records for this to work

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure you can code the compact operation

    but hey, if you're going to empty the table, why not just drop and recreate it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    try recreating the autonumber field in the table design view

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    i can do that but;
    i am using an append query to add all the rows into the table after the old ones have been deleted. the query inputs 2 fields. the table contains 3 fields(2 from the query and one "ID" that is an autonumber)
    if i were to create a make table query that makes a new table everytime, how would I do it so that it also creates an autonumber field?

  8. #8
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    how do I recreate the autonumber in design view

  9. #9
    Join Date
    Mar 2004
    Posts
    118
    you have a certain field set to autonumber, right?

    Just go into the design view of the table... And delete that field, then make a new one with exactly thesame name and set it to autonumber.

    Or are you dynamically creating that table?

  10. #10
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    I know, that's how I did it in the first place, but I need it to be automated.
    I can't expect people to go into the design view of the tables everytime they use the program.

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by r937
    i'm not sure you can code the compact operation

    but hey, if you're going to empty the table, why not just drop and recreate it?
    To compact a DB: you can not compact a live DB (i.e the one you are working with). You can compact a closed db using DBEngine.CompactDatabase Method. Just have a look to its help

  12. #12
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    that'll be fine if I can automatically create an autonumber when I create the table using a make-table query.
    how do I do that,
    or
    after the table is created can I code to add an autonumber field into the table?

  13. #13
    Join Date
    Mar 2004
    Posts
    118
    Quote from MS help

    CREATE TABLE Statement, CONSTRAINT Clause Example (MDB)

    To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

    Warning These examples makes changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

    The following example creates a new table with two Text fields:

    CREATE TABLE FirstTable (FirstName TEXT, _
    LastName TEXT);
    The next example creates a new table with two Text fields, a Date/Time field, and a unique index made up of all three fields:

    CREATE TABLE SecondTable (FirstName TEXT, _
    LastName TEXT, DateOfBirth DATETIME, _
    CONSTRAINT MyTableConstraint UNIQUE (FirstName, _
    LastName, DateOfBirth));
    The following example creates a new table with two Text fields and an Integer Number field. The SSN field is the primary key.

    CREATE TABLE ThirdTable (FirstName TEXT, _
    LastName TEXT, SSN INTEGER
    CONSTRAINT MyFieldConstraint PRIMARY KEY);

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hammbakka
    To compact a DB: you can not compact a live DB (i.e the one you are working with).
    to be specific, you may not be able to do it in code, but you sure can do it from the menu bar when the database is open in Access

    Tools > Database Utilities > Compact Database

    works every time, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2004
    Posts
    118
    I dont know how you would compact the database in code, but that would work too...

    Either way, if you just drop and create the table every time you should have no problems.

Posting Permissions

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