This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines

Autonumbers--What they are NOT and What They Are

What they are NOT

1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers (see note 1 , below).
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded (exception: see note 2 , below).
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table (see note 5 , below).
11. Necessarily used to determine the default order the rows may be returned from the table (see note 6 , below).
12. Indicative of or related to any TimeStamp field that may also be in the table row (see note 7 , below).

What they ARE

1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access (see note 3 , below).
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row (exception: see note 4 , below).

Notes:

1. In a replicated database system, the autonumbers generated for subsequent rows being inserted are random numbers having either a positive or negative sign (and thus are not a series of always increasing values).
   
   
2. By doing a Database Compact operation**, and if the table is not in a replicated table, any autonumbers that were both A) used by rows that were deleted from the table, AND B) greater than the currently highest-valued remaining autonumber in that column, WILL be reused/reassigned for subsequent new rows added to the table after the Database Compact operation completes.
   
   ** (Note added as requested by Walter Niesz 2008-07-10) Microsoft Jet Service Pack 4 changed this behavior. A Database Compact operation will only reset the AutoNumber to one (1) when there are NO RECORDS remaining in the table. If there are any records remaining in the table, a DB Compact operation will have no effect on the next available Autnumber. Please see this Microsoft KB article if you need to achieve this behavior.
   
   
3. A new autonumber is "instanced" when you open a form in Insert New Record mode.
   That means that a new autonumber is generated by Access and "assigned" to that particular record (or row). However, the row does not yet have any data in it (since you've not typed any data yet), and if you then cancel the "insert operation" (by pressing the ESC key, for example) before the row is actually written to the table, the autonumber just assigned is discarded and not assigned to some other "new record". This is one common reason for "gaps" in autonumber series.
   
   Another reason for gaps is that one or more rows were deleted from the table, and since deleted numbers are not reassigned (except as noted in note 2 re Compact Database operations), you can not eliminate the gaps without performing (unnatural) acts such as dropping/recreating the autonumber column or creating a new table and then inserting the rows into it. And neither procedure is customarily recommended, since it results in the renumbering of existing rows, thus playing serious havoc with any Foreign Key relationships in other tables that link to the table being renumbered.
   
   
4. About the only time a table's autonumber column might be validly resequenced is when:
   1. an application is being "put into production" AND
   2. there are NO rows in other tables that have Foreign Keys linked to the autonumber column, AND
   3. you just "want to start with a clean slate" of numbers.
      
      
5. A bit of history re what is returned by a query or Select statement:
   Back in the mid-70's while the IBM Research folks were working on the idea of storing data in relationally-linked tables, they realized that while people might refer to "storing records in the database", the actuality was that the ("record") information was actually just being stored in some internal format that had no definitive "record" attribute.
   
   And (contrary to a common saying concerning ducks) that while what was returned by a Select/Query statement might look like a record, might smell like a record, and might feel like a record, it was in fact a "calculated result" that was obtain by gathering data from potentially (in the research product, anyway) up to 16 different "tables". Thus, you could "get records out" that had never been "put in" the database.
   
   And they came up with the word "tuple" to represent these "pseudo-record-results". And if you wanted the tuples returned in a particular "tuple order", you had to specify a data column that identified what that order was (and thus the ORDER BY clause was invented). (And we were an early user of their initial test product and used to laugh at just storing our budgets "in a pile" and getting them back "in a jumble"!) However, the word Tuple was apparently too difficult a "sell", and I see that the results that are returned by Selects and Queries are customarily being referred to as records or recordsets. (I prefer rows and rowsets, personally, but find that I occasionally slip back to the record/recordset references.)
   
   
6. Order of Rows Returned by Access:
   
   I certainly do not claim to know the internals of how Access determines the "return order" of rows, but I do recall having a certain degree of exposure to the internal workings of three other rdbms's, and see no reason to believe that Access is very different. And this may be "old news" to a lot of you, but I just thought I'd lay it out for discussion for true Access experts to shoot at.
   
   And as I see it, there are two parts to be considered here:
   1. How things are numbered and sequence-of-storing-data during the insert/load phase, and
   2. How things are numbered and sequence-of-rows-returned during the retrieval phase.

So, with that in mind, here's some comments re the situation:

First, a bit of "level-setting" to state some fundamental facts that I believe apply, and if they are incorrect, I'd really like to hear about it:

1. Specifying a table name as a Record Source for a form is the same (regarding the way the rdbms returns rows) as specifying "Select * from tablename", but is "less than" specifying a saved query containing "Select * from tablename" (since a saved query can have a stored "data materialization path plan" - see below).

2. Autonumbers may increment in an ascending manner, but "holes" in the series may occur if any records are deleted (or not inserted) after an autonumber value has been assigned to a row.

3. While you may deliver rows (records) to the rdbms in some fixed order, you truly do not know how the rows are stored in the database by the rdbms. And in fact they may be stored "all over the place" and you won't know it. And SQL provides NO operator that says "ORDER BY SEQUENCE THAT ROWS WERE GIVEN TO RDBMS". If you want a particular order of row sequencing, you have to provide a column of data that contains values that are appropriate for the sequence order desired, and then you have to refer to said column in an ORDER BY clause. (Yes, I know that GROUP BY clauses can imply an order by, but I'm trying to keep things simple here.)

4. Unless you specifically provide an ORDER BY clause in the select statement, the delivery-sequence of the resulting rows of the "data materialization phase" of the process will be determined by the rdbms during a pre-delivery processing phase which evaluates (most/many/some/?) of the various ways of "collecting" the requested data for delivery to the requestor.

This phase can be (loosely) called the "DATA PATH PLAN OPTIMIZING" phase and is very closely "tied" to the number and location of the tables involved, whatever indexes are available for use, the "selectivity factor" of the indexes, the percentage of total table rows being retrieved/examined, whatever columns are being used as JOIN points with other tables or queries, the approximate number of "data rows in the table" that are stored in a "natural ascending order" (I've forgotten the actual technical name of this table attribute, but I have definite memories re arguing its merits some 15-20 years ago with certain DBMS designers), and probably a host of other factors.

OK, 'nuf re level setting. Now to the devil-is-in-the-details bit:

1. The point that I'm trying to make is that the resulting "pile" of records that is delivered to you will be (navigationally) sequenced 1,2,3,4... IN THE ORDER THEY ARE FOUND IN THE DELIVERED PILE OF ROWS, and not necessarily in the order they were added to the table. Thus, these 1,2,3,4 "navigation numbers" have no (absolutely-good-all-the-time) logical "connection" to any autonumber that is assigned during the record insert operation. And you can prove this by select only the ODD recordid's from the table. You will still get a 1,2,3,4,... set of navigation numbers, but this time for the new set of rows that are only the odd recordids. r you can sort the pile in a descending manner. You still get the 1,2,3,4... navigation series, but "from some other beginning of the rowset.

2. Another consequence of the PLAN OPTIMIZATION PHASE is that what was a good "retrieval plan" yesterday may be replaced by a different one today, and the order of the rows being returned may change (unless you've specified an ORDER BY clause). And this is done without notifying you (or the program) that it is happening.

3. Why would a perfectly good plan get replaced, you may ask. Well, that's a good question, and there are probably dozens of answers, but here's a few candidates that I might suspect:

a. An index involved in the (good) plan was dropped/modified. (And the dropped index may not have even been used by the "good" plan!) b. An index was added to a table involved in the plan. c. A database decompile was done (I think) d. A database repair was done (maybe, don't know about Access...) e. A query that was used as a base for this query was modified. f. A table used in the query had a relationship added/deleted/modified. g. A Select is being processed (as opposed to a Query that was saved as a Query Object in the Database View Window). h. A Table is specified as a Record Source (iffy...I don't know if Access stores precompiled plans for tables or not, but I'd guess not). i. and I'd guess that there are several dozen other scenarios not covered above...

4. And that brings up the following point:

If a precompiled plan does not already exist for a table/select/query, then by definition Access will have to go through the plan determination process in order to find out how to best "materialize" the rowset result. And the topology of the database may be different today from what it was yesterday. And the resulting plan may also be different, and with a different sort order.

7. Scenario re Autonumbers vs. Timestamps...

Preconditions:

All times noted below are before noon, and Form1 includes a timestamp field that is set in the Form's BeforeInsert and BeforeUpdate event procedures (where the system clocks in all machines involved are somehow magically "in sync").

Situation:

8:00 - Jack, on Mach1 using FE1 and connected to BE_1, opens Form1 and moves to "new record" (thus getting an autonumber 1 assigned).

8:01 - Jane, on Mach2 using FE2 and connected to BE_1, opens Form1and moves to "new record" (thus getting autonumber 2 assigned).

8:02 - Jane completes her data entry and moves on to next "new record",

         thus A) setting timestamp of 8:02 in the tuple with autonumber = 2, and B) gets the next autonumber (which is 3). 

8:03 - Jack completes his data entry and moves on to next "new record",

         thus A) setting timestamp of 8:03 in the tuple with autonumber = 1, and B) gets the next autonumber (which is 4). 

8:04 - Jack cancels his "new record" status and moves back to his previous record (which is autonumber 1)

8:05 - Jack finishes looking at his "nbr 1" record and move on to "new record" again, but this time gets autonumber = 5 assigned.

8:06 - Jack finishes his data entry and closes the form, thus setting timestamp of 8:06 into the tuple with autonumber = 5.

8:07 - Jan completes her data entry and closes the form, thus setting timestamp of 8:07 into the tuple with autonumber = 3.

Result:

NewID Timestamp

1 8:03

2 8:02

3 8:07

5 8:06

and this illustrates how autonumbers do not even indicate sequence of "insertion into the table".

So to recap

1. Autonumbers are great, but you can not rely on the rows always having one contiguous series of numbers starting at 1 and incrementing by 1 to the number of rows in the table.

2. The primary use of autonumbers should be as the PK of a table, and then as the PK side of PK/FK relationship links.

3. Autonumbers should not be made visible to the users of the application.

4. Unless you specify an ORDER BY clause (either in the select or query or form or report order by property or via a GROUP BY clause), you truly have no idea what order the rows will be returned to you, and if they happen to be delivered in the desired sequence, then you are lucky (today, but there are no guarantees about the future).

5. If you want an ordered report, you must list the necessary fields to sort on, and autonumber fields are not considered to be good candidates for inclusion in this list.

This page was originally ported from the UtterAccess Forums. It is based heavily or in part on a post by jinky44.
Autonumbers - what they Are and Are Not...
(With special thanks to R. Hicks, r_cubed, mvos, uno1980 and NoahP)