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