AutoNumber Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brigitte P - 13 Feb 2004 14:02 GMT

Have a volunteer database with several tables. One is
Organizations where any new entry has an ID designated
with an AutoNumber which is also the primary key. When
data is entered, the related tables (e.g. Contribution)
automatically takes that ID (created in the original table
by the AutoNumber) as the related field. The tables are
related in a one to many relationship (Organization 1;
Contributions many). The contributions table has as
primary key its own IDNumber, again created with the
AutoNumber simply to provide a place for a primary index.
Referential integrity is enforced on table level.
All works fine, except that I read someplace that upon
compacting the dbase, AutoNumbers can be reassigned to be
consecutive. Here is my question:
1.Is my thinking accurate that even in a split database,
as long as referential integrity is enforced on the
backend, AutoNumbers are not reassigned when related
records exist (otherwise it may mess up things badly)?
2.Is there a way to prevent that AutoNumbers are
reassigned upon compacting?
3.Is there a difference between A97, A2000, A2002 in
regard to AutoNumbers, since I understand that A2002
automatically compacts databases when the dbase is close.
Thanks for your thoughts on this.
Brigitte

Reply to this Message

Jeff Boyce - 13 Feb 2004 15:01 GMT

Brigitte

To the best of my knowledge, NONE of the versions compact automatically
(although you can check a checkbox in Tools|Options to make this happen in
'02) ... and any time you do a Compact/Repair, you risk losing your db
unless you've backed up first.

Autonumbers in Access are intended as unique row identifiers, so there's
rarely a need to show them to users or try to make sense of them. And if
you've set up related tables, using the primary key (autonumber) from table1
as a foreign key in table2, "re-assigning" autonumbers in table1 will hose
your table2 relationship!

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Steve Schapel - 14 Feb 2004 02:38 GMT

Brigitte,

Basically, there are no grounds for your worries. No, autonumbers are
not re-assigned in linked tables. No, autonumbers are not "re-assigned"
upon compacting... the only thing remotely resembling this is that in
Access 97, but not so in later versions, compacting will reset the "next
number" to one more than the maximum number in the case of records being
deleted at the end of the autonumber sequence, but even this would not
pose any threat to your data integrity. In Access 2000 and later, you
can select to Compact On Close... but this is "by the way" as regards
your question, as autonumber data will not be affected.

Signature

Steve Schapel, Microsoft Access MVP

> Have a volunteer database with several tables. One is
> Organizations where any new entry has an ID designated
[quoted text clipped - 21 lines]
> Thanks for your thoughts on this.
> Brigitte

Reply to this Message

Brigitte P - 16 Feb 2004 19:50 GMT

Thanks, I'm reassured. The dbase is right now in A97, but will be converted
as soon as our IT folks have the newer software installed, either A2000 or
A2002. I just wait with compacting until we upgraded from A97.
Brigitte

> Brigitte,
>
[quoted text clipped - 33 lines]
> > Thanks for your thoughts on this.
> > Brigitte

Reply to this Message