Microsoft KB Archive/290060

= You cannot set Index property of ADO recordset that is based on a Microsoft Jet database =

Article ID: 290060

Article Last Modified on 8/10/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q290060



Advanced: Requires expert coding, interoperability, and multiuser skills.

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

For a Microsoft Access 2000 version of this article, see 249683.



SYMPTOMS
When you try to set the Index property of an ActiveX Data Objects (ADO) recordset that is based on a table in a Microsoft Jet database, you may receive the following error message:

Run-time error '3251':

Current provider does not support the necessary interface for Index functionality.



CAUSE
You are trying to set the Index property of a linked table within the database.



RESOLUTION
Open a separate ADO connection to the back-end database, and then open the table directly instead of using the linked table. To see an example of how to do this, follow these steps.

NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

 Follow steps 1 through 7 in the &quot;Steps to Reproduce Behavior section&quot; later in this article.  Modify the procedure created in steps 1 through 7 in the &quot;Steps to Reproduce Behavior section&quot; later in this article as follows: Sub LinkTableSeek Dim cn As ADODB.Connection Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection With cn       .Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; .ConnectionString = &quot;C:\Program Files\Microsoft &quot; _ & &quot;Office\Office10\Samples\Northwind.mdb&quot; .Open End With Set rs = New ADODB.Recordset With rs       .ActiveConnection = cn        .Source = &quot;Customers&quot; .CursorLocation = adUseServer

.CursorType = adOpenKeyset .Open Options:=adCmdTableDirect .Index = &quot;PrimaryKey&quot; .Seek &quot;WOLZA&quot; If (Not .EOF And Not .BOF) Then MsgBox rs.Fields(&quot;CompanyName&quot;).Value Else MsgBox &quot;Record not found&quot; End If       .Close End With Set rs = Nothing Set cn = Nothing End Sub

 Follow steps 9 and 10 in the &quot;Steps to Reproduce Behavior&quot; section later in this article.

Note that you receive a message with the text &quot;Wolski Zajazd,&quot; which is the company name of the CustomerID that the code was seeking.



STATUS
This behavior is by design.



MORE INFORMATION
NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

Steps to Reproduce the Behavior
 Start Microsoft Access 2002.</li> Create a new, blank database.</li> On the File menu, point to Get External Data, and then click Link Tables.</li> In the Link dialog box, use the Look in box to locate the sample database Northwind.mdb, which is located in the C:\Program Files\Microsoft Office\Office10\Samples folder by default.</li> After you locate it, click Northwind.mdb, and then click Link.</li> In the Link Tables dialog box, click the Customers table, and then click OK.</li>  Create a new module, and then type the following line in the Declarations section if it is not already there: Option Explicit </li>  Type the following procedure: Sub LinkTableSeek Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset With rs       .ActiveConnection = cn        .Source = &quot;Customers&quot; .CursorLocation = adUseServer

.CursorType = adOpenKeyset .Open Options:=adCmdTableDirect .Index = &quot;PrimaryKey&quot; .Seek &quot;WOLZA&quot; If (Not .EOF And Not .BOF) Then MsgBox rs.Fields(&quot;CompanyName&quot;).Value Else MsgBox &quot;Record not found&quot; End If       .Close End With Set rs = Nothing Set cn = Nothing End Sub </li> On the Debug menu, click Compile  to verify that the module is compiled correctly.</li>  To test the procedure, type the following line in the Immediate window, and then press ENTER: LinkTableSeek Note that you receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article. </li></ol>

<div class="references_section">