- database solutions and downloads for microsoft access

Microsoft Access Data Types

AutoNumber Data Type

The AutoNumber data type stores an integer that Microsoft Access increments (adds to) automatically as you add new records. You can use the AutoNumber data type as a unique record identification for tables having no other unique value.

Data Type Description Field Size
AutoNumber Automatic number increments 4 Bytes

Many people operate under a misconception of what an AutoNumber is. Microsoft Access (Jet, really, the program that moves data for Access) generates unique, sequential AutoNumber when you create records.

So people expect that the AutoNumbers in their records will be sequential. Fair enough. But if you take a look at the behavior of an Access table with an AutoNumber in it you'll see something very different.

Make a new database with one table with two fields, one an AutoNumber field and the other a text field. Create a couple of records. Now delete the last record and then create another one. You'll see that Access doesn't reuse that AutoNumber, it just keeps on going. Now create a record but don't save it. Instead, hit the escape key a couple of times. And then create a new record. You'll see that Microsoft Access won't use the number that was generated for that most recent record, either.

Auto numbers are a great method for insuring that records are uniquely identified. But they were not designed to count records, and they should never be used for such a purpose. If that's what you're trying to do you will be better off looking at the article relating to Automatically Increment a Fields Value in a database

Related Articles: