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

Answer 1

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!)

Answer 2

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

Answer 3

"DarrellDoesData" wrote in message

news:[e-mail]...

> 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

Answer 4

Excellent, Karl. Thanx!

---

Darrell H Burns

Answer 5

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)

Answer 6

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

Answer 7

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)

Answer 8

Forget sequence, did it ever occur to you that maybe a designer might need to enforce a given character length for the number should it be cast to a CHAR value? Ever played ball with a mainframe?