none
Seeding an Access AutoNumber

    Question

  • Is there a way to seed an Access Autonumber field so it won't begin at 1?

    Thanx.


    Darrell H Burns
    Monday, December 13, 2010 7:35 PM

Answers

  • DarrellDoesData wrote:

    Is there a way to seed an Access Autonumber field so it won't begin
    at 1?

    Yes, beginning with JET4 = Access 2000 you can do it by SQL:

    ALTER TABLE Table ALTER COLUMN Field COUNTER(1000,1)

    Where 1000 is the new seed value and 1 the increment.


    cu
    Karl
    *****
    Access-FAQ (German/Italian): http://www.donkarl.com

    Monday, December 13, 2010 7:52 PM

All replies

  • Check what Allen Browne has at http://www.allenbrowne.com/ser-26.html
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Monday, December 13, 2010 7:43 PM
  • DarrellDoesData wrote:

    Is there a way to seed an Access Autonumber field so it won't begin
    at 1?

    Yes, beginning with JET4 = Access 2000 you can do it by SQL:

    ALTER TABLE Table ALTER COLUMN Field COUNTER(1000,1)

    Where 1000 is the new seed value and 1 the increment.


    cu
    Karl
    *****
    Access-FAQ (German/Italian): http://www.donkarl.com

    Monday, December 13, 2010 7:52 PM
  • "DarrellDoesData" wrote in message
    news:92d7cab9-1790-4c85-b313-f9d3f8167e6c@communitybridge.codeplex.com...
    > Is there a way to seed an Access Autonumber field so it won't begin at 1?
     
    One way is to use an append query to force a record into the table with a
    value in the autonumber field that is one less than the first number you
    want subsequent entries to start at.  Then delete that record.  Here's an
    example:
        ' Set next autonumber to 1000.
       With CurrentDb
            .Execute _
               "INSERT INTO Table1 (ID) VALUES (999)", _
               dbFailOnError
            .Execute "DELETE FROM Table1 WHERE ID = 999"
        End With
     
    Provided that Table1's autonumber hasn't already reached 999, the above code
    forces the subsequent autonumbering to begin at 1000.
     
    Now, if you want a technical way to do it instead, I believe you can
    actually set the autonumber "seed" value using ADO (or maybe ADOX).
    However, the above simple technique is pretty straightforward.
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Monday, December 13, 2010 7:56 PM
  • Excellent, Karl. Thanx!
    Darrell H Burns
    Monday, December 13, 2010 8:16 PM
  • Darrell,

    While several MVPs have answered the technical quesiton for you, I have one in return for you.

    Why do you need to do this at all? The circumstances when this should be done are rare, such as when merging datasets with conflicting autonumbers - or similar circumstances when this may be appropriate.

    Just as a reminder, in case there is any question, AUTONUMBER VALUES ARE ASSURED TO ONLY SUPPLY UNIQUENESS AND *NOTHING ELSE*!. Using an AutoNumber field to supply Sequential/Serial Values IS DECIDEDLY NOT A RECOMMENDED PRACTICE! (Sorry for SHOUTING, but the emphasis IS warranted.)  There are a number of other ways to achieve this which do not rely on the easily disrupted "sequentiality" (is that even a word?) of autonumber values (which is why relying on their sequential values is so highly _not recommended_).

    If you are using Autonumber values for anything other than supplying UNIQUE values, please let us know what your needs are and we can recommend a few alternative mechanisms which would both be under your control, and _not_ so fragile as AutoNumbers can be.

     


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, December 13, 2010 8:23 PM
  • Mark,

    To reference your question from my other thread (altering data def on linked data source), no I didn't see this response. Here's my scenario:

    I'm executing an import routine from a Master DB, in order to import data into a linked table (Import_Raw). Upon execution, I create an empty Import_Raw from a template table. It has an autonumber field called AutoID. Since I'll be appending new customers to an existing customer table, I want to initialize AutoID to the last customer ID + 1. That's what prompted my question in this thread and the other one.

    Darrell

    PS -- I'm totally with you on not using the autonum to keep a sequential "place". That's one sin I'm not guilty of :-)


    Darrell H Burns
    Monday, December 13, 2010 10:05 PM
  • Darrell,

    OK, just wanted to be sure you knew what you were doing with autonumbers (I've seen many folks misuse them).


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Monday, December 13, 2010 10:08 PM