PrimaryKey usage with Many Fields.
When designing a master table in Microsoft Access, ensure that unique values are in a column 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 that the uniqueness of the values entered into the employee-code field?
It is possible to validate user input using VBA before saving the record. Database systems already have this functionality of a primary key index. The primary key prevents duplicate values and 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 Primary Key Field contents don't always need to be numbers only; they can be any value such as 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 to the Index in the Index Name column and use it to activate a specific Primary Index.
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 an 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 use 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
