HOW CA N I change an autonumber field in Access 2000?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JJfrustraded - 14 Feb 2006 17:39 GMT

Sorry, I think this question is very simple, but, I have been reading
Microsoft Help Access and I can't make work it!!! Anybody can give me a
better light in my problem? Here is my case:

I have a Table that has a primary key Customer ID and it has a current
autonumber 23010.
Now I want change that number with a new range example... 60000. My sadness
is when I try complete the instructions from Acces 2003 sample in Microsoft,
It doesn't work. When I try to run the add query I can't get add new record
with the new autonumber. I don't why... Any light please...

Reply to this Message

Klatuu - 14 Feb 2006 18:35 GMT

Access assigns the Autonumbers. This is a bad use of Autonumber. You cannot
rely on their values. Gaps occur anytime you go to a new record and undo it
or if you delete a record.

Autonumbers should NEVER be used for anything other than PK/FK
relationships. Perhaps if you give us the business rule you are trying to
satisfy, we can help with a solution.

> Sorry, I think this question is very simple, but, I have been reading
> Microsoft Help Access and I can't make work it!!! Anybody can give me a
[quoted text clipped - 6 lines]
> It doesn't work. When I try to run the add query I can't get add new record
> with the new autonumber. I don't why... Any light please...

Reply to this Message

Ken Sheridan - 14 Feb 2006 18:43 GMT

You need to append a new row to the table with a value one less than the
number you wish to start at, e.g. with the following 'append' query:

INSERT INTO MyTable (MyID,Firstname,LastName)
VALUES(59999, "Dummy", "Dummy");

Then delete the dummy row.

However autonumbers are really just to guarantee uniqueness. If you want
sequential ranges its best to compute the numbers. You'll find a demo of
mine for this at:

http://community.compuserve.com/n/pfx/forum.aspx?msg=12455.6&nav=messages&webtag
=ws-msoffice

Ken Sheridan
Stafford, England

Reply to this Message