Autonumber field

[Rocky]

I am creating a database, the fields of which will be sent annually to a
national warehouse. I am using an autonumber field for unique identifiers of
records. How does Access assign autonumbers? If a record is deleted, is
there any chance that the previously used autonumber would be reused? Data
sent to the warehouse with an autonumber value previously used, will cause
identity problems.
Tx
rocky

 

[pietlinden]

I am creating a database, the fields of which will be sent annually to a
national warehouse.  I am using an autonumber field for unique identifiers of
records.  How does Access assign autonumbers?  If a record is deleted, is
there any chance that the previously used autonumber would be reused?  Data
sent to the warehouse with an autonumber value previously used, will cause
identity problems.
Tx
rocky

No. Not likely. If the ONLY requirement is that the autonumber be
unique, you should change the Autonumber from Incrementing to Random.
Then if you have to merge large datasets, you are much less likely to
have problems later on.

 

[Jeff Boyce]

Rocky

Will more than one source be sending IDs to the national warehouse?

If not, a deleted Autonumbered record from your database will not get
re-used by Access (but there are ways to circumvent this and re-use an
autonumber).

If you delete a record, how do you let the "national warehouse" know that it
should delete that record?

Another approach might be to add a field you use to indicate that a record
is no longer valid. That way, you don't need to worry about "missing"
autonumbers... or having your database and the national warehouse database
be out of sync.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

"Less likely" is not really adequate. The only way I know of that a number
can be reused is if the record with the highest number is deleted, then the
database is compacted. If data are being sent from several locations to the
national warehouse it will be necessary to come up with something other than
autonumber to uniquely identify the records (perhaps a combination of
autonumber and FacilityNumber). A lot depends on how the national warehouse
is handling the data. If the data are all coming from a single database
then the autonumber value is unlikely to be duplicated, although as I have
described it may be possible in some cases.

I am creating a database, the fields of which will be sent annually to a
national warehouse. I am using an autonumber field for unique identifiers
of
records. How does Access assign autonumbers? If a record is deleted, is
there any chance that the previously used autonumber would be reused? Data
sent to the warehouse with an autonumber value previously used, will cause
identity problems.
Tx
rocky

No. Not likely. If the ONLY requirement is that the autonumber be
unique, you should change the Autonumber from Incrementing to Random.
Then if you have to merge large datasets, you are much less likely to
have problems later on.

 

[Klatuu]

Autonumbers are never reused within a table. Even if a user starts a new
record, but cancels the entry before the record is saved, that number is
discarded.

The only issue you would have is if multiple locations using the system all
send their files to the national warehouse. Then there probably would be
conflicts. IN this case, you might consider using two fields as the primary
key. Add a field to identify the location and make the primary key a
combination of the autonumber and the location identifier.

 

[Rocky]

Thanks so much for the information, now I can proceed.
The data is identified by a combination local number--in my case
autonumber--and submitter number, so contamination problems from someone else
using autonumber is not a problem.
Again, thanks.
Rocky