Vb6,Access2000,Ado (Using Access2000 Index's) Not Working

From MSDN:

Q249683 - ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database
http://support.microsoft.com/support/kb/articles/Q249/6/83.ASP

-------------------------------------------------------
ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database

-----------------------------------------------------------The information in this article applies to:

Microsoft Access 2000

-----------------------------------------------------------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 Q290060.


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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:

http://www.microsoft.com/partner/referral/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
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 ensure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.


Follow steps 1 through 7 in the "Steps to Reproduce Behavior section" later in this article.


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
 
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 ensure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.



Steps to Reproduce Behavior
Start Microsoft Access 2000.


Create a new, blank database.


On the File menu, point to Get External Data, and then click Link Tables.


In the Link dialog box, locate the sample database Northwind.mdb, located in the C:\Program Files\Microsoft Office\Office\Samples folder by default.


Click Northwind.mdb in the Link dialog box, and then click Link.


In the Link Tables dialog box, click the Customers table, and then click OK.


Create a new module and type the following line in the Declarations section if it is not already there:


Option Explicit
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
On the Debug menu, click Compile project name to verify that the module is compiled correctly.


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:

Q243465 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
Issue type : kbprb
Technology : kbAccessSearch kbAccess2000 kbAccess2000Search

I think you may find this easier to read:

Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
 
Set cnn = New ADODB.Connection
With cnn
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Trim(App.Path) & "\accesswol.MDB"
   .Open
End With

Set rs = New ADODB.Recordset
With rs
   .Source = "wol"
   .ActiveConnection = cnn
   .CursorType = adOpenKeyset
   .LockType = adLockOptimistic
   .Open Options:=adCmdTableDirect
   Debug.Print .Supports(adIndex), .Supports(adSeek)
   .Close
End With
Set rs = Nothing

cnn.Close
Set cnn = Nothing

wsh2

Just got round to reading your solution.  The problem as I see it, is not that the provider does not support setting the index, it does.  Nor that the database does not support it, providing it is an Access 2000 database it is supported.  The problem is that turnerrob is trying to set an index to a Select statement which is clearly never going to work.  The other problem is that he/she is setting CursorLocation property for the Connection object to adUseClient and this also will not allow setting any indexes even if a table is opened in adCmdTableDirect.

Having said that, I do not recall ever having used this (Index) property.  Probably because I very rarely have the need to do record searches. So I could well be wrong, even though it appeared to work as advertised on my workstation.


Anthony

Anthony..

I totally agree with your code solution.. (ie. the old "K.I.S.S." - Keep It Simple Stupid principle.. <smile>). Looking at some of the ADO code settings used by the Questioner <and still scratching my head>.. either he/she is some kind of ADO Gawd or he/she is no more than a mere mortal like us trying to make it through to the next paycheck by randomly hitting keys until finally a work of Shakespearian proportion finally appears. Long story short.. the code Anthony provided is VERY servicable and a terrific starting point to build.. <smile>.

As I understand it.. the Index / Seek methodology so well used in the good old, old, old Access days is no longer in vogue.. and if I am correct.. met its demise in the later stages of DAO. I am fairly certain that the cause of its demise.. was the overhead required to build the Index before any processing could be done.. proved to be too onerous a task.. virtually negating ANY retreival savings to be had once the Index was finally built.

Which now makes me wonder.. what the questioner is attempting to do with the Index / Seek properties.. and that perhaps there is a much better and simpler way to achieve this were we to know the Questioners intentions.. <smile>.

Thanks for the Comment re my Ado Problem.
The alterations did solve the problem.

I copied this code from Microsoft MSN (Somewhere).
It did seem a little complex, even to my inexperienced VB mind!!!.

I had a feeling it might have been a VB Reference etc, problem. I was wrong, it was code.!!

Appreciate the help from wsh2, with a similiar answer,will note for any future questions asked and answered.

Appreciate the help

Turnerrob