Generating primary keys in Access

Every table in an Access database ought to have a primary key to act as the unique identifier for each row in the table. Many database operations process the primary key - you'll be using it to locate and retrieve records and to act as the link between tables - so an efficient key is important. An integer is a very good choice. Integers give you a large range of values but take up little storage space and Access can process them efficiently. Access provides the AutoNumber data type to generate these but there are times when you want to be able to take control and generate your own series of numbers.

Generating your own primary key in VBA code gives you more flexibility. In particular it allows you to:

  • Skip numbers and restart the sequence - for example to start a new year with a new number such as 2007000001.
  • Reuse cancelled numbers to ensure that there are no gaps in the sequence. Accountants in particularly get very worried if there is an invoice number missing.
  • Assign different sub-sequences of numbers to different users or to different offices or regions.
  • Generate composite sequences such as A001..A999, B001..B999.

The usual technique when generating your own sequence is to store the next free number in a table. Whenever a new record is added it takes this number as its primary key and adds one to the numbe stored. In a multi-user system, the table must be locked to make sure that two users do not try to get the same number at the same time.

Example code

This is a simple example of a function to generate sequential numbers. This assumes that the primary keys are stored in a table named 'Key' and that this table has many fields, each one holding the next available value for a different table in the database.

Function GetNewID(strTable As String) As Long
' Description..: Gets a new Primary Key
' Accepts......: The name of the table
' Returns......: A unique ID number

Dim rst As DAO.Recordset
Dim lngID As Long

'-- Open the recordset with Pessimistic locking
'-- gdbs is a public reference to this database

Set rst = gdbs.OpenRecordset(Name:="Key",
    Type:=dbOpenDynaset)
rst.LockEdits = True
rst.MoveFirst
rst.FindFirst "TableName = '" & strTable & "'"
If rst.NoMatch = True Then
  ErrorHandler "Cannot find " & strTable
Else
   '-- Get the next counter then update the table.
   rst.Edit
   lngID = rst!NextID
   rst!NextID = lngID + 1
   rst.Update
End If

'-- Close and shut the table
If rst Is Nothing Then
   '-- Do nothing - we didn't create a recordset
Else
   rst.Close
   Set rst = Nothing
End If

GetNewID = lngID

End Function

Using the function

The function works with a table named Key which holds the next primary key available for each table in the database. It has two fields:

  • TableName - a character field holding the name of a table
  • NextID - an integer field holding the next primary key for that table.

When you add a new table to your database, define the primary key as a Long rather than as an AutoNumber and add a new row to the Key table with name of your new table in the TableName field and the starting point of the primary key sequence (usually 1) in the NextID field.

When you create a new record you can leave the primary key empty until the user decides to save the record. At that point - perhaps in the form's AfterUpdate event - call the GetNewID function and store its value in the table's primary key.