Re: Access Autonumber NOT Unique

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon Penner" <Sheldon Penner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:958EA5B8-F6E2-4B29-BB46-3E73B3FD411C@xxxxxxxxxxxxxxxx
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.

.

Relevant Pages

  • Re: book publishing database--design question about tabbed forms
    ... Building a database is like building a house... ... if you care about that Autonumber then best to ... which my query and tabbed form became essentially read-only--unable to be ... click a desired tab. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Set value of one field depending on result of next record in q
    ... The autonumber issue should not be a problem. ... table" query to deal with the issue that S Clark offered a solution with. ... > See http://www.QBuilt.com for all your database needs. ... >> Michael Bond ...
    (microsoft.public.access.queries)
  • Re: Renumbering records
    ... Autonumber is just a special kind of default value, ... represent the sequence in which the records were added. ... I wanted to maintain that order and I thought the database ... That would be a regular replica, ...
    (microsoft.public.access.replication)
  • Re: Non-unique KeyIDs (Autonumber) in Access 2000
    ... The problem is creted by a database tool we developed to help fix our ... Dim WS As Workspace, DB As Database, TempDB As Database ... it creates an autonumber than is the same as an existing ... >> We have a problem with an Access 2000 table where the KeyID field was ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Simple between and query
    ... What I figured out on my own was to created the ID as an autonumber. ... school database Students and Courses need to be in separate tables. ... hide original message or reply text. ... Phonebook table and a FaxNumber: ...
    (microsoft.public.access.queries)