Hi there,

Any idea about how to setting the autonumber field. When I delete the 

records, the autonumber field isn't changed. Why?

Thank you.

Jie

See



http://www.mvps.org/access/general/gen0006.htm



and



http://www.mvps.org/access/general/gen0025.htm



HTH,



-Roy



-----Original Message-----

From: Jie Ray [mailto:lnslz@h...]

Sent: Wednesday, September 05, 2001 4:07 PM

To: Access

Subject: [access] Initialize the autonumber value?





Hi there,

Any idea about how to setting the autonumber field. When I delete the 

records, the autonumber field isn't changed. Why?

Thank you.

Jie

This questions comes up periodically and I guess the best answer is that 

autonumber values, in and of themselves, are, and are intended to be 

meaningless. Their only real use should be to define primary key fields for 

your tables; they should never serve as "real data" (i.e, a client ID 

number), and are generally hidden from the user. And though this second 

point is controversial, some say that ONLY autonumber fields should be used 

to define primary keys (thats my practice), even in situations where primary 

keys are defined using multiple columns, or where a "natural" primary key 

exists (i.e., social security number). The primary reason is that a 

self-incrementing, "unduplicatable" field is the only guaranteed way of 

insuring unique record identifiers. So it shouldn't matter to your database 

if the autonumber sequence of certian tables is interrupted as records are 

deleted; each record maintains a unique identity despite that fact. Other 

reasons include: relationships based on autonumbers are easier to create, 

and indexes based on autonumber fields can be updated faster, which means 

that inserts and updates to tables are processed faster.



If you want to read more about autonumbers, searh for Russell Sinclairs 

article on the Smart Access Newsletter web called "Everything About 

Autonumbers". The article includes a downloadable add-in that will reseed 

your autonumber fields and increment them to a value of your choice. There 

are other ways to do this as well using code. Only drawback is that all 

these procedures (I beleive) require you to delete all the rcords from your 

table first. So none of them has any practical application, so far as I can 

see. Neat, but not too useful.





>From: "Jie Ray" <lnslz@h...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Initialize the autonumber value?

>Date: Wed, 5 Sep 2001 23:07:35

>

>Hi there,

>Any idea about how to setting the autonumber field. When I delete the

>records, the autonumber field isn't changed. Why?

>Thank you.

>Jie

>

>

This is very good advice I think.  The only thing I would add is that when

you use an autonumber primary key you should be sure to also define a unique

index on the natural key.  Otherwise it'd be easy to add all sorts of

duplicate records, and the duplicity (is that the right word?) would be

masked by the uniqueness of the artificial key.



-Roy



-----Original Message-----

From: Bob Bedell [mailto:bdbedell@m...]

Sent: Wednesday, September 05, 2001 7:33 PM

To: Access

Subject: [access] Re: Initialize the autonumber value?





This questions comes up periodically and I guess the best answer is that 

autonumber values, in and of themselves, are, and are intended to be 

meaningless. Their only real use should be to define primary key fields for 

your tables; they should never serve as "real data" (i.e, a client ID 

number), and are generally hidden from the user. And though this second 

point is controversial, some say that ONLY autonumber fields should be used 

to define primary keys (thats my practice), even in situations where primary



keys are defined using multiple columns, or where a "natural" primary key 

exists (i.e., social security number). The primary reason is that a 

self-incrementing, "unduplicatable" field is the only guaranteed way of 

insuring unique record identifiers. So it shouldn't matter to your database 

if the autonumber sequence of certian tables is interrupted as records are 

deleted; each record maintains a unique identity despite that fact. Other 

reasons include: relationships based on autonumbers are easier to create, 

and indexes based on autonumber fields can be updated faster, which means 

that inserts and updates to tables are processed faster.



If you want to read more about autonumbers, searh for Russell Sinclairs 

article on the Smart Access Newsletter web called "Everything About 

Autonumbers". The article includes a downloadable add-in that will reseed 

your autonumber fields and increment them to a value of your choice. There 

are other ways to do this as well using code. Only drawback is that all 

these procedures (I beleive) require you to delete all the rcords from your 

table first. So none of them has any practical application, so far as I can 

see. Neat, but not too useful.





>From: "Jie Ray" <lnslz@h...>

>Reply-To: "Access" <access@p...>

>To: "Access" <access@p...>

>Subject: [access] Initialize the autonumber value?

>Date: Wed, 5 Sep 2001 23:07:35

>

>Hi there,

>Any idea about how to setting the autonumber field. When I delete the

>records, the autonumber field isn't changed. Why?

>Thank you.

>Jie

>

>