ACC2002: "Specified Index Does Not Exist" Error Message When You Set ADO Recordset Index (287459)

MORE INFORMATION

The CurrentProject.AccessConnection property, which is new in Access 2002, returns an ADO Connection object that uses the Microsoft.Access.OLEDB.10.0 provider. Neither this provider nor the MSDataShape provider supports the IRowsetIndex interface. The CurrentProject.Connection property returns an ADO Connection object by using the Microsoft Jet OLEDB provider, which does support the IRowsetIndex interface.

If your program requires indexes, you need to use CurrentProject.Connection as the ActiveConnection property. When you write code to open a recordset in this way, it is also important that you use adUseServer as the CursorLocation property, as in the following example.

Steps to Reproduce the Problem

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Access and open the sample database Northwind.mdb.
2. In the Database window, click Modules, and then click New.
3. On the Tools menu, click References. Make sure that the Microsoft ActiveX Data Objects 2.x Library item is included and its check box is selected in the Available References box (where 2.x is version 2.1 or later).
4. Type or paste the following code:

   Sub TestConnection()
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
   
      Set cn = CurrentProject.AccessConnection
      Set rs = New ADODB.Recordset
      With rs
         .ActiveConnection = cn
         .Source = "Customers"
         .CursorType = adOpenKeyset
         .CursorLocation = adUseServer
         .LockType = adLockOptimistic
         .Index = "PrimaryKey"
         .Open Options:=adCmdTableDirect
         .Seek "PARIS"
         If Not .EOF Then MsgBox .Fields("CustomerID").Value
        .Close
      End With
   End Sub
   					

5. Save the module as TestConnect.
6. Click any line in the TestConnection procedure, and then press F5 to run it. Note that you receive the error message described in the "Symptoms" section when the code attempts to open the recordset.