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
Dim rst
As DAO.Recordset
Dim lngID
As Long
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
rst.Edit
lngID = rst!NextID
rst!NextID = lngID + 1
rst.Update
End If
If rst Is Nothing Then
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:
-
- a character field holding the name of a table
-
- 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.
|
