PrimaryKey usage with Many Fields ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When designing a master table in Microsoft Access, it is important to maintain the uniqueness of at least one column value to make data retrieval easier. For example, in the Employees table of the Northwind.mdb sample database, each employee is identified by a unique employee code. But who ensures the uniqueness of the values entered into the employee-code field?

While it is possible to validate user input with VBA before saving the record, database systems already provide this functionality through the use of a Primary Key index. The primary key not only prevents duplicate values but also organizes the data automatically in ascending or descending order.

Usage of Indexes in Programs.

We use these field values in programs to retrieve the specific information very fast. Let us write a small VBA Routine to see how this is used in programs.

Public Function PrimaryKey_Example1(ByVal EmpCode As Integer)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "PrimaryKey" 'activate the Index on Employee Code

rst.Seek "=", EmpCode 'we are looking for the record of Employee Code provided
'Let us test whether the search for Employee Code was successfull or not
If Not rst.NoMatch Then
    MsgBox "EC: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "EC: " & EmpCode & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function

The statement rst.Index = "PrimaryKey" activates the index named PrimaryKey. A primary key index can include more than one field, and a table may contain several indexes within the Indexes collection, each defined with different field combinations. If a single field is not sufficient to ensure uniqueness, additional related fields can be included in the index. You can activate the appropriate index based on how you want the data organized before continuing with the processing steps.

The field contents don't always need to be numbers only; instead, they can be any value, like FirstName or LastName fields, or both, or any combination of field types: text, date, number, etc., except the Memo field. You can give any suitable name for the Index in the Index Name column.

Right-click to open Large Image in New Tab/Window.

The Recordset's Seek() method is used for search operations on the Table. One of the Indexes should be active before the Seek() operation can be executed.

In the above example, the rst.Seek "=", the Empcode statement checks for the Employee Code passed to the Index_Example1() function. The statement looks for the exact match ("=" ) very quickly.

When the Index is defined with more than one field, then search keys must be separated with commas in the Seek() method. Let us modify the above program to provide multiple values for the Index Keys in the Seek method, assuming that FirstName and LastName fields are the members of MyIndex.

Index having more than one Field Value.

Public Function PrimaryKey_Example2(ByVal strFirstName As String, ByVal strLastName As String)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "MyIndex" 'activate the Index

'Search for the Employee record
rst.Seek "=", strFirstName, strLastName

'Let us test whether the search for the Employee was successfull or not
If Not rst.NoMatch Then
    MsgBox "Employee: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "Employee: " & strFirstName & " " & strLastName & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function