An alternative to AutoNumbers

An alternative to AutoNumbers

In order to utilise Access properly, you will need to use relationships between tables, and to do this, you will need to ensure that there is a unique field in the one side of each 'one-to-many' relationship.

If there is a natural key for the table, then that may be used. However, there might be the problem that although there is a unique value for each object, at the time of entry this value isn't known. Therefore, you must use an artificial key. Access supplies its own built-in unique field, in the shape of an Autonumber. However, there are various problems with using an autonumber:

Firstly, you shouldn't be revealing the Autonumber field to the user in a database, and expect it to have any meaning. The Autonumber should really only be used internally by Access to relate records in relationships.
With A2K, there is the possibility that this number might not be unique. Look at article Q257408 on the Knowledge Base for further information.
Autonumbers are not guaranteed to be consecutive. Firstly, in a database that is replicated, the Autonumber NewValue is set to Random from increment, in order to try and minimise the chances of two users in different replicas creating different records with the same primary field. Secondly, if the entry of a new record is interrupted, the primary key will already have been allocated, and therefore when the next record is added successfully, it is not given the value of the uncommitted record, but the one after that. Once a autonumber has been created and then deleted, there is no way to reuse that number.

You can create your own unique primary key, using DMax, called in the BeforeInsert event for the Form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UniqueFieldName=Nz(DMax("[UniqueFieldName]","[TableName]"),0)+1
End Sub

Another option that you might want to consider is creating a single field table, and storing the unique value in there until it is needed, at which point it is read out, and a new value created instead in the table.

A question that is often asked is how to reset the autonumber back, so that it starts incrementing from 1 again. Well, firstly, if you are using the autonumber datatype properly, then the value of the field should have no meaning whatsoever. Anyway, to reset the autonumber, delete all of the data from the table, and then compact your database. Now the first record that you enter into this table will start off at 1.

Something else that seems to be popular is to try and create and primary key field that uses the date in conjunction with an autonumber, for example 010120010001 might be the first record that was created on 1 Jan 2001, whilst 050220010015 would be the fifteenth record created on 5 Feb 2001. This is a very bad idea - the idea of relational databases is to store one piece of information in each field. If you wanted to do something like this, you would need to modify the code above slightly, and add a date field to the table:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UniqueFieldName = Nz(DMax("[UniqueFieldName]", "[tblUnique]", "[DateField]=Date()"), 0) + 1
Me!DateField = Date
End Sub

You can then create a primary key for the table that is based on both the DateField and UniqueFieldName by selecting them both in the design view of the table by using the Control key and then clicking on the Primary Key symbol. You will then have a number that increments each day, and resets itself the next day.

Top

Last modified at 06/06/2006 15:01:39