Posted: 28 Jan 2010 05:47 AM PST
If you open the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database in design view you can see that the EmployeeID Field is defined as Primary Key.
To create an Index manually and to define it as Primary Key:
If the Record Values in the selected field are not unique then you can select more data fields (up to a maximum of ten Fields) to form Unique Key for the Primary Key.
You may click and drag over the Fields to select them (if they are adjoining fields) or click on each field by holding the Ctrl Key to select fields randomly.
The above procedure is for creating a PrimaryKey Index for the Table. We can create more than one Index for a Table. But, only one Index can be active at one time.
We can activate an existing Index of a Table or create new Index through VBA and use it for data processing. We will learn here how to create a new Index with the name myIndex for a Table through VBA, activate it and use it for data processing and delete it at the end of the process.
We must validate the presence of myIndex in the Indexes collection of the Table, if found then activate it, otherwise create myIndex and activate it for data processing.
We will use Orders and Order details Table from Northwind.mdb sample database. We will organize the Order Details Table in Order Number sequence so that Order-wise Total Value of all items can be calculated and updated on the same Order record in Orders Table.
Following are the data processing steps which we follow in the VBA Routine to update the Orders Table with order-wise Total Value from Order details Table:
You may display the Index List of this Table to view its PrimaryKey Index on Order ID field.
At the beginning part of the Code we are attempting to make one of the Indexes (myIndex) of Order Details Table active. Since, myIndex is not yet created on the Table this action runs into an Error condition. we are trapping this Error Code and passes control to the Sub-Routine to create myIndex and to add it to the Indexes collection. The new Index is activated in preparation for data processing.
Next steps calculate Order-wise Total Values and updates on Orders Table.
At the end of the process myIndex is deleted from the Indexes Collection of Order Details Table.
|You are subscribed to email updates from LEARN MS-ACCESS TIPS AND TRICKS |
To stop receiving these emails, you may unsubscribe now.
|Email delivery powered by Google|
|Google Inc., 20 West Kinzie, Chicago IL USA 60610|