After experimenting with many possible combinations and permutations of RecordSet Source, CursorType, CursorLocation, LockType and CommandType, I finally found the the right combination that yielded a record set with an index.

The key to opening a record set with an index is to open it with Source = Table (rather than an SQL SELECT * command), and to set the CursorLocation = adUseServer, and to set the Open Options parameter to adCmdTableDirect

Any CursorType and LockType is OK. Here is the code that will successfully utilize the built in indexes in an Access table:


Dim RS As ADODB.RecordSet

Set RS = New ADODB.RecordSet
RS.ActiveConnection = adoMyConnection
RS.CursorType = (any CursorType is OK)
RS.LockType = (any LockType is OK)

' Use table name - Don't use "SELECT * FROM MyTable"
RS.Source = "MyTable"

RS.CursorLocation = adUseServer
RS.Open , , , , adCmdTableDirect

I knew that Microsoft had to provide some way support indexes since we are talking about VB's native database and engine.

Tony Lin