This is a general question.  I have been using the Autonumber field in 

Access.  I have had trouble using Autonumber field, but believe I have 

gotten help to correct this (through this list, which I appreciate).  

Right now, my databases are really flat files, not complicated.



Is using AutoNumber a good idea?  For example, I've noticed that deleting 

a record doesn't affect the Autonumber, which is never assigned to a new 

record.  This seems it may make searches longer as the database grows.  To 

correct this, I've deleted the AutoNumber field, then recreated it to fill 

in the gaps.  (But this may raise its own problems.)



The question is, based on your experience, do you prefer  an autonumber 

index field or a manual index field?  What are good and bad points?



As always, help is greatly appreciated.



Steve

I have had some heated 'discussions' around this issue.  I sometimes use

autonumbers because they are quick and easy.  They don't however

represent any meaningful information, which I believe is a requirement

for designing a data model that conforms to third normal form.  They

also don't stop the user from entering duplicate records.



For example, if you have a 'user' record, with a 'userid' (autonumber)

as the primary key, the data entry person could enter 'Joe Aussie' in

the 'users' table as many times as they wanted, leading to redundancy.

Some check should be made to ensure that the 'Joe Aussie' record is

unique.



When I do use autonumbers, I ALWAYS hide them from the user.  I find

some users don't like autonumbers that don't stay sequential.  If they

delete a record and the sequence is disrupted, they want the field to

re-order itself to remove any gaps.  This is not a good thing, espically

if you are trying to maintain integrity across many tables.



I think the answer is, "Use autonumbers if you need to, but be aware of

their limitations.  If possible, use another field as a primary key,

especially in associate tables."



That's my 2c.



Padgett







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

From: Stephen Proctor [mailto:steveproctor@c...] 

Sent: Monday, 24 September 2001 5:58 AM

To: Access ASP

Subject: [access_asp] Good Idea to Use Autonumber Field in Access?



This is a general question.  I have been using the Autonumber field in 

Access.  I have had trouble using Autonumber field, but believe I have 

gotten help to correct this (through this list, which I appreciate).  

Right now, my databases are really flat files, not complicated.



Is using AutoNumber a good idea?  For example, I've noticed that

deleting 

a record doesn't affect the Autonumber, which is never assigned to a new



record.  This seems it may make searches longer as the database grows.

To 

correct this, I've deleted the AutoNumber field, then recreated it to

fill 

in the gaps.  (But this may raise its own problems.)



The question is, based on your experience, do you prefer  an autonumber 

index field or a manual index field?  What are good and bad points?



As always, help is greatly appreciated.



Steve

I suggest using both in every table, each for a different reason.



The AutoNumber field adds an extra level of "uniqueness" to each database

record, but I never use it searching and retrieving. The AutoNumber field

tracks of a particular record "slot" despite any other field changes until

it is deleted. It can be used as the unique primary key of a database when

other key fields are not unique. I considerate it a standard data auditing

device, so I can track overwritten or otherwise missing data when the need

arises.



Some sort of logical "manual" key like ClientCode or IdNumber or

AccountNumber is definitely required for linking tables as well as for

effective searching and retrieving.



In my opinion, always put in a AutoNumber field, like RecordID, but don't

use it for searching or retrieving.



-- Zee







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

From: "Stephen Proctor" <steveproctor@c...>

To: "Access ASP" <access_asp@p...>

Sent: Sunday, September 23, 2001 2:57 PM

Subject: [access_asp] Good Idea to Use Autonumber Field in Access?





> This is a general question.  I have been using the Autonumber field in

> Access.  I have had trouble using Autonumber field, but believe I have

> gotten help to correct this (through this list, which I appreciate).

> Right now, my databases are really flat files, not complicated.

>

> Is using AutoNumber a good idea?  For example, I've noticed that deleting

> a record doesn't affect the Autonumber, which is never assigned to a new

> record.  This seems it may make searches longer as the database grows.  To

> correct this, I've deleted the AutoNumber field, then recreated it to fill

> in the gaps.  (But this may raise its own problems.)

>

> The question is, based on your experience, do you prefer  an autonumber

> index field or a manual index field?  What are good and bad points?

>

> As always, help is greatly appreciated.

>

> Steve

>

Steve, 



I'd have to agree with Padgett.  But I don't quite agree with Zee where 

you would include both a autonumber field and a unique identifier in the 

same record, for the exact reasons that Padgett mentioned. 



Zee's recommendation to include an autonumber and identifier in each 

record suggests a transaction based database which may not be relevant to 

your situation, and I do not agree that it is required in every table. 



Particularly take note of Padgett's comments regarding hiding the 

autonumber from the user.  It's drives people nuts seeing missing numbers, 

and these missing numbers aren't relevant to the integrity of the 

database.   



Definately, do not get yourself into the habbit of deleting the autonumber 

field and re-inserting it to maintain an even spread of numbers.  You WILL 

get yourself into a real mess doing this. 



A big advantage of the autonumber field is that it stays unique 'all by 

itself' and you don't need to code a thing to keep it that way.  I have 

worked with databases in the past were code was written to find the 

maximum value and then add 1 to it to create a manual autonumber field.  

This had advantages and dis-advantages, but I personally like it when the 

database does it by itself.



This type of thing comes down to database design.  If you don't have a 

reason to include it, then don't include it. 



Paul