Microsoft KB Archive/249683

From BetaArchive Wiki

Article ID: 249683

Article Last Modified on 6/23/2005



APPLIES TO

  • Microsoft Access 2000 Standard Edition



This article was previously published under Q249683

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

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

For a Microsoft Access 2002 version of this article, see 290060.


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':

Object or provider is not capable of performing requested operation.

In Microsoft Windows 2000, 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 open the table directly instead of using the linked table. To see an example of how to do this, follow these steps:

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 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.

  1. Follow steps 1 through 7 in the "Steps to Reproduce Behavior section" later in this article.
  2. Type the following procedure within the new module:

    Sub LinkTableSeek()
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
    
        Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "C:\Program Files\Microsoft " _
             & "Office\Office\Samples\Northwind.mdb"
            .Open
        End With
        Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = cn
            .Source = "Customers"
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .Open Options:=adCmdTableDirect
            .Index = "PrimaryKey"
            .Seek "WOLZA"
            If (Not .EOF And Not .BOF) Then
                MsgBox rs.Fields("CompanyName").Value
            Else
                MsgBox "Record not found"
            End If
            .Close
        End With
        Set rs = Nothing
        Set cn = Nothing
    End Sub
    
                        
  3. Follow steps 9 and 10 in the "Steps to Reproduce Behavior" section later in this article.

Note that you receive a message with the text "Wolski Zajazd," 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 Behavior

  1. Start Microsoft Access 2000.
  2. Create a new, blank database.
  3. On the File menu, point to Get External Data, and then click Link Tables.
  4. In the Link dialog box, locate the sample database Northwind.mdb, located in the C:\Program Files\Microsoft Office\Office\Samples folder by default.
  5. Click Northwind.mdb in the Link dialog box, and then click Link.
  6. In the Link Tables dialog box, click the Customers table, and then click OK.
  7. Create a new module and type the following line in the Declarations section if it is not already there:

    Option Explicit
                        
  8. 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 = "Customers"
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .Open Options:=adCmdTableDirect
            .Index = "PrimaryKey"
            .Seek "WOLZA"
            If (Not .EOF And Not .BOF) Then
                MsgBox rs.Fields("CompanyName").Value
            Else
                MsgBox "Record not found"
            End If
            .Close
        End With
        Set rs = Nothing
        Set cn = Nothing
    End Sub
                        
  9. On the Debug menu, click Compile project name to verify that the module is compiled correctly.
  10. To test the procedure, type the following line in the Immediate window, and then press ENTER:

    LinkTableSeek
                            

    Note that you receive the error mentioned in the "Symptoms" section of this article.


REFERENCES

For additional information about using the Seek method in an ADO recordset based on a table in a Microsoft Jet database, click the article number below to view the article in the Microsoft Knowledge Base:

243465 ACC2000: How to Use the Seek Method with ActiveX Data Objects (ADO) Against a Jet Recordset



Additional query words: prb attached run time error 3251

Keywords: kbdta kbprb KB249683