Append Autonumber

[RabSwinney]

I have an append query changing the starting number of a autonumber field in a table, the number goes into the table when i run the query, but the autonumber still follows on from the original autonumber.

 

[dhookom]

I don't know what you mean by "still follows on from the original autonumber". Also, if you have a query that doesn't seem to work, it is advisable to post the SQL to the forum.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RabSwinney]

Say the original Autonumber was 1 and I want to start it at 1000, I run the append query and the 1000 goes into the table, but the next autonumber is 2.

 

[bboffin]

An Autonumber field is just that - an automatically generated number. You have no control over what number gets generated and you cannot override it by providing a value for the column.

You cannot even reset it by deleting all the records in the table. You have to run database compact and repair as well.

SQL Server or MSDE tables use a concept called Identity Columns and these have a few more options such as the starting value and the increment size but once the first record has been written the same rules apply as to Access tables.

 

[r937]

"you cannot override it by providing a value for the column"??

yes you can

try this:

[tt]create table a
( id counter not null constraint a_pk primary key
, txt varchar(10)
)

insert into a ( txt ) values ('a')
insert into a ( txt ) values ('b')
insert into a ( id, txt ) values (26,'z')
insert into a ( txt ) values ('aa')[/tt]

now do SELECT * FROM a and see what value id 'aa' got

rudy

http://r937.com/

SQL Consulting

 

[dhookom]

I agree that you can "seed" the autonumber with a value. I just did a test on a table and inserted 1000 into the autonumber field. I then opened the table and added a new record. The autonumber field correctly incremented to 1001.

I rarely care what the value of an autonumber field is since it only needs to be unique. My users never see this field so it makes no difference what value is being stored.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JeremyNYC]

I've just written up my take on autonumbers. I definitely feel that if you're manipulating the value of an autonumber, you're doing something wrong. The value shouldn't matter at all.

The write up is in the Developers' section of my website.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.