Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    Auckland, Auckland, New Zealand
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    access 2000 reset auto number (2000)

    Hi everyone,

    I've been a developer for a few years but I have been out of Access for a while (2 years). I've just returned to it and found that some of my notes are missing. I know there is a method to re-sequence the auto number field to start at 1 again, but I can't for the life of me remember how to do it. And none of my Access books want to help me there either. Can someone refresh my memory?

    Thanks and regards,
    Sue.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: access 2000 reset auto number (2000)

    if you compact and repair the db the autonumber fields (if they have no data in them <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20> will revert to 1. Basically, you might want to copy the db, delete the info in the copy, compact and repair, then re-import the info. That's not terribly elegant but it works.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: access 2000 reset auto number (2000)

    If Steve's solution does not work then look at <post#=358042>post 358042</post#>.

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Location
    Auckland, Auckland, New Zealand
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: access 2000 reset auto number (2000)

    thanks - that was very helpful. I knew it was a little tricky but a lot faster than trying to find a way to code it. This way will work certainly.
    <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: access 2000 reset auto number (2000)

    Not sure if this applies to Access, but in SQL Server it's easy to create a new blank table with the auto-increment field set to 1. this is the stored procedure:

    <font face="Georgia">ALTER PROCEDURE CreateCaseListTable AS
    -- =============================================
    -- Create table with IDENTITY column
    -- =============================================
    IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = N'CaseList'
    AND type = 'U')
    DROP TABLE CaseList

    create table CaseList (
    CaseListID int IDENTITY(1, 1),
    CaseID varchar(255) NULL,
    TargetID varchar(255) NULL,
    TargetName varchar(255) NULL,
    OpenedDate varchar(255) NULL,
    ClosedDate varchar(255) NULL,
    ContactName varchar(255) NULL,
    UserArea varchar(255) NULL,
    SourceDescription varchar(255) NULL,
    Status varchar(255) NULL,
    StatusDescription varchar(255) NULL
    )



    GO</font face=georgia>

    What this code does is: if the table 'CaseList' exists, drop it (delete) and re-create the table with the specs as shown. In SQL Server (and mySQL as well) you can 'order up' tables with any number of column defs, including the INDENTITY column. This works in my setup because I need to periodically re-load data to a table and the existing data is deleted before the re-load.

    I'd *think* access has a similar function that you can hook into in a module. The only problem with coding for a module is figuring out how to access it via, say, a button on a form etc. Access is a little weird about setting event procedures to existing code.

    On looking in the VBA help, there is in fact the command CREATE TABLE. So, that should work as well.

Posting Permissions

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