Microsoft KB Archive/287459

= ACC2002: &quot;Specified Index Does Not Exist&quot; Error Message When You Set ADO Recordset Index =

Article ID: 287459

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287459



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you use code to select an index and open an ActiveX Data Objects (ADO) recordset, the procedure may fail with the following error message:

Run-time error '-2147217687 (80040e35)'

Specified index does not exist.



CAUSE
This behavior can occur if you are using either the Access OLEDB provider or the MSDataShape provider for the ADO connection for the recordset.



RESOLUTION
To resolve this issue, open your Connection object by using the Microsoft Jet OLE DB provider. If you are running your code in Microsoft Access, you may use CurrentProject.Connection instead of CurrentProject.AccessConnection for your Connection object.



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.

 Start Access and open the sample database Northwind.mdb. In the Database window, click Modules, and then click New. 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).  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 = &quot;Customers&quot; .CursorType = adOpenKeyset .CursorLocation = adUseServer .LockType = adLockOptimistic .Index = &quot;PrimaryKey&quot; .Open Options:=adCmdTableDirect .Seek &quot;PARIS&quot; If Not .EOF Then MsgBox .Fields(&quot;CustomerID&quot;).Value .Close End With End Sub  Save the module as TestConnect.</li> Click any line in the TestConnection procedure, and then press F5 to run it. Note that you receive the error message described in the &quot;Symptoms&quot; section when the code attempts to open the recordset.</li></ol>

<div class="references_section">