PRB: Index Does Not Work in Access Basic Function w/ NWIND.MDB

Article ID: Q109318
Creation Date: 02-JAN-1994
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access versions 1.0 and 1.1

SYMPTOMS

When you run an Access Basic function that has PrimaryKey defined as an index and that uses the Index method on a table in the sample database NWIND.MDB, you receive the error message:

   'PrimaryKey' isn't an index in this table.

CAUSE

The PrimaryKey index is incorrectly named Primary Key internally (note the space between "Primary" and "Key") in three tables in NWIND.MDB. These tables are the Products, Shippers, and Suppliers tables.

RESOLUTION

When you are running an Access Basic function that uses the Index method on these three tables, refer to the index as "Primary Key" instead of "PrimaryKey."

STATUS

This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

NOTE: This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

Steps to Reproduce Behavior

Open the sample database NWIND.MDB.

Create a new module and enter the following sample function:

      '*********************************************
      'Declarations section of the module.
      '*********************************************
      Option Explicit

      Function NWINDSEEK (Prod_ID As Integer)
         Dim db As Database, TBL As Table
         Set db = CurrentDB()
         Set TBL = db.OpenTable("Products")
         TBL.Index = "PrimaryKey"
         TBL.Seek "=", Prod_ID
         If TBL.NoMatch Then
            MsgBox "Not a valid ID. Try another"
         Else
            MsgBox "This Product ID is in the table!"
         End If
         TBL.Close
         db.Close
      End Function

From the View menu, choose Immediate Window.
Type the following in the Immediate window:
```
      ?NWINDSEEK(1)
```
The above error message displays.
Change the TBL.Index statement in the module to the following:
```
      TBL.Index = "Primary Key"
```
Repeat step 4.

The appropriate message box is displayed.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.