Access Autonumber NOT Unique

[Sheldon Penner]

I recently installed an archiving system to a client's Access database which
copies records from certain tables to equivalent tables in a separate archive
database. Since these tables are related by key fields and foreign keys, I
copied the autonumbered ID fields of these tables as Longs into the new
tables. In doing so, I relied on the uniqueness of autonumber fields to
enable me to restore these records to the original database if required.

Unfortunately, Access Autonumbers are not unique. If a record is deleted,
its autonumbered ID number will be reused, making it impossible to restore
the original record and records in other tables related to it via foreign key.

The notion that autonumbered fields are unique and will continue to
increment upward even when lower numbers have been deleted is a common
misonception that I have seen several times in forums.

I'd be grateful for any suggestions on how to deal with this situation in my
archiving program.

 

[Allen Browne]

In general, this is how autonumbers work in Access 2000 and later:

1. An AutoNumber field has a Seed property, defining the next value that
will be assigned.

2. When you start entering a new record, the seed value is assigned, and the
seed is incremented.

3. Even if you abort the new entry at this point, the assigned value is not
reused.

4. If you compact/repair the database, the seed may be reset to 1 more than
the highest number already used. (This is version specific: in some
versions/service packs, the seed is not reset unless you deleted all the
records from the table.)

5. There are some cases where Access sets the seed wrongly, such as when you
use an append query to explicitly assign a value to an AutoNumber field in a
linked table. Examples and info on how to fix this bug:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

6. AutoNumbers are not necessarily unique, e.g. you can assign a duplicate
into an AutoNumber column by assigning a value in an Append query. To
guarantee uniqueness, you must use a unique index or make the column the
primary key.

7. AutoNumber values are generally not reused after deletion. However, they
could be if you explicitly append a record with a specific value for the
AutoNumber column, or if you delete some of the most recently added records
and then compact the database.

If you are moving records into an archive database, you will therefore need
to deal with the possibility of duplicate numbers. One way around this might
be to assign a unique batch number to each move. The target tables would
then have 2 Number fields rather than an auto number, e.go. BatchID +
ClientID as the key. The combination of these 2 fields would be unique in
the table on the one-side of the relationship in the archive database, so
could be the primary key. The related tables would then be joined on the
combination of the 2 matching fields as foreign key.

If you don't like that, you may have to roll your own instead of using an
AutoNumber. This would involve an extra table for every table that needs a
quazi-autonumber. This table contains only the highest value used so far.
You would write a routine that locks the extra table, increments the number,
assigns the number to the new record, and then unlocks the extra table,
including random pauses and limited retry timeouts to handle multi-user
clashes. Since this table is not reset by a compact repair, it would solve
the compacting issue. It would not solve the issue of values being
reassigned explicitly by Append queries.

 

[Sheldon Penner]

Thank you for responding to my posting, Allen.

I think, though, that a number of points you make are mistaken. I am using
Access 2003 with "Access 2000 file format". Perhaps the behavior I describe
is specific to this combination. My archiving removed the oldest records
that had not been accessed in at least 5 years. These included records
numbers 2, 5 and 6. Since one of the purposes of archiving was to decrease
the size of the file, it was compacted after the deletions.

The highest autonumber currently in use in one of the three archived tables
is 2843, but the new autonumbers recently added include 2, 5 and 6. The next
record to be added will be 8. The autonumber fields in all three tables are
key fields with unique indexes.

So, the uniqueness of numbers in an autonumbered field, even a key field
with a unique index, will be violated when records are deleted and the file
is compacted. Autonumbering will continue not at one above the highest
number currently in the table, but at the lowest unused number.

I like your idea of an additional field indicating the archive batch or the
archive datetime stamp. I'll probably adapt something along those lines.

 

[Allen Browne]

No version of Access goes back and fills the holes in the way you describe,
so something else must be going on here.

The Seed must be being reset to a lower number somehow. This could happen by
setting the Seed directly, or by an append query specifying the number to
insert, it could happen as a result of one of the bugs I referred to. If you
have records where the numbers are 1, 3, and 4, and the database inserted
new records with the numbers 2, 5, and 6, and it was not through an action
query, then the Seed must have been reset. I would suggest that the attempt
to insert 3 and 4 must have failed. Perhaps there were suppressed error
message here.

It would be worth your effort to dig in and find out what's going on.
For example, ask it what the Seed property is currently set to:
http://allenbrowne.com/func-ADOX.html#GetSeedADOX

 

[Sheldon Penner]

Thank you for your further response, Allen, and especially for those ADOX
routines which will be very useful.

I used the GetSeedADOX routine to determine that the seed in my [Patients]
table is currently 9 (the maximum PatientID is 2843); the seed in [Doctors]
is 159 (the maximum MDID is 828); and the seed in [Info Callers] is 1803 (the
maximum CallerID is 9134).

Possibly, this came about when I tested restoring a number of archived
records using, in the case of [Info Callers], the following append query:

PARAMETERS CallerID Long;
INSERT INTO [Info Calls]
SELECT arcInfoCalls.*
FROM arcInfoCalls LEFT JOIN [Info Calls] ON arcInfoCalls.CallerID = [Info
Calls].CallerID
WHERE (((arcInfoCalls.CallerID)=[CallerID]) AND (([Info Calls].CallerID) Is
Null));

I believe I can undo the damage that has been done by increasing the ID
field in each of the 3 archive tables, as well as their associated foreign
keys in other tables, by a set amount (the key fields in the archive tables,
of course, are not autonumbered), then resetting the seed in each of the
source tables to 1 greater than the highest number in the archived tables,
assuring that I can restore these records in the future without encountering
a duplicate value error.

Further, I might revise my archiving routine to reset the seed in each of
the source tables to 1 greater than the highest existing id number before
executing the deletions.

I'd be very interested in your response to this approach to the problem.

Best regards,

 

[Allen Browne]

Yes: that makes sense.

As I pointed out earlier, there is a bug in Access where it sets the Seed
incorrectly when specifying the value to use for the AutoNumber field in an
Append query.

It's the 2nd cause listed here:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

And the ADOX fix is there as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your further response, Allen, and especially for those ADOX
routines which will be very useful.

I used the GetSeedADOX routine to determine that the seed in my [Patients]
table is currently 9 (the maximum PatientID is 2843); the seed in
[Doctors]
is 159 (the maximum MDID is 828); and the seed in [Info Callers] is 1803
(the
maximum CallerID is 9134).

Possibly, this came about when I tested restoring a number of archived
records using, in the case of [Info Callers], the following append query:

PARAMETERS CallerID Long;
INSERT INTO [Info Calls]
SELECT arcInfoCalls.*
FROM arcInfoCalls LEFT JOIN [Info Calls] ON arcInfoCalls.CallerID = [Info
Calls].CallerID
WHERE (((arcInfoCalls.CallerID)=[CallerID]) AND (([Info Calls].CallerID)
Is
Null));

I believe I can undo the damage that has been done by increasing the ID
field in each of the 3 archive tables, as well as their associated foreign
keys in other tables, by a set amount (the key fields in the archive
tables,
of course, are not autonumbered), then resetting the seed in each of the
source tables to 1 greater than the highest number in the archived tables,
assuring that I can restore these records in the future without
encountering
a duplicate value error.

Further, I might revise my archiving routine to reset the seed in each of
the source tables to 1 greater than the highest existing id number before
executing the deletions.

I'd be very interested in your response to this approach to the problem.

Best regards,

No version of Access goes back and fills the holes in the way you
describe,
so something else must be going on here.

The Seed must be being reset to a lower number somehow. This could happen
by
setting the Seed directly, or by an append query specifying the number to
insert, it could happen as a result of one of the bugs I referred to. If
you
have records where the numbers are 1, 3, and 4, and the database inserted
new records with the numbers 2, 5, and 6, and it was not through an
action
query, then the Seed must have been reset. I would suggest that the
attempt
to insert 3 and 4 must have failed. Perhaps there were suppressed error
message here.

It would be worth your effort to dig in and find out what's going on.
For example, ask it what the Seed property is currently set to:
http://allenbrowne.com/func-ADOX.html#GetSeedADOX

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Tony Toews [MVP]]

I used the GetSeedADOX routine to determine that the seed in my [Patients]
table is currently 9 (the maximum PatientID is 2843); the seed in [Doctors]
is 159 (the maximum MDID is 828); and the seed in [Info Callers] is 1803 (the
maximum CallerID is 9134).

For so few records I wouldn't bother archiving. I'd consider
archiving when over a million records or so.

Consider instead just putting an Inactive flag on the appropriate
records.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/