ADO Connection With Table Indexes

[Jimmer]

I want to use an ADO connection to an Access 2000 database but I don't want to use SQL to make the connection. I want to use the tables index to sort the records and make the connection. I've tried this:

Set dbConnect = New ADODB.Connection
With dbConnect
.ConnectionString= "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & dbLocation
.CursorLocation = adUseClient
.Open
End With

recSet.Open "table",dbConnect,adOpenKeyset,adLockOptimistic, adCmdTableDirect

recSet.Index = "tableIndex"

But This will not work.

With DAO this would work will no problems. Any idea???

Thanx in Advance.

[Clunietp]

here ya go

    Dim rs As Recordset
    Dim cn As Connection
    Dim fld As Field
    
    Set cn = New Connection
    Set rs = New Recordset
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWind2k.mdb"
    
    With rs
        .ActiveConnection = cn
        .Source = "Customers"
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .Open
        
        Set fld = .Fields("CustomerID")
        fld.Properties.Item("Optimize").Value = True
        
        .Find "CustomerID = 'FRANR'"
                
        If .RecordCount > 0 Then
            MsgBox "Found!"
        Else
            MsgBox "Not Found!"
        End If
            
    End With