Visit our new web pages

We have launched a new extended set of web pages at www.alvechurchdata.co.uk. These old pages will stay on the server because people still have links to them but they will not be updated.

The new version of this page is at www.alvechurchdata.co.uk/hints-and-tips/acckey.html.

Generating primary keys in Access

Every table ought to have a primary key which acts as a unique identifier for each row in the table. You'll be using the primary key to locate and retrieve records and to act as the link between tables so an integer is a good choice. They 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 in code the primary key gives you more flexibility and allows you to:

Skip numbers to start a new year with a new sequence.
Generate multiple sequences such as A001..A999, B001..B999 for the same field.
Reuse cancelled numbers to ensure that there are no gaps in the sequence
Assign different number sequences to different users

Example code

This is a simple example of a function to generate sequential numbers:

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.

The Site Search feature requires JavaScript. If your browser does not support Javascript then please use our Site Map instead.

Hints & tips

The textbox class in Visual FoxPro 9 has a new Autocomplete property which shows the user the previous values that have been entered in that textbox.
Autocomplete in VFP 9

Your Access database will look more impressive if you add custom toolbars...
Custom toolbars

FoxPro has always had functions to read and write files at a low level...
Foxpro low level file functions

More...
More pages of hints and tips for users of Microsoft FoxPro and Access databases.

Site Map