Introduction.
When Microsoft Access Tables are designed, or Tables in any database systems for that matter, especially the master table, the first thing we consider is to maintain the uniqueness of at least one field content for easier retrieval of information. For example Employee Codes in Employees Table in the Northwind.mdb sample database. Each employee is identified by a unique code number, but who will check for the uniqueness of the numbers entered into the employee-code field? We can validate User inputs for uniqueness with VBA programs before storing them in the table. But, this is already built into the database systems and used in the form of the PrimeryKey Index. This method prevents duplicate keys and arranges the data in Ascending/Descending order.
Usage of Indexes in Programs.
We can use these field values in programs to find the information very quickly. 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 with the name PrimaryKey. Under the PrimaryKey Index, there can be more than one Field. One Table can have several Indexes in the Indexes Group with different combinations of fields as well. If one field alone cannot maintain the uniqueness of information, then we can add other related fields under the required Index Group. We can activate the required Index depending on which way we would like to get the data organized before proceeding with the processing steps.
It is not necessary that the field contents should always be numbers only instead it can be any value like FirstName or LastName fields or both or any combination of field types like text, date, number, etc., except the Memo field. You can give any suitable name for the Index in the Index Name column.
The Recordset's Seek() method is used for search operations on the Table. One of the Indexes should be active before Seek() operation can be executed.
In the above example the rst.Seek "=", 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
No comments:
Post a Comment
Comments subject to moderation before publishing.